Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Trying to eliminate nested queries

    I have two tables. The first is a table of vehicles. The second is a table of events that have occurred to those vehicles (purchased, sold, accident, destroyed, etc). The events table has columns for the event date and the type of event. Obviously one vehicle may have multiple events associated with it.

    I've created a query that returns the vehicle with its purchased and sold dates (assumes that only one of each event exist per vehicle, which is a good assumption in my case, but I don't have logic to restrict it). The query involves two nested queries which are essentially the same, except for the WHERE clause. I'm wondering if there is a way to simplify it. While the example below is pretty simple, in reality the nested queries are a bit more complex that I've shown -- but they are indeed identical, except for the WHERE clause. In addition, the actual query will likely have two or three additional columns, all from other similar nested queries.

    Code:
    SELECT	Vehicle.Name, BuyDate.EventDate AS [Purchased], SellDate.EventDate AS [Sold]
    FROM	Vehicle 
    LEFT OUTER JOIN
    	(SELECT Event.Vehicle_ID, Event.EventDate
    	FROM    Event INNER JOIN
    	WHERE  	(EventType = 'Purchase')) AS BuyDate 
    ON Vehicle.Vehicle_ID = Event.Vehicle_ID 
    LEFT OUTER JOIN
    	(SELECT Event.Vehicle_ID, Event.EventDate
    	FROM    Event INNER JOIN
    	WHERE  	(EventType = 'Sale')) AS SellDate 
    ON Vehicle.Vehicle_ID = Event.Vehicle_ID

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Your example is invalid because of this part:
    Code:
    SELECT Event.Vehicle_ID, Event.EventDate
    FROM    Event INNER JOIN
    WHERE  	(EventType = 'Purchase')
    You have two errors in your SQL

    - You are missing a table after the INNER JOIN or the INNER JOIN is simply not necessary.
    - this is not ANSI SQL because of the illegal column alias: [Sold] (illegal because of the brackets. ANSI SQL requires double quotes for object names)

    having said this, you don't need sub-selects here:

    Code:
    SELECT  Vehicle.Name, 
            BuyDate.EventDate AS Purchased, 
            SellDate.EventDate AS Sold
    FROM Vehicle 
    LEFT OUTER JOIN event AS BuyDate 
       ON Vehicle.Vehicle_ID = Event.Vehicle_ID AND event.eventType = 'Purchase'
    LEFT OUTER JOIN event as SellDate 
       ON Vehicle.Vehicle_ID = Event.Vehicle_ID AND event.eventType = 'Sale'

  3. #3
    Join Date
    Aug 2010
    Posts
    57

    I oversimplified my example

    Agh. I oversimplified my example, to the point that as shammat stated, I no longer need the nested queries. In my original, each of the nested queries has some joins in them (That's the reason for the dangling INNER JOIN in the original example, which is obviously incorrect).

    A less simplified variant (hopefully with no more errors):
    Code:
    SELECT	Vehicle.Name, 
    		BuyDate.EventDate AS Purchased, 
    		SellDate.EventDate AS Sold
    FROM Vehicle 
    LEFT OUTER JOIN
    	(SELECT Event.Vehicle_ID, Event.EventDate
    	FROM    Event 
    	INNER JOIN EventTypes
    	ON Event.EventType_ID = EventTypes.EventType_ID
    	WHERE  	(EventType = 'Purchase')) AS BuyDate 
    ON Vehicle.Vehicle_ID = Event.Vehicle_ID 
    LEFT OUTER JOIN
    	(SELECT Event.Vehicle_ID, Event.EventDate
    	FROM    Event 
    	INNER JOIN EventTypes
    	ON Event.EventType_ID = EventTypes.EventType_ID
    	WHERE  	(EventType = 'Sale')) AS SellDate 
    ON Vehicle.Vehicle_ID = Event.Vehicle_ID

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The only thing I can think of to make this simpler is to use a CTE:
    Code:
    WITH evt AS (
        SELECT Event.Vehicle_ID, Event.EventDate, EventTypes.EventType
        FROM Event 
          INNER JOIN EventTypes
             ON Event.EventType_ID = EventTypes.EventType_ID
    ) 
    SELECT	Vehicle.Name, 
            BuyDate.EventDate AS Purchased, 
            SellDate.EventDate AS Sold
    FROM Vehicle 
    LEFT OUTER JOIN evt AS buyDate 
       ON buyDate.Vehicle_Id = vehicle.vehicle_ID AND evt.EventType = 'Purchase'
    LEFT OUTER JOIN evt AS sellDate 
       ON sellDate.Vehicle_Id = vehicle.vehicle_ID AND evt.EventType = 'Sale'

  5. #5
    Join Date
    Aug 2010
    Posts
    57
    Thanks, that is perfect. I didn't know about CTEs - it is exactly what I needed in this scenario. While in the example query it does not seem to simplify things much, in my real query it makes it much more readable. (And easier to document!)

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Beware of performance pitfalls. A CTE can make things faster or slower.

    If the CTE reduces a large result set to just a few rows, then it makes things probably faster (by re-using the small result).

    If the result of the CTE is so large that it doesn't fit into memory, it will spill to disk and thus will make things slower (mainly because nothing will be indexed in that result)

  7. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    I've created a query that returns the vehicle with its purchased and sold dates (assumes that only one of each event exist per vehicle, which is a good assumption in my case, but I don't have logic to restrict it).
    Does this mean you want exactly one purchased date and exactly one sold date for each vehicle? (No matter which dates, if several exists?)

    If that's what you want, I'd use correlated subqueries:

    SELECT Vehicle.Name,
    (SELECT MAX(EventDate) FROM evt
    WHERE Vehicle_Id = V.vehicle_ID AND EventType = 'Purchase') AS Purchased,
    (SELECT MAX(EventDate) FROM evt
    WHERE Vehicle_Id = V.vehicle_ID AND EventType = 'Sold') AS Sold
    FROM Vehicle as V


    The MAX aggregate is there just in case several sold/purchased dates exist for a vehicle.

Posting Permissions

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