Complicated SQL query

nst2 · #1 (**permalink**) 02-02-12, 12:40

I am having difficulties with the following:

There is a table Customers nad another Cars

Obviously, ths is a m:n relation, therefore there is a third table containing only keys,
Customer_ID, Car_ID, CustomerRentsCar_ID

Now I need to get all Customers who 've rent e.g. a Mercedes but on a second column having an indication if he also rent an Alfa Romeo. That means that as soon as the query reaches the Car_ID of Mercedes it returns that Customer_ID, but in the same time it should check if there is another row with the same Customer_ID and the Car_ID for Alfa Romeo.

How can this be done?

Thanks.

Pat Phelan · #2 (**permalink**) 02-02-12, 13:23

Based on the information you've given, it isn't possible to answer your question. There is no table that includes information about car manufacturers so there isn't a way to query that information.

When you don't give all of the information needed to answer your question then we need to either guess at what you might possibly mean or ask you to provide the missing details.

-PatP

nst2 · #3 (**permalink**) 02-02-12, 16:11

I wrote it, there are 2 tables

Quote:

There is a table Customers nad another Cars

Customers:
Alan
Jones
Smith

Cars:
Chrysler
Mercedes
Citroen
Alfa Romeo

corncrowe · #4 (**permalink**) 02-02-12, 16:30

I thought there were three tables? Table three being the junction?

nst2 · #5 (**permalink**) 02-02-12, 16:54

Quote:

Originally Posted by corncrowe

I thought there were three tables? Table three being the junction?

Correct.
Table CustomerRentsCar:
CustomerRentsCar_ID, Customer_ID, Car_ID

And I would like to take:
Jones Mercedes
Alan Mercedes AlfaRomeo
Smith Mercedes

nst2 · #6 (**permalink**) 02-03-12, 12:59

I tried SELECT FROM (SELECT

unsuccessfully...

Pat Phelan · #7 (**permalink**) 02-03-12, 15:35

Code:

CREATE TABLE Customers (
   customer     NVARCHAR(50)        NOT NULL
   PRIMARY KEY (customer)
)

CREATE TABLE manufacturers (
   manufacturer NVARCHAR(50)        NOT NULL
   PRIMARY KEY (manufacturer)
)

CREATE TABLE customer_manufacturer (
   customer         NVARCHAR(50)    NOT NULL
   FOREIGN KEY (customer) REFERENCES customers (customer)
,  manufacturer     NVARCHAR(50)    NOT NULL
   FOREIGN KEY (manufacturer) REFERENCES manufacturers (manufacturer)
)

INSERT INTO customers (customer)
   SELECT 'Alan'
   UNION ALL SELECT 'Jones'
   UNION ALL SELECT 'Smith'

INSERT INTO manufacturers ( manufacturer)
   SELECT 'Chrysler'
   UNION ALL SELECT 'Mercedes'
   UNION ALL SELECT 'Citroen'
   UNION ALL SELECT 'Alfa Romeo'

INSERT INTO customer_manufacturer (customer, manufacturer)
   SELECT 'Jones', 'Mercedes'
   UNION ALL SELECT 'Alan', 'Mercedes'
   UNION ALL SELECT 'Alan', 'Alfa Romeo'
   UNION ALL SELECT 'Smith', 'Mercedes'
   
SELECT
   customer
,  CASE WHEN 0 < Count(CASE WHEN 'Alfa Romeo' = manufacturer THEN 1 END) THEN 'Alfa Romeo' END AS 'Alfa Romeo'
,  CASE WHEN 0 < Count(CASE WHEN 'Chrysler' = manufacturer THEN 1 END) THEN 'Chrysler' END AS 'Chrysler'
,  CASE WHEN 0 < Count(CASE WHEN 'Citroen' = manufacturer THEN 1 END) THEN 'Citreon' END AS 'Citreon'
,  CASE WHEN 0 < Count(CASE WHEN 'Mercedes' = manufacturer THEN 1 END) THEN 'Mercedes' END AS 'Mercedes'
   FROM customer_manufacturer
   GROUP BY customer

DROP TABLE customer_manufacturer
DROP TABLE manufacturers
DROP TABLE Customers

-PatP

Wim · #8 (**permalink**) 02-04-12, 03:34

It's hard to tell what you really want. You have Pat's interpretation. This is another interpretation:

Code:

DECLARE @FirstManufacturer NVARCHAR(50)
DECLARE @SecondManufacturer NVARCHAR(50)

SET @FirstManufacturer = 'Mercedes'
SET @SecondManufacturer = 'Alfa Romeo'

SELECT
   F.customer,
   F.manufacturer,
   S.manufacturer
   FROM (SELECT customer, manufacturer
	FROM customer_manufacturer
	WHERE manufacturer = @FirstManufacturer
	) as F
	INNER JOIN (SELECT customer, manufacturer
			FROM customer_manufacturer
			WHERE manufacturer = @SecondManufacturer
			) as S ON
		F.customer = S.customer

This is yet another interpretation:

Code:

SELECT
   F.customer,
   F.manufacturer,
   S.manufacturer
   FROM (SELECT customer, manufacturer
	FROM customer_manufacturer
	WHERE manufacturer = @FirstManufacturer
	) as F
	INNER JOIN (SELECT customer, manufacturer
			FROM customer_manufacturer
			WHERE manufacturer <> @FirstManufacturer
			) as S ON
		F.customer = S.customer

nst2 · #9 (**permalink**) 02-04-12, 05:09

Thank you both, I think it works with certain modifications, since it is a part of a more complicated query. I will try it next week at the office database.

Quote:

Originally Posted by Wim

It's hard to tell what you really want.

Do you mean, the way I express it is not clear?

Wim · #10 (**permalink**) 02-04-12, 17:55

Quote:

Now I need to get all Customers who 've rent e.g. a Mercedes but on a second column having an indication if he also rent an Alfa Romeo. That means that as soon as the query reaches the Car_ID of Mercedes it returns that Customer_ID, but in the same time it should check if there is another row with the same Customer_ID and the Car_ID for Alfa Romeo.

This can be interpreted in many ways:
- you want an overview of all the brands a customer has rented (Pat's solution/interpretation)
- you want all customers who have rented a Mercedes AND an Alfa Romeo.
(my first solution/interpretation)
- you want all customers who have rented a Mercedes AND an a car of any other brand. (my second solution/interpretation)
- you want all customers who have rented exactly two different brands of cars
- you want all customers who have rented at least two different brands of cars
- What with a customer who has rented a Mercedes twice. You want those too, or only when they rent other brands too. (your data model won't allow to store that data)

For each of the above questions the query must be adjusted. The performance could vary greatly among the queries.

Quote:

Do you mean, the way I express it is not clear?

You're the only one on this forum who knows exactly what your business rules are. It's hard for us to grasp what the real question is behind a few lines of explanation. Something that works very well is to give some sample data, the result you want and some extra explanation why some data are in the result set and others not.

Internet Services

Web Development

Digital Marketing

Consumer Tech