CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
Fhd.ashraf
Fhd.ashraf

Reputation: 537

Joining Two Tables with Where Clause

I have two tables. Cat and Data.

Cat
  Cat_Serno
  Cat_Name 

 Data
  Data_Serno
  Data_Name
  Data_Cat_ID
  Data_Project_ID

When i Am doing a regular join I am getting

 SELECT t1.*,t2.* 
 FROM Cat t1 
      LEFT JOIN Data t2 ON t1.Cat_Serno = t2.Data_Cat_Id

enter image description here

but when I apply a where condition on Project_Id it gives me only one column. I want to Display all the category and Null if there is no related data in the Data table along with the where clause on the Project_Id. It should also contain Null if I am using a where clause with a project_id without any value in the Data table (eg: where Project_Id=2) even if 2 is not present in the Data Table.

When I do it with Project_Id=2 which is not existing in Data Table I only get one Record with Null Values. enter image description here

Upvotes: 0

Views: 257

Answers (4)

Pred
Pred

Reputation: 9042

You have to add the where condition with the required values and add an OR condition with IS NULL

Example:

WHERE (DataName = 'Data_One' OR DataName IS NULL)

Please note, that NULL is not equals to any values (including NULL), so you have to handle it.

Another way:

WHERE COALESCE(DataName, 'n/a') IN ('Data_One', 'n/a')

Upvotes: 1

MinhD
MinhD

Reputation: 1810

Try:

WHERE t2.project_id = ...
OR t2.project_id IS NULL;

Upvotes: 0

AK47
AK47

Reputation: 3797

If you include column of table Data in where clause, your join will almost act as inner join, so if you want all records of Cat table, you should not include any column of Data table in where clause, still if you want to apply condition you can include it in "on" in join Try this,

SELECT t1.*,t2.* 
 FROM Cat t1 
      LEFT JOIN Data t2 ON t1.Cat_Serno = t2.Data_Cat_Id
          and Project_Id=2

Upvotes: 1

Dan
Dan

Reputation: 10680

If you want to return records from both Data and Cat, when there is no matching records in the opposite table, do a FULL OUTER JOIN, instead of a LEFT/RIGHT join.

Furthermore, to filter records from either table without filtering the entire result set, apply your conditions in the ON part of the statement, rather than the WHERE part of the statement. For example:

SELECT t1.*, t2.* FROM Cat t1 FULL OUTER JOIN Data t2 
     ON t1.Cat_Serno = t2.Data_Cat_Id 
    AND t2.Data_Project_Id = 2

Upvotes: 0

Related Questions