Selecting items from one table that match 2 enteries in another table

[Buchead]

Hello,

Please can someone point me in the right direction. I have 2 tables called products and products_tree. products contains 5 fields (productID, name, height,  length, weight) and products_tree contains 2 fields (productID and sectionID). It's possible for products to be in more than 1 section, so there would be mulitple enteries in products_tree.

What I'd like to do it select the items from products where they're in multiple sections.

For example:

In products, an item has productID of 10.

It's listed in sections 4 and 5, so there are 2 enteries in products_tree, so

productID  sectionID
    10              4
    10              5


I've been unable to find the command that checks products_tree for any productID that match the 2 sectionIDs, and then retrieve this productID data from products.

Is it actually possible to perform in one command, or am I heading in an impossible direction?

Thanks,

Clive.

[fenway]

You could do a count() and then use a HAVING clause to find those with 2 matches.

[Buchead]

Thanks for that. By using:

SELECT productID, sectionID, count(productID) FROM products_tree GROUP BY productID HAVING count(productID) = 2

it displays the product IDs that have multiple enteries. However, I can't seem to get it pulling off only those records where one of the section IDs matches a specific criteria. By viewing the sectionID pulled from the query it's showing the same for all the records (which is only one section ID I want to search for).

products_tree looks like:

productID    sectionID
    1                 1
    2                 1
    3                 1
    1                 2
    4                 1
    5                 1
    4                 3

It's pulling off productIDs 1 and 4, but showing sectionID '1' for both of them. I'd want to search for productIDs with sectionID's of 1 and 3, thus pulling off 1 record, but can't seem to add in any other search parameters.

Any ideas?

Thanks.

[Barand]

[code]SELECT * FROM product_tree p
WHERE
  (SELECT COUNT(*) FROM product_tree b WHERE p.product_id = b.product_id) > 1[/code]

[fenway]

A correlated subquery is not the most ideal... why not simply add "WHERE sectionID IN (1,4)" to the query?

[Buchead]

Thanks for all the help but I'm still experiencing problems extracting the correct information. It clearly down to being new to mysql and attempting something other than the basics!

Using 'SELECT * FROM products_tree WHERE sectionID IN (1,4)' pulls out all the records which have those sectionIDs and not just productIDs that match both the sections. If I put that query as a subselection it pulls out no records.

How can I set it so that it only pulls the productIDs that match both the sectionIDs?

Sorry if this is obvious but it's not to me!

[Barand]

To avoid confusion, you start with this
[pre]
productID    sectionID
    1                1
    2                1
    3                1
    1                2
    4                1
    5                1
    4                3[/pre]

What output are you wanting to see?

[Buchead]

What I have is a table that contains product information. The productID corresponds to a field in products_tree table that also has a field for the sectionID the product can be displayed in. It's possible for a product to be in 2 sections, and what I want to do is pull out all the records from the products table that correspond to 2 given sectionIDs.

I have written a long-winded - and not the best - way of doing this, but was wondering if there was an sql command that could pull all the records from the products table in one easy go.

Hopefully this makes sense.

[Barand]

Did my query above (reply #3) come close?

[Buchead]

Yes, it came close. That retrieved all the productIDs from products_tree that had multiple enteries, however it extracts all of them. For example, productID 2 may be in sectionIDs 1 and 3, while productIDs 3 and 4 are in 1 and 4.

What I want to do is pull the productIDs that are only in sectionIDs 1 and 4.

Thanks.

[shoz]

I think fenway was suggesting the following
[code]
SELECT
productID, sectionID, count(productID)
FROM
products_tree
WHERE
sectionID
IN
(1,3)
GROUP BY
productID
HAVING
count(productID) = 2
[/code]

Btw, you should put a UNIQUE index on (productID, sectionID) to ensure that each productID is referenced to a specific sectionID only once.

[Buchead]

Fantastic. That worked perfectly. Many thanks for all your help......and putting up with my incompetence!

[Buchead]

Oops, jumped the gun a little. This command correctly selects the required records from products_tree but is it then possible to pull the corresponding records from the products table within the same command?  Those are the actual records than I'm after.

Thanks.

[shoz]

[code]
SELECT
p.*
FROM
products AS p
INNER JOIN
products_tree AS t
ON p.productID = t.productID
WHERE
t.sectionID
IN
(1,3)
GROUP BY
t.productID
HAVING
count(t.productID) = 2
[/code]

[Buchead]

Thank you very much.  Hopefully it'll all start making sense soon.......