question

fashraf avatar image
fashraf 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
sqljoinsmssql
one.png (4.9 KiB)
2.png (4.0 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

you have done left join that why you are getting like that..
0 Likes 0 ·
fashraf avatar image
fashraf answered
SELECT t1.*,t2.* FROM Cat t1 LEFT JOIN Data t2 ON t1.Cat_Serno = t2.Data_Cat_Id and Project_Id=2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bhupendra99 avatar image
Bhupendra99 answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.