prabhjot prabhjot - 3 months ago 7
SQL Question

getting datafrom two tables collectively and get all the rows from table A

I have two tables from where i want the rows to come collectively.
table a is like this

id a_name image category
1 name1 image1.png cate1
2 name2 image2.png cate2
3 name3 image3.png cate3
4 name4 image4.png cate3
5 name5 image5.png cate1
6 name6 image6.png cate2
7 name7 image7.png cate3
8 name8 image8.png cate1


and a table b like this (a_id is the id of any of the above table's row)

id user_id a_id email amount code active
1 123 2 s@s 23 23ke 1
2 542 1 s@s 23 23ke 1
3 523 2 s@s 23 23ke 1
4 423 6 s@s 23 23ke 1
5 103 5 s@s 23 23ke 1
6 523 5 s@s 23 23ke 0


i want to join two tables in such a way that whenever i run the query it should return all the rows of table A joined with table B. i have used join but it gets only the rows where join is successfully applied. the rows to be fetched from table b should be in terms of user_id also.
for example:
if iam searching for the data of user with user_id =523. then it should output following. (id field is from table a)

id a_name image user_id email amount code active
1 name1 image1.png - - - - -
2 name2 image2.png 523 s@s s@s 23KE 1
3 name3 image3.png - - - - -
4 name4 image4.png - - - - -
5 name5 image5.png 523 s@s 23 23KE 0
6 name6 image6.png - - - - -
7 name7 image7.png - - - - -
8 name8 image8.png - - - - -


I am using mysql.

Answer

You have to use Outer Join,

SELECT * from a LEFT OUTER JOIN b ON a.id = b.a_id AND b.user_id = 523