CODEWITHSUNDEEP
sqljoin
user605505
user605505

Reputation: 59

sql query using two tables

Table one:

|user_id|hotel_id|  
|  1    |  4     |
|  1    |  5     | 
|  2    |  6     |
|  4    |  7     |

table two: 

|hotel_id|hotel_name|more_ifo| 
|    4   | bla      |bla     |
|    5   | bla      |bla     |
|    6   |more bla  |bla     |
|    7   | bla      |bla     |

I want to get all the info of the hotels that a certain user have for example user one hotels 4 and 5 .. any help? I tried using join but I guess I'm not using it correctly..?

Upvotes: 1

Views: 172

Answers (4)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36681

t1 is alias for table one and t2 is alias table two

select t1.user_id,t2.hotel_id,t2.hotel_name,t2.more_info
from table_one t1 INNER JOIN table_two t2
ON t1.hotel_id=t2.hotel_id
where t1.user_id=1;

Upvotes: 2

Soner Gönül
Soner Gönül

Reputation: 98868

Select B.more_ifo
From table_one A, table_two B
Where A.hotel_id = B.hotel_id

This isn't work ?

After that;

Select B.more_ifo
From table_one A, table_two B
Where A.hotel_id = B.hotel_id
AND A.hotel_id IN (4, 5)

Upvotes: 1

Andomar
Andomar

Reputation: 238296

You could query all hotels for a user with an exists clause:

select  *
from    hotels
where   exists
        (
        select  *
        from    users
        where   users.hotel_id = hotels.hotel_id
                and users.user_id = @YourUserId
        )

Upvotes: 1

cdhowie
cdhowie

Reputation: 169458

SELECT users.*, hotels.*
FROM users
INNER JOIN hotels
ON users.hotel_id = hotels.hotel_id

Note that using * in a SELECT clause is considered bad practice; I only do it here as part of an example of using joins.

If you want to search for the hotels associated with a specific user, just add a WHERE clause to that effect:

SELECT users.*, hotels.*
FROM users
INNER JOIN hotels
ON users.hotel_id = hotels.hotel_id
WHERE users.user_id = ?

Upvotes: 3

Related Questions