By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,220 Members | 1,008 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,220 IT Pros & Developers. It's quick & easy.

Multiple Subquery joins on same table

P: 9

I am just starting my work with subqueries and I am a little lost on how to handle the syntax on one part.

I have a query that will contain some subqueries for the purpose of counting the records in three different tables.

I understand how to create the subqueries for the most part but I am unsure how to set-up the join since all 3 subqueries will need to join back to the same table that also has to be joined to one other table not in any of the subqueries.

Basically each table (lets say tbl1, tbl2, and tbl3) has the field "Item" that will need to be counted and joined to tblItems.Item.

How do I got about setting up the syntax on those joins? I know how to make them individually but not all the in the same query.

An example of what one of the queries looks like follows:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblItems.Item, Count(Q.Item) AS KanbanInHold
  2. FROM [SELECT tblKanbanInHold.Item
  3. FROM tblKanbanInHold INNER JOIN tblItems ON tblKanbanInHold.Item = tblItems.Item]. AS Q INNER JOIN tblItems ON Q.Item = tblItems.Item
  4. GROUP BY tblItems.Item;
Aug 25 '08 #1
Share this Question
Share on Google+
1 Reply

P: 97
The inner join syntax will only join items that are matching. If you have different items your joins are doing nothing. I don't have access in front of me but I believe you want a UNION or possibly a LEFT join.
Sep 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.