Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: SQL Query NOT Working

    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.

  2. #2
    Join Date
    Jun 2004
    Posts
    21
    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),
    thats why the output shows 1 and 2 as the products.
    Ive tried the same query with different sample data and it
    works good.

    Hope I Helped,
    Roland

  3. #3
    Join Date
    Jul 2004
    Posts
    5
    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.

  4. #4
    Join Date
    Jun 2004
    Posts
    21
    Hi Dawuad,
    precisely Ive made some examples using 30 fruits and
    different combinations between products and fruits.
    Ive 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 Ive get from different sample
    queries with different fruits-subqueries have been correct.
    Also, Ive 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

  5. #5
    Join Date
    Jul 2004
    Posts
    5
    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.

  6. #6
    Join Date
    Jun 2004
    Posts
    21
    Ok, I got it. I will try and ask to see if I can make the query!

    Thanx!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select P.ID   as ProductID
         , P.Name as ProductName
      from Fruit F
    inner
      join Products_Fruit PF
        on F.ID = PF.Fruit_ID
    inner
      join Products P
        on PF.Products_ID = P.ID   
     where F.Name in ('Pear','Orange')    
    group
        by P.ID  
         , P.Name  
    having count(distinct F.Name) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2004
    Posts
    5
    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!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Dawuad
    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.
    that's because you did it wrong

    it should be

    ... HAVING COUNT(DISTINCT Countries.Countries_ID) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2004
    Posts
    5
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •