Please help me out with this query. I can't seem to figure out how to make this work. I will give you an example:

Table Products { ID, Name }
Table Fruit { ID, Name }

Table Products_Fruit { ID, Products_ID, Fruit_ID }

Sample Data:
Products -
Record 1: [1, Bowl]
Record 2: [2, Jar]

Fruit -
Record 1: [1, Apple]
Record 2: [2, Pear]
Record 3: [3, Orange]
Record 4: [4, Grape]

Products_Fruit -
Record 1: [1, 1 (Bowl), 1 (Apple)]
Record 2: [2, 1 (Bowl), 2 (Pear)]
Record 3: [3, 1 (Bowl), 4 (Grape)]
Record 4: [4, 2 (Bowl), 2 (Pear)]

Basically this is what I am trying to accomplish.
"Give me all the products that contain Pear and Orange"
I thought I would the following: SELECT DISTINCT(Products_ID) from Products_Fruit WHERE Fruit IN (2, 3);

The output shows 1 and 2 as the products. It should really show only 1.

If I do a query like the following it works:
SELECT DISTINCT(Products_ID) FROM Products_Fruit a, Products_Fruit b where a.Fruit = 2 and b.Fruit = 3 and a.Products_ID = b.Products_ID

The problem with the above query is that it is not feasbile for a huge number of options.
This is just an example, my real query can expand to be really big. I want a way to write this query easily than doing the way I do!

I have posted this on other forums, but I am not having any luck. I many need to use JOINS. But everything I have tried does not work.

Hi Dawuad,
I was looking the data you put:

Record 1: [1, 1 (Bowl), 1 (Apple)]
Record 2: [2, 1 (Bowl), 2 (Pear)]
Record 3: [3, 1 (Bowl), 4 (Grape)]
Record 4: [4, 2 (Bowl), 2 (Pear)]

but, record # 4 says: 2 (Bowl) and must be 2 (Jar),
that´s why the output shows 1 and 2 as the products.
I´ve tried the same query with different sample data and it
works good.

Hope I Helped,
Roland

Sorry Roland, for the bad input. I apologize. Suppose that Record 4 is [4, 2(Jar), 2 (Pear)]. I know my query works, but I need to optimize it. You see this example only asks Products that contain Pear and Orange, but my real example can ask for like 20 fruits. If I did my query like how I showed it at a.Fruit = 1 and b.Fruit=2; and t.Fruit=3 . . . b.Fruit=3 and a.Products = b.Products_ID and ... t.Products_ID. You see how ugly and unefficient the query is. I need it to be simplier than this. Thanks for the help though.

Hi Dawuad,
precisely I´ve made some examples using 30 fruits and
different combinations between products and fruits.
I´ve been using this kind of query (that uses subqueries):

SELECT DISTINCT(Products_ID)
FROM Products_Fruit
WHERE Fruit_ID IN (4,9,2,8);

at this moment all outputs I´ve get from different sample
queries with different fruits-subqueries have been correct.
Also, I´ve tested with a query something like:

... WHERE Fruit_ID IN (SELECT Fruit_ID FROM Fruit WHERE Fruit_Name
LIKE '%a%');
only to see if outputs were correct, and they were.

Have you tried this?
Hope I helped.
Roland

Roland, it works but not exactly. Suppose I want Products that contain Pear and Orange. The query that you gave returns any Products that may contain Pear OR Orange. You see I just want Products that have Pear AND Orange.

Ok, I got it. I will try and ask to see if I can make the query!

Thanx!

I appreciate your help, however it does not work with my real life example. I have tried several forums, and they have given me similar queries. Let me reiterate the problem. The Database Table:

CREATE TABLE Products_Countries (
Products_Countries_ID INT AUTO INCREMENT PRIMARY KEY,
Products_ID INT NOT NULL,
Countries_ID INT NOT NULL);

I want to retrieve unique products that are from France, and England. IDs: 95 and 96 respectively.

Your query that was trasformed to fit my database:
SELECT Products.Products_ID FROM Countries INNER JOIN Products_Countries ON Countries.Countries_ID = Products_Countries.Countries_ID INNER JOIN Products ON Products_Countries.Products_ID = Products.Products_ID WHERE Countries.Countries_ID IN (95, 96) GROUP BY Products.Products_ID HAVING COUNT(DISTINCT Products.Products_ID) = 2;

Apparently it does not produce any output. However this query does work:
SELECT DISTINCT(a.Products_ID) FROM Products_Countries a, Products_Countries b WHERE a.Countries_ID = 95 and b.Countries_ID = 96 and a.Countries_ID = b.Countries_ID

The problem with this query is that it works but suppose I have a product that I want to retrieve that is from 10 countries. It comes for all ten countries. If you use my above query than it is difficult to use, and probably very inefficient.

I am not sure what I else I need to do. Is there really a solution to make this work? I am not sure of that as well. Thanks in advance!

that's because you did it wrong

it should be

... HAVING COUNT(DISTINCT Countries.Countries_ID) = 2

Thank You that worked. The final query if any one interested:

SELECT Products.Products_ID, Countries.Countries_ID FROM Countries INNER JOIN Products_Countries ON Countries.Countries_ID = Products_Countries.Countries_ID INNER JOIN Products ON Products_Countries.Products_ID = Products.Products_ID WHERE Countries.Countries_ID IN (95, 96) GROUP BY Products.Products_ID HAVING COUNT(DISTINCT Countries.Countries_ID) = 2;