asked
Joining Two Tables
I have two tables. Cat and Data. Cat Cat_Serno Cat_Name Data Data_Serno Data_Name Data_Cat_ID Data_Project_ID Whn 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 > ![alt text][1] 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 a where Project=2 where 2 is not existing in data table I am getting a single row. ![alt text][2] I need all the columns of the Cat tables with respect to the projet_id and if there is no Project id then all the Cat columns with null in the Project Id [1]: /storage/temp/1484-one.png [2]: /storage/temp/1485-2.png
SELECT t1.*,t2.* FROM Cat t1 LEFT JOIN Data t2 ON t1.Cat_Serno = t2.Data_Cat_Id and Project_Id=2
You can use below query which uses Union Union will get distinct records so you can use the first seelct to pass project is and then second select will get the records having project id null SELECT t1.*,t2.* FROM @Cat t1 LEFT JOIN @Data t2 ON t1.Cat_Serno = t2.Data_Cat_Id Where Data_Project_ID=1 Union SELECT t1.*,t2.* FROM @Cat t1 LEFT JOIN @Data t2 ON t1.Cat_Serno = t2.Data_Cat_Id Remove @Sign before executing the query
16 People are following this question.
