Hello,

Having 6 tables:

user with col user
tbl1 with col id, user
tbl2 with col user, sin, pic
tbl3 with col user, sin, link
tbl4 with col user, sin, ref
tbl5 with col user, sin, lit

tbl2-5.sin corresponds to tbl1.id

tbl1-5.user corresponds to user.user

I want to select:

id, user, pic, link, ref, lit

Where

id is the unique value of tbl1.id

user is tbl1.user

pic is the number of rows where tbl2.sin=tbl1.id AND tbl2.user=user.user

link is the number of rows where tbl3.sin=tbl1.id AND tbl3.user=user.user

ref is the number of rows where tbl4.sin=tbl1.id AND tbl4.user=user.user

lit is the number of rows where tbl5.sin=tbl1.id AND tbl5.user=user.user

With "WHERE" I would like to restrict the output to a specific user e.g. "WHERE user.user='5'"

I have tried with LEFT JOIN, but not succeeded.

I would appreciate any suggestion or reference to a thread where a similar query has been discussed. Thanx.

good question. i am not sure how to add a COUNT(*) and get other information to one query. can you not put this into multiple queries?

I have solved the problem with subselect. It works, but I think there must be a better way. For anyone interested here a copy from the actual code:

SELECT
h.id,
h.taxgen,
h.taxspe,
h.user AS data,
(SELECT COUNT(*) FROM pictures p WHERE h.id = p.sin AND p.user='$usid') AS pics,
(SELECT COUNT(*) FROM links l WHERE h.id = l.reltosp AND l.user='$usid') AS links,
(SELECT COUNT(*) FROM literature lit WHERE h.id = lit.sin AND lit.user='$usid') AS lit,
(SELECT COUNT(*) FROM botxt b WHERE h.id = b.sin AND b.user='$usid') AS txt
FROM herbarium h
HAVING data=1 OR pics>0 OR links>0 OR lit>0 OR txt>0
ORDER BY h.taxgen, h.taxspe

Thank you cornercuttin, multiple querries would be also a solution, but would require even more complex coding (php+mysql).