Having trouble with a difference query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Having trouble with a difference query

Author

Topic

Jackson
Starting Member

6 Posts

Posted - 2008-05-28 : 19:14:34

Hi, I have created a query (using SQL 2005) that will pull the people who have spent the most on tickets purchased:

Select P.Passenger_ID, Passenger_Name, Ticket_Price
From Passenger P, Ticket_Purchase T
Where P.Passenger_ID = T.Passenger_ID
Group By P.Passenger_ID, Passenger_Name, Ticket_Price
Having Ticket_Price >= All (Select Max(Ticket_Price)
From Ticket_Purchase
Group By Ticket_Price);

Passenger_ID Passenger_Name Ticket_Price
---------------------------------------
132812298 Nice,Richard 1750.00
234890032 Franco,Sylvia 1750.00
339209841 Kim,Jongouk 1750.00
385894857 Uribe,Gloria 1750.00

(4 row(s) affected)

I now want to be able to only choose the Passenger_ID's from above who are not listed in another table called Frequent_Flier, which should leave me with only 2 records not 4.

I am wondering if I add the below to the first query to eliminate those passengers in the Frequent_Flier table:
NOT IN (Select Passenger_ID
From Frequent_Flier);

When I add it to the Where clause I get an error. Should I be sub-querying that differently or is there a better way to do this.

Thanks for any help you can offer.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 19:17:19

Try this:


SELECT Passenger_ID
FROM 
(
	YourCurrentQueryGoesHere
) t
WHERE NOT EXISTS (SELECT * FROM Frequent_Flier f WHERE t.Passenger_ID = f.Passenger_ID)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Jackson
Starting Member

6 Posts

Posted - 2008-05-28 : 19:24:50

Thank you. That worked great. One question, what does the lowercase 't' after the parenthesis mean that is after the query? Does that reference the alias I gave Ticket_Purchase?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 19:27:33

It is an alias for the derived table. You can use a different name if you'd like. For shortness in these forums, I juse use t or dt to indicate a derived table. In your actual code, I'd suggest using a more descriptive name to make it clearer down the road.

I see you edited your post, so let me comment on that part. Your T alias is different from my t alias. The inner aliases are not visible in the outer query, so even if both are named the same, you will not get an error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Jackson
Starting Member

6 Posts

Posted - 2008-05-28 : 19:28:37

Thank you so much. Learning everyday here. I appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources