Joining two columns of foreign key with one column of another table

Question

Joining two columns of foreign key with one column of another table

codeexplorer1

SSC Enthusiast

Points: 124
More actions
January 4, 2023 at 6:21 am
Go to Answer
#4133356

Hi,

I have two columns in table1 i.e. hotel1 and hotel1 . Both of them have foreign key of second table2 hotels.
i.e. Table 1 (Book)
hotel1 hotel2
1 3
Table 2 Hotels
1 HotelName1
2 HotelName2
3 HotelName3

I want to join in such a way that I get hotel names corresponding to table1. Please guide.
Grant Fritchey

SSC Guru

Points: 398882
More actions
January 4, 2023 at 12:52 pm
Answer
#4133487
Sounds like two joins. Once on hotel1 to the table2 id, then on hotel2 to table2 id. This is psuedo code, but you get the idea;
```
SELECT ha.HotelName AS hotel1name, 
     hb.HotelName AS hotel2name
FROM dbo.Book AS b
JOIN dbo.Hotels AS ha
    ON b.hotel1 = ha.id
JOIN dbo.Hotels AS hb
    ON b.hotel2 = hb.id
WHERE....
```
It's the aliases that let you do something like this. Also, be sure you have foreign keys defined between the columns using WITH CHECK. That's a performance enhancer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

codeexplorer1 wrote:

Hi,
I have two columns in table1 i.e. hotel1 and hotel1 . Both of them have foreign key of second table2 hotels.
i.e. Table 1 (Book)
hotel1 hotel2
1 3
Table 2 Hotels
1 HotelName1
2 HotelName2
3 HotelName3
I want to join in such a way that I get hotel names corresponding to table1. Please guide.

Quick thought, you might want to review the schema design 😉

😎

My question is; why are the two columns in a table for the same kind of data?

codeexplorer1 SSC Enthusiast Points: 124 More actions · Answer 2

Because there will be two hotels that need to be allocated one for 15 days and another one for 15 days 🙂

This reply was modified 1 year, 10 months ago by codeexplorer1.

codeexplorer1 SSC Enthusiast Points: 124 More actions · Answer 3

Grant Fritchey wrote:

Sounds like two joins. Once on hotel1 to the table2 id, then on hotel2 to table2 id. This is psuedo code, but you get the idea;
SELECT ha.HotelName AS hotel1name, 
     hb.HotelName AS hotel2name
FROM dbo.Book AS b
JOIN dbo.Hotels AS ha
    ON b.hotel1 = ha.id
JOIN dbo.Hotels AS hb
    ON b.hotel2 = hb.id
WHERE....
It's the aliases that let you do something like this. Also, be sure you have foreign keys defined between the columns using WITH CHECK. That's a performance enhancer.

Thank you for the solution. It worked.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

codeexplorer1 wrote:

Because there will be two hotels that need to be allocated one for 15 days and another one for 15 days 🙂

Then why not a table with Hotel, "from date" and "to date"? What happens if there will be a requirement of 3 or more hotels, are you going to more columns?

😎

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

Eirikur Eiriksson wrote:

codeexplorer1 wrote:
Because there will be two hotels that need to be allocated one for 15 days and another one for 15 days 🙂
Then why not a table with Hotel, "from date" and "to date"? What happens if there will be a requirement of 3 or more hotels, are you going to more columns? 😎

Couldn't agree more.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning