retrieve records not in joining table

lynx_lynx · #1 (**permalink**) 02-12-04, 17:32

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

aus · #2 (**permalink**) 02-12-04, 20:01

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;

lynx_lynx · #3 (**permalink**) 02-13-04, 12:51

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?

Quote:

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;

aus · #4 (**permalink**) 02-13-04, 15:27

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.

lynx_lynx · #5 (**permalink**) 02-13-04, 16:40

Aus, thanks a lot, works beautifully.

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech