Jump to content


Photo

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


  • Please log in to reply
14 replies to this topic

#1 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 08 September 2006 - 10:53 PM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 September 2006 - 03:47 AM

You could do a count() and then use a HAVING clause to find those with 2 matches.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 09 September 2006 - 04:50 AM

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.

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 02:21 PM

SELECT * FROM product_tree p
WHERE
  (SELECT COUNT(*) FROM product_tree b WHERE p.product_id = b.product_id) > 1

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 September 2006 - 03:56 PM

A correlated subquery is not the most ideal... why not simply add "WHERE sectionID IN (1,4)" to the query?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 09 September 2006 - 07:12 PM

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!

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 07:15 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 09 September 2006 - 07:39 PM

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.

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 07:43 PM

Did my query above (reply #3) come close?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 09 September 2006 - 07:54 PM

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.

#11 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 09 September 2006 - 08:02 PM

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

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

#12 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 09 September 2006 - 08:13 PM

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

#13 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 09 September 2006 - 08:22 PM

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.

#14 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 09 September 2006 - 08:36 PM

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


#15 Buchead

Buchead
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts

Posted 09 September 2006 - 08:46 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users