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

SQL table join + count help

P: 8
I have 3 tables: ARTISTS, ART and PAINTINGS
ARTISTS has columns (data):
userid (adam, betty, gil, jack, larry, steve, tyler)
full_name (firstname lastname)

ART has columns:
art_id (auto number created when new art_name added via application)
art_name (name of piece of art entered)
designed_by (populated with userid)

PAINTINGS has columns:
paint_id (auto number created when new painting_name added via application)
painting_name (name of painting entered)
painted_by (populated with userid)

I need a sql statement for a report that will return a list of the userids, counts(art_id), and count(paint_id).

Some userids will exist in both tables some will not. If not then count = 0.

Would like result set format to look like this:

artist ART_CNT PAINT_CNT
adam 1 5
betty 3 0
gil 4 4
jack 6 0
larry 2 2
steve 0 9
tyler 3 7

I can get the userids and counts from each table but don't know how to join or merge the sql to get the result set format of 3 columns from one query. Is this possible???

Here are the seperate select statements:

SELECT designed_by, Count(art_id)
FROM ART
GROUP BY designed_by

SELECT painted_by, Count(paint_id)
FROM PAINTINGS
GROUP BY painted_by

Thanks for your help in advance.
Jan 25 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT userid as artist,ART_CNT,PAINT_CNT
  2. FROM ARTISTS 
  3. LEFT JOIN
  4. (   SELECT designed_by, Count(art_id) as ART_CNT
  5.     FROM ART
  6.     GROUP BY designed_by
  7. ) b on a.userid = b.designed_by
  8. LEFT JOIN
  9. (    SELECT painted_by, Count(paint_id) as PAINT_CNT
  10.      FROM PAINTINGS
  11.      GROUP BY painted_by
  12. ) c on a.userid = c.painted_by
  13.  
LEFT JOIN is allowed in SQL server but I don't think it will work in access
I think it should be either LEFT OUTER JOIN or LEFT INNER JOIN
Can't remember which as I havn't used access in a while
Jan 25 '08 #2

P: 8
Thanks for the fast reply. What do the a, b, c reference?

b on a.userid = b.designed_by

When executing it, an error comes back as invalid identifier "b"
Jan 25 '08 #3

Delerna
Expert 100+
P: 1,134
the a b and c are alias's for the table and subqueries
I did that just to make my typing easier in the post

the alias shuld be recognizable by access. Check your query carefully
it seems you may have left the alias off
Jan 25 '08 #4

Delerna
Expert 100+
P: 1,134
No, sorry I left the alias off the table

this bit
SELECT userid as artist,ART_CNT,PAINT_CNT
FROM ARTISTS

should be
SELECT userid as artist,ART_CNT,PAINT_CNT
FROM ARTISTS a
Jan 25 '08 #5

Post your reply

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