December 3, 2011 at 9:06 am
Hi ,
Sorry if my SQL Syntax is not correct, I am typing this from memory.
But basically my question is - how do I exclude matching rows from my report ?
I am trying to exclude some records from a query.
Table 1 (City)
Name , City, Zip, CustomerID
Peter, Dallas, 12345, 1
Paul, New York, 29292, 2
John, Piscataway, 77320, 3
Table 2 (State)
State, CustomerID
Texas, 1
New York, 2
New Jersey, 3
Table 3 (Paid)
CustomerID
1
3
My Code so far :
Select City.Name, City.City, State.State
FROM City
Inner Join State ON State.CustomerID = City.CustomerID
Inner Join Paid ON City.CustomerID = Paid.CustomerID
WHERE City.CustomerID <> Paid.CustomerID
---------
This works with one record only.
So it sees that Customer 1 has paid but it does not seem to "loop" to Customer 3
Any Ideas why ?
December 3, 2011 at 11:04 am
Maybe using EXCEPT migt be better here. Not sure without table def, sample data and expected result... (see the first article in my signature for details on how to post that information).
December 3, 2011 at 11:12 pm
Also without knowing your entire situation and schema, I would take the NOT IN approach as it is slightly easier than the EXCEPT syntax. The EXCEPT syntax requires that the second query contains all the same fields in the result as if you were performing a UNION.
Not really sure that this is what you intended, but offers you an option to exclude a set of records.
Select City.Name, City.City, State.State
FROM City
Inner Join State ON State.CustomerID = City.CustomerID
Inner Join Paid ON City.CustomerID = Paid.CustomerID
WHERE City.CustomerID NOT IN (SELECT Paid.CustomerID FROM Paid)
-Eric
-Eric
December 4, 2011 at 7:10 am
Eric Niemiec (12/3/2011)
Also without knowing your entire situation and schema, I would take the NOT IN approach as it is slightly easier than the EXCEPT syntax.
Except that NOT IN exhibits some interesting (and usually undesired) behavior if there are any NULL values returned by the subquery. (This is because NOT IN uses AND logic, while regular IN uses OR logic). In fact, even if there are no NULL values returned, if the column being queried has been declared nullable in the table definition, you can experience serious performance problems.
I've had to fix enough code that used the NOT IN approach, I now prefer to play it safe and use NOT EXISTS instead of NOT IN when the values list is being generated by a subquery. I prefer to use NOT IN only if a have a static list of values I'm comparing to, e.g. NOT IN ('X', 'Y', 'Z').
Eric Niemiec (12/3/2011)
Select City.Name, City.City, State.State
FROM City
Inner Join State ON State.CustomerID = City.CustomerID
Inner Join Paid ON City.CustomerID = Paid.CustomerID
WHERE City.CustomerID NOT IN (SELECT Paid.CustomerID FROM Paid)
If you're doing an INNER JOIN between City and Paid on Customer ID, the only rows returned will be those where City has a Customer ID that is also in Paid. In this example, the NOT IN clause negates your join predicate, and the query will never return any rows.
December 4, 2011 at 7:20 am
Using NOT EXISTS, here's how you would return all rows from City and State with a value of CustomerID that does not exist in Paid:
SELECT City.Name, City.City, State.State
FROM City INNER JOIN
State ON State.CustomerID = City.CustomerID
WHERE NOT EXISTS (SELECT 1 FROM Paid WHERE Paid.CustomerID = City.CustomerID)
And since there's more than one way to skin this cat, you could get the same results by using a LEFT OUTER JOIN between City and Paid, and then filtering our all matches where Paid.CustomerID is not NULL:
SELECT City.Name, City.City, State.State
FROM City INNER JOIN
State ON State.CustomerID = City.CustomerID LEFT OUTER JOIN
Paid ON Paid.CustomerID = City.CustomerID
WHERE Paid.CustomerID IS NULL
Of course I'm not completely sure that's what you were trying to accomplish; the DDL, sample data, and expected results that LutzM requested would be very useful in order to help you further.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply