Help with MySQL join

[Overkill]

i need to do a single query against two table as follow.


Select item.a, item.b, item.c, count(place.d) as item_count where from item left outer join place on (item.id = place.id) where location='1';

not the query runs just fine, how my result is not what i want. given that table place will have multiple entry for a, i want to count the amount and display it as one in a single table.

The result i am looking a table with multiple items and a count of the id as it appears in place

my issue is that when i run the query i only get one result back not the list, event thought my items table have multiple items and my place table is blank....

can anyone help????

[leocrawf]

Try adding a group by for the column that will make it un unique example item.id

[Wallizzle]

i need to do a single query against two table as follow.


Select item.a, item.b, item.c, count(place.d) as item_count where from item left outer join place on (item.id = place.id) where location='1';

not the query runs just fine, how my result is not what i want. given that table place will have multiple entry for a, i want to count the amount and display it as one in a single table.

The result i am looking a table with multiple items and a count of the id as it appears in place

my issue is that when i run the query i only get one result back not the list, event thought my items table have multiple items and my place table is blank....

can anyone help????

Why your question stated so complexly. Next time use real world examples bro. But like the other guy said use a group by clause to group your items like so

Select item.a, item.b, item.c, count(place.d) as item_count
from item
left outer join place
on (item.id = place.id)
where location='1'
group by item.a, item.b, item.c

so each data row will look like this
| item a | item b | item c | item_count
| Nokia | Lumia | 810 | 5

P.S. try indenting your code too, makes it so much easier to read
you could throw in nolock on the table names to prevent lockups, depending on how long ur query takes to run, also alias for easier coding... just saying