sql query help

[j420exe]

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
 

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.

SELECT userid as artist,ART_CNT,PAINT_CNT
FROM ARTISTS a
LEFT JOIN
(   SELECT designed_by, Count(art_id) as ART_CNT
    FROM ART 
    GROUP BY designed_by
) b on a.userid = b.designed_by
LEFT JOIN
(    SELECT painted_by, Count(paint_id) as PAINT_CNT
     FROM PAINTINGS
     GROUP BY painted_by
) c on a.userid = c.painted_by

[deepuv04]

try this query

SELECT a.userid AS artist, b.ART_CNT, c.PAINT_CNT
FROM   ARTISTS AS a LEFT OUTER JOIN
       (SELECT     designed_by, COUNT(art_id) AS ART_CNT
        FROM          ART
        GROUP BY designed_by) AS b ON a.userid = b.designed_by LEFT OUTER JOIN
      (SELECT     painted_by, COUNT(paint_id) AS PAINT_CNT
        FROM          PAINTINGS
        GROUP BY painted_by) AS c ON a.userid = c.painted_by