how to link between two tables

Question

how to link between two tables

SPWGUT 121

Hi,i want to select cities groupping by countries ,Any suggestions?

My code
create table dbo.Countries ( CountryID int, Country nvarchar(255) )
insert into dbo.Countries ( CountryID, Country )
values ( 1, N'Russia' ), ( 2, N'USA' ), ( 3, N'Germany' )
, ( 4, N'France' ), ( 5, N'Italy' ), ( 6, N'Spain' )

create table dbo.Cities ( CityID int, CountryID int, City nvarchar(255) )
insert into dbo.Cities ( CityID, CountryID, City )
values ( 1, 1, N'Moscow' ), ( 2, 1, N'St. Petersburg' ), ( 3, 1, N'Yekaterinburg' )
, ( 4, 1, N'Novosibirsk' ), ( 5, 1, N'Samara' ), ( 6, 2, N'Chicago' )
, ( 7, 2, N'Washington' ), ( 8, 2, N'Atlanta' ), ( 9, 3, N'Berlin' )
, ( 10, 3, N'Munich' ), ( 11, 3, N'Hamburg' ), ( 12, 3, N'Bremen' )
, ( 13, 4, N'Paris' ), ( 14, 4, N'Lyon' ), ( 15, 5, N'Milan' )
go

Sorry, forgot to put the desired result, here is the result I requested.
1 Russia 1 1 Moscow
1 Russia 2 1 St. Petersburg
1 Russia 3 1 Yekaterinburg
1 Russia 4 1 Novosibirsk
1 Russia 5 1 Samara

Thanks in advance..

Accepted answer

0 additional answers

Your answer

Answer 1

LiHong-MSFT 10,056

Hi @SPWGUT
You just need a simple join,check this:

    SELECT *  
    FROM dbo.Countries A JOIN dbo.Cities B ON A.CountryID=B.CountryID  
    WHERE A.CountryID =1

Output:

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

LiHong-MSFT 10,056 Reputation points

2022-04-18T08:18:23.637+00:00

To display cities of Russia only , just add a where filter: WHERE A.CountryID =1 or WHERE A.Country ='Russia'

By the way, if this is your homework, I recommend you to solve it by yourself. If you come to the forum for help, it will not improve your knowledge.

Best regards,
LiHong

SPWGUT 121

Hello, what is my homework? I don't know much about sql. I am the engineer responsible for the front end of the company. The next step I want to achieve is to create a Stream to get the execution date of the table. This execution date can be specified by me or other administrators.

public static Stream<LocalDate> getDatesInRange(
@Nonnull LocalDate startDate, @Nullable LocalDate endDate, @Nullable Set<DayOfWeek> dayOfWeeks) {
Objects.requireNonNull(startDate, "Must provide a start date");

    final LocalDate oneWeekLater = startDate.plusWeeks(1);
    final List<LocalDate> datesInAWeek = Stream.iterate(startDate, d -> d.isBefore(oneWeekLater), d -> d.plusDays(1))
                                               .filter(d -> dayOfWeeks == null || dayOfWeeks.contains(d.getDayOfWeek()))
                                               .collect(Collectors.toList());

    final Stream<LocalDate> dateStream = Stream.iterate(
        datesInAWeek,
        dates -> dates.stream().map(d -> d.plusWeeks(1)).collect(Collectors.toList())
    ).flatMap(Collection::stream);
    return endDate != null
        ? dateStream.takeWhile(d -> !d.isAfter(endDate))
        : dateStream

}
Can you help with this, thanks in advance.

LiHong-MSFT 10,056 Reputation points

2022-04-18T08:56:01.093+00:00

Hi @SPWGUT
Sorry I mistakenly thought you were a T-SQL beginner.
And for your second issue,considering it's not very relevant to your initial question, I recommend that you create a new thread asking this question.

Best regards,
LiHong

Share via

how to link between two tables

0 additional answers

Your answer