• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

Sql Join query - joining on multiple tables

Dear Experts,

I have never used join statements for more than 1 table - here I have to join 5 tables - I did manage to write the query and it returns correct results so far - but something tells me its not right.

This is my DB structure
Tables
Store - primary key store_id
Offer - primary key offer_id ( other fields include store_id)
User - primary key user_id
User_query - primary key user_query_id - This table has a foreign key relationship with User table (user_query.user_id = user.user_id)
Offer_Sent - primary key offer_sent_id - This table has a foreign key relationship with User_query table (offer_sent.user_query_id = user_query.user_query_id)
Offer_sent table also captures offer_id - but this column can also be null if no offers are found.

Now to find all the queries that have come in user_query table - if I write

select *
from offer_sent os,
             user_query uq,
             offer o,
             store s,
             user u
where uq.user_query_id=os.user_query_id
and os.offer_id = o.offer_id
and o.store_id = s.store_id
and u.user_id=uq.user_id

It will only return rows that have offer_id present in offer_Sent table. It wont include rows where offer_id is null

But this query seems to work:

select *
from offer_sent os
join user_query uq on uq.user_query_id=os.user_query_id
join user u on uq.user_id=u.user_id
left outer join offer o on os.offer_id=o.offer_id
left outer join store s on o.store_id = s.store_id

Can you take a look at the query and tell me if thats they way multiple tables should be joined
Appreciate your quick feedback
Thanks
0
gublooo
Asked:
gublooo
2 Solutions
 
Pratima PharandeCommented:
try this ... your's is also correct

select *
from offer_sent os
inner join user_query uq on uq.user_query_id=os.user_query_id
inner join [user] u on uq.user_id=u.user_id
left outer join offer o on os.offer_id=o.offer_id
left outer join store s on o.store_id = s.store_id
0
 
mabbj747Commented:
Hi,
The query which you have written is correct but I want to make a suggestion here.
Try to alias the table with proper names so that when you debug it in future, you can easily figure out which colums you are joining from which tables. Here is the example

SELECT  
   *
FROM  
   offer_sent OfferSent
INNER JOIN  
   user_query UsrQry
ON  
   UsrQry.user_query_id=OfferSent.user_query_id
INNER JOIN  
   [user] Users
ON  
   UsrQry.[user_id] = Users.[user_id]
LEFT OUTER JOIN
   offer Offers
ON  
   OfferSent.offer_id = Offers.offer_id
LEFT OUTER JOIN  
   store Stores
ON
   Offers.store_id = Stores.store_id
0
 
gubloooAuthor Commented:
Thanks for your suggestions guys - Appreciate it.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now