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.