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

sql query 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT designed_by, Count(art_id)
  2. FROM ART
  3. GROUP BY designed_by
  4.  
Expand|Select|Wrap|Line Numbers
  1. SELECT painted_by, Count(paint_id)
  2. FROM PAINTINGS
  3. GROUP BY painted_by
  4.  
I orginially posted this in the Access forumn but got our sql server 05 db running over the weekend. So, I thought I would post it here since I am still unable to figure it out. This was the query that was suggest from another viewer but I am getting an invalid identifier "b" error message. Thanks for your help in advance.
Expand|Select|Wrap|Line Numbers
  1. SELECT userid as artist,ART_CNT,PAINT_CNT
  2. FROM ARTISTS a
  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
Jan 28 '08 #1
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
try this query

Expand|Select|Wrap|Line Numbers
  1. SELECT a.userid AS artist, b.ART_CNT, c.PAINT_CNT
  2. FROM   ARTISTS AS a LEFT OUTER JOIN
  3.        (SELECT     designed_by, COUNT(art_id) AS ART_CNT
  4.         FROM          ART
  5.         GROUP BY designed_by) AS b ON a.userid = b.designed_by LEFT OUTER JOIN
  6.       (SELECT     painted_by, COUNT(paint_id) AS PAINT_CNT
  7.         FROM          PAINTINGS
  8.         GROUP BY painted_by) AS c ON a.userid = c.painted_by
Jan 29 '08 #2

Post your reply

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