Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: retrieve records not in joining table

    I'm trying to query 3 tables, 1 of them being a joining table.

    The three tables are 1-cars, 2-dealers, 3-sales


    I would like to find the dealers(criteria: location: New York) who have not made a sale for a car model type. The sales table consists of 2 fields (car_id, dealer_id).

    Not being able to use nested queries, I have been able to get the New York dealers who have sold Ford cars with this below query, but have had no luck trying to get the opposite, fnding the New York dealers who have yet to sell a Ford car. If anyone can help out with the second query, would be greatly appreciated.

    SELECT DISTINCT d.dealer_id
    FROM cars c, dealers d
    LEFT JOIN sales s ON d.dealer_id = s.dealer_id
    WHERE d.dealer_location = 'New York' AND c.model = 'Ford' AND c.car_id = s.car_id AND d.dealer_id = s.dealer_id

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: retrieve records not in joining table

    This uses the crosstab method, but ignores the crosstab details (the exact count of the sales numbers).
    Code:
    SELECT d.dealer_id
    FROM dealers d
    LEFT JOIN sales s ON s.dealer_id = d.dealer_id
    LEFT JOIN cars c ON c.car_id = s.car_id
    WHERE d.dealer_location = 'New York'
    GROUP BY d.dealer_id
    HAVING SUM(if(c.model='Ford', 1, 0)) = 0;

  3. #3
    Join Date
    Nov 2003
    Posts
    6

    Re: retrieve records not in joining table

    The query seems to retrieve all the New York dealers, even the ones who have never sold a Ford. I'm trying to get the NEw York dealers who have not sold a Ford'. Any suggestion?


    Originally posted by aus
    This uses the crosstab method, but ignores the crosstab details (the exact count of the sales numbers).
    Code:
    SELECT d.dealer_id
    FROM dealers d
    LEFT JOIN sales s ON s.dealer_id = d.dealer_id
    LEFT JOIN cars c ON c.car_id = s.car_id
    WHERE d.dealer_location = 'New York'
    GROUP BY d.dealer_id
    HAVING SUM(if(c.model='Ford', 1, 0)) = 0;

  4. #4
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: retrieve records not in joining table

    What is the output of your query? This next one will show you what the number of Ford sales are for each New York dealer. I know that this works (it just might need to be tweaked for your database).

    Code:
    SELECT d.dealer_id, d.dealer_location, sum(if(c.model='Ford', 1, 0)) AS fordsales
    FROM dealers d
    LEFT JOIN sales s ON s.dealer_id = d.dealer_id
    LEFT JOIN cars c ON c.car_id = s.car_id
    WHERE d.dealer_location = 'New York'
    GROUP BY d.dealer_id;
    Adding a HAVING clause will limit the results to those who have sold 0 Fords:

    Code:
    SELECT d.dealer_id, d.dealer_location, sum(if(c.model='Ford', 1, 0)) AS fordsales
    FROM dealers d
    LEFT JOIN sales s ON s.dealer_id = d.dealer_id
    LEFT JOIN cars c ON c.car_id = s.car_id
    WHERE d.dealer_location = 'New York'
    GROUP BY d.dealer_id
    HAVING fordsales = 0;
    The only thing that could be tripping you up with this is that the IF function may need a different test for the make of the car.

  5. #5
    Join Date
    Nov 2003
    Posts
    6

    Re: retrieve records not in joining table

    Aus, thanks a lot, works beautifully.

    Originally posted by aus
    What is the output of your query? This next one will show you what the number of Ford sales are for each New York dealer. I know that this works (it just might need to be tweaked for your database).

    Code:
    SELECT d.dealer_id, d.dealer_location, sum(if(c.model='Ford', 1, 0)) AS fordsales
    FROM dealers d
    LEFT JOIN sales s ON s.dealer_id = d.dealer_id
    LEFT JOIN cars c ON c.car_id = s.car_id
    WHERE d.dealer_location = 'New York'
    GROUP BY d.dealer_id;
    Adding a HAVING clause will limit the results to those who have sold 0 Fords:

    Code:
    SELECT d.dealer_id, d.dealer_location, sum(if(c.model='Ford', 1, 0)) AS fordsales
    FROM dealers d
    LEFT JOIN sales s ON s.dealer_id = d.dealer_id
    LEFT JOIN cars c ON c.car_id = s.car_id
    WHERE d.dealer_location = 'New York'
    GROUP BY d.dealer_id
    HAVING fordsales = 0;
    The only thing that could be tripping you up with this is that the IF function may need a different test for the make of the car.

Posting Permissions

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