[SOLVED] selecting multple doubles

[mforan]

how would one go about selecting data from the database which is present 2 (or more) times in the database?

[Barand]

use a subquery to find multiple values then select using a join matching aginst the subquery.

[mforan]

im confused already lol, i think i understand what you are saying thou, im not sure about this subquery :S

[Barand]

If you ask a generalised question I can only give a generalised answer.

[Barand]

I can be more specific but I have to invent an example. I'm not sure how relevant this is to your situation. Anyway, it's a two-for-price-of-one offer

 

Prices data

[pre]

+----------+-------+

| price_id | price |

+----------+-------+

|        1 | 29.95 |

|        2 | 35.00 |

|        3 | 25.00 |

|        4 | 25.00 |

|        5 | 20.00 |

|        6 | 15.00 |

|        7 | 15.00 |

|        8 | 12.00 |

|        9 | 10.50 |

+----------+-------+[/pre]

 

Find rows with the same prices.

 

[pre]

mysql> SELECT a.price_id as itemA, b.price_id as itemB

    -> FROM prices a INNER JOIN prices b ON a.price=b.price

    -> WHERE a.price_id < b.price_id;

+-------+-------+

| itemA | itemB |

+-------+-------+

|    3 |    4 |

|    6 |    7 |

+-------+-------+

[/pre]

 

Find rows with duplicate prices

 

[pre]

mysql> SELECT p.price_id

    -> FROM prices p

    -> JOIN (SELECT price, COUNT(*) FROM prices GROUP BY price

    -> HAVING COUNT(*) > 1) as x

    -> ON p.price=x.price;

+----------+

| price_id |

+----------+

|        3 |

|        4 |

|        6 |

|        7 |

+----------+

[mforan]

ahh perfect, after a bit of tweaking that worked a charm, and i learnt from this about the "group by" and "having count", understand now how they work abit more! cheers bud!