Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2005
    Posts
    23

    Unanswered: Complicated SQL query

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2005
    Posts
    23
    I wrote it, there are 2 tables
    There is a table Customers nad another Cars
    Customers:
    Alan
    Jones
    Smith

    Cars:
    Chrysler
    Mercedes
    Citroen
    Alfa Romeo

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I thought there were three tables? Table three being the junction?

  5. #5
    Join Date
    Sep 2005
    Posts
    23
    Quote Originally Posted by corncrowe View Post
    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

  6. #6
    Join Date
    Sep 2005
    Posts
    23

    Unhappy

    I tried SELECT FROM (SELECT

    unsuccessfully...

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Sep 2005
    Posts
    23

    Smile

    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 View Post
    It's hard to tell what you really want.
    Do you mean, the way I express it is not clear?
    Last edited by nst2; 02-04-12 at 06:32.

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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