Complicated select statment help...

[bugzy]

I have this merge table name "item_category"

 

Here's the scenario:

 

category_id | item_id

 

        1              33

        2              33

 

 

What I want is like this:

 

Find all item_id that are the same as item_id of category_id "1"

 

on the table above it supposed to give me two rows since there are two item_id "33".

 

 

I have this mysql code but it's counting one row only

 

SELECT ic.item_id, COUNT( i.item_id )

FROM item_category ic

LEFT JOIN item i ON ic.item_id = i.item_id

WHERE ic.category_id =93

GROUP BY ic.category_id

 

 

 

Don't know if I'm doing this right.. anyone?

[Barand]

You must GROUP BY one (at least) of the items that you have selected.

 

Also you say you are expecting 2 rows with item_id = 33 yet your WHERE is looking for category_id = 93

[bugzy]

You must GROUP BY one (at least) of the items that you have selected.

 

Also you say you are expecting 2 rows with item_id = 33 yet your WHERE is looking for category_id = 93

 

 

Barrand thanks for the response again..

 

To make it more clear..

 

category_id | item_id

 

        1              33

        2              33

        1              34

        1              35

        2              35

 

 

On this table, I'm looking actually for the category id.

 

So let's say variable

 

<?php $category_id = 1; ?>

 

 

then I have this simple sql statement

 

Select * from item_category where category_id = {$category_id}

 

 

Now, what I want is, to count all the item_id from that table that is also the same item_id of $category_id.

 

The table above will supposed to count 5 rows.

 

 

Is my condition still right? Now I don't know if my code is even near to what I'm expecting...

[bugzy]

I tried this but it aint working also

 

SELECT category_id, item_id, COUNT(item_id)

FROM item_category

WHERE category_id = 1

HAVING COUNT(item_id) > 1

 

 

 

[bugzy]

Ok I have this now..

 

SELECT category_id, count( DISTINCT item_id )

FROM item_category

GROUP BY category_id

 

I am getting the right number of row from the above code..

 

The problem is when I put a condition

 

SELECT category_id, count( DISTINCT item_id )

FROM item_category

WHERE category_id = 1

GROUP BY category_id

 

Number of rows is always wrong

 

Anyone?

[Barand]

From the input data you provided as an example

category_id | item_id

 

        1        33

        2        33

        1        34

        1        35

        2        35

       

are you expecting this?

item_id  |  count

  33        2

  34        1

  35        2

[bugzy]

From the input data you provided as an example

category_id | item_id

 

        1        33

        2        33

        1        34

        1        35

        2        35

       

are you expecting this?

item_id  |  count

  33        2

  34        1

  35        2

 

 

Barrand Yes exactly,  I'm expecting 5 total counts.. Is this possible in mysql?

[Barand]

Yes. You need a subquery to find the items associated with your chosen category, so

 

SELECT ic.item_id as Item, COUNT(ic.category_id) as Total
FROM item_category ic
    INNER JOIN (
        SELECT item_id FROM item_category WHERE category_id = 1
    ) as x
    ON ic.item_id = x.item_id
GROUP BY ic.item_id

 

Not tested so let me know how it goes.

[bugzy]

Thanks Barrand