How to exclude rows that don’t join with another table? – This article will take you through the common SQL errors that you might encounter while working with sql, join, . The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have two tables, one has primary key other has it as a foreign key.
I want to pull data from the primary table, only if the secondary table does not have an entry containing it’s key. Sort of an opposite of a simple inner join, which returns only rows that join together by that key.
SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key WHERE B.Key IS NULL
Full image of join
From aticle : http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx
SELECT * FROM primarytable P WHERE NOT EXISTS (SELECT * FROM secondarytable S WHERE P.PKCol = S.FKCol)
(NOT) EXISTS is a better choice then
(NOT) IN or
use a “not exists” left join:
SELECT p.* FROM primary_table p LEFT JOIN second s ON p.ID = s.ID WHERE s.ID IS NULL
Another solution is:
SELECT * FROM TABLE1 WHERE id NOT IN (SELECT id FROM TABLE2)
SELECT P.* FROM primary_table P LEFT JOIN secondary_table S on P.id = S.p_id WHERE S.p_id IS NULL
If you want to select the columns from First Table “which are also present in Second table, then in this case you can also use
EXCEPT. In this case, column names can be different as well but data type should be same.
select ID, FName from FirstTable EXCEPT select ID, SName from SecondTable
This was helpful to use in COGNOS because creating a SQL “Not in” statement in Cognos was allowed, but it took too long to run. I had manually coded table A to join to table B in in Cognos as A.key “not in” B.key, but the query was taking too long/not returning results after 5 minutes.
For anyone else that is looking for a “NOT IN” solution in Cognos, here is what I did. Create a Query that joins table A and B with a LEFT JOIN in Cognos by selecting link type: table A.Key has “0 to N” values in table B, then added a Filter (these correspond to Where Clauses) for: table B.Key is NULL.
Ran fast and like a charm.
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.