SQL question / advice

[eluser]nevsie[/eluser]
I wondered if any one could offer advice on how to solve a scenario... Its confusing me, and a little beyond my abilities (if it is possible!)...

I have a single table which holds product_ids and category_ids... Simple theory is that a product can be put in more than one category... no problem there...

However, i want to run a search query which returns only product_ids that in two defined categories... for example:

Code:

prod_id     cat_id
  1         123
  2         123
  3         123
  4         123
  1         125
  2         126
  3         126

If i want only product ids that are in categories "123" and "125" i would then be returned "1".
If i wanted "123" and "126" i would be returned "2" and "3"

Can anyone advise me on how and if this will be possible through a (single) SQL query? I am currently working on queries, subqueries, join, union, etc. trying to figure out a possibility... my mind is frying!!!
Help appreciated, N

[eluser]Mike Ryan[/eluser]
Interesting... this should do it:

Code:

select prod_id from table where cat_id='123' and prod_id in (select prod_id from table where cat_id='126')

I haven't tested it yet, let me know how it goes.

[eluser]drewbee[/eluser]
The Distinct Keyword is what you are looking for. Using the IN keyword also helps clean this up a little bit.

For every value passed to an IN(x,y,z) column = x OR column = y OR column = z

Code:

SELECT DISTINCT product_id FROM table WHERE cat_id IN ('123', '125')

Code:

SELECT DISTINCT product_id FROM table WHERE cat_id IN ('123', '126')

[eluser]Mike Ryan[/eluser]
I bow to drewbee's superior SQL knowledge ;-)

On reading your solution, I realised how ugly my suggestion would become if you wanted to search for an item that exists in more than two categories.

[eluser]nevsie[/eluser]
hi all,
Thanks for the reply... and i will be testing out the ideas and seeing which works best in this scenario...

I will be looking into distinct first, however, the problem that i have is that this is for a pre-existing ecommerce facility... And i am looking to add on a search conditional that just adds to WHERE part of the SQL statement.
The reason is that i do not want to duplicate / reproduce chunks of code specifically for this different search feature. Therefore, if i can just add a small piece of code to the WHERE part without influencing speed and performance it will be the cleanest coding option - admittedly it might not be the right choice overall.

To give you an idea of the search facility currently there (with virtually all options turned off i have:

Code:

SELECT $fields FROM $db_tables[products] as products $join WHERE $search_condition GROUP BY products.product_id $sorting $limit

If you imagine that the $fields part is multiple fields across multiple tables, various joins occurring, and there are other where statements already included.... I imagine this will severely influence the DISTINCT part of the query... Although reading more, it might nt be a problem actually... let me trial and get back... any other tips while i work is appreciated! Cheers Guys, N

[eluser]drewbee[/eluser]
Perhaps if you post the table structure, we can help you out further.

I can't remember specificially for mysql's DISTINCT behavior, but I believe for a row to be identified as a duplicate, every column must match another column.


Anyways, the query I gave you above could actually be used as a nice starter as part of a subquery, then join all resulting rows based on that. Their are all kinds of things you can do actually.

[eluser]nevsie[/eluser]
hi drewbee...
I certainly know that if i should not really have simplified down the SQL, PHP, and full db tables, but there is so much interrelated it would be crazy to paste it all... Therefore i was looking for hints that would get my mind ticking and hopefully i would understand how i resolved it (hopefully!!!)... Therefore at the moment i am testing by building in a new feature based on your DISTINCT and IN statement above and hopefully the test will be fruitful... if not, i'll be back knocking in 10 or 15 or so!!!

Cheers, N

[eluser]nevsie[/eluser]
Right, sorry, said I would be back!!!
I have everything working and in principle it does as you suggested, however the IN part of the query does not perform as i wished (and most likely i did not explain correctly above)...

As far as i have read, IN will pull back all rows which have ANY of those values in it... (effectively and OR on each of those values). Therefore using my example from above:

Code:

prod_id     cat_id
  1         123
  2         123
  3         123
  4         123
  1         125
  2         126
  3         126
  5         127

cat_ids “123” and “125” using an IN would return "1", "2", "3", "4"
cat_ids “123” and “127” using an IN would return "1", "2", "3", "4", "5"

Where as i really wanted an AND where which only returns a distinct prod_id if both cat_ids are met...
cat_ids “123” and “125” wanted results "1"
cat_ids “123” and “127” wanted results "no results"

Does this make more sense? will have a look and play around with the subquery as i am only ever going to have two Cat_ids in this current instance...
Hope you can offer a magic solution, i am getting tired eyes now!!! And you help is truly appreciated! N

[eluser]Colin Williams[/eluser]
SELECT DISTINCT prod_id FROM products WHERE cat_id = "123" OR cat_id = "125"

EDIT --

Above SQL query misses the "match all" condition, among other things. Hard to assume your table structure, which would probably reveal a solution

[eluser]nevsie[/eluser]
Hi guys, thanks for you above replies... but the OR part of the statement is where i am having the trouble... Or will effectively bring back rows which have one of those values...
I understand the need for the table structure, but it is literally as described above. It is a two column table which links products and categories (allowing products to be placed in more than one category).

Because Cat id is only mentioned once on each row, effectively i am looking to merge two rows where prod_id is the same, and where in each of these two rows cat_id equals the value...
The more i read an analyse this cannot be done with a single query, therefore i have had to go down the two query path and have a working example....
the first query brings back all product_ids for the first cat_id, the second bring back only prod_ids that are from the first query, and have the second cat id... not clean, not efficient, but it works!