Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cross table sub selects

Status
Not open for further replies.

Guest_imported

New member
Jan 1, 1970
0
I'm totally stuck with a particular problem.

I have two tables (Simplified for this example) called wm_a and wm_b

wm_a has 3 fields:
id
category
description

wm_b has 2 fields:
id
category_name

Now, I want to select all the rows in wm_b AND how many rows there are in wm_a which have a corresponding category number to wm_b's id.

So I get a result looking like:
Code:
+--+-------------+-----+
|id|category_name|count|
+--+-------------+-----+
| 1|MyCategory01 |    2|
| 2|MyCategory02 |    1|
| 3|MyCategory03 |    4|
| 4|MyCategory04 |    0|
+--+-------------+-----+
The 'count' column being the number of rows in wm_a who's category field corresponds to the id column.

Thank you greatly in advance
 
hi
i think this should work

SELECT wm_b.id, wm_b.category_name, COUNT(wm_a.id) FROM wm_b INNER JOIN wm_a ON wm_a.category = wm_b.id
GROUP BY wm_b.id

hope this helps
 
It works, except if the count is 0, the row isn't returned. Is there any way to tell it to return the row even if the value is 0.
 
change the INNER JOIN to LEFT JOIN and that's it
 
It works! Hooray! Thanks, you're a lifesaver.

I can handle MySQL and PostgresQL pretty well, as long as I don't have to do any joins... can you direct me towards a good reference/tutorial on how to use joint tables?
 
sorry i don't know any tutorial for joins, but it's quite good described in the mysql manual
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top