CODEWITHSUNDEEP
mysqlsqlt-sqlleft-join
Bill
Bill

Reputation: 19268

sql join two table

TABLE A >>
uid name
1   test1
2   test2
3   test3
4   test4

TABLE B >>
uid address
1   address1
2   address2
4   address3

RESULT
1   test1   address1
2   test2   address2
3   test3
4   test4   address3

Can anyone show me how to write a query and fetch the result as above, Thanks very much! i have tried join, left and right join. all result nothing.

Upvotes: 12

Views: 22521

Answers (5)

Pranay Rana
Pranay Rana

Reputation: 176896

You can write left outer join between this two tables Best way to understand is check the below image

Query for your requirement 

SELECT A.uid, A.name, B.address FROM A LEFT JOIN B ON A.uid=B.uid

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

alt text

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

Upvotes: 20

Pavel Donchev
Pavel Donchev

Reputation: 1889

I guess you're after an empty value if there is no value for B, that is having the same uid in A.

If this is the case, IFNULL will return the default value you specified in case the parameter is null (ISNULL is used in MSSQL):

SELECT A.value, IFNULL(B.value, '')
FROM A LEFT JOIN B
ON A.uid = B.uid

This will produce something like:

test1   address1
test2   address2
test3   
test4   address3

Upvotes: 1

Suresh
Suresh

Reputation: 1504

You can use any join.I write this query for full join.

select A.uid,A.name,B.address from A FULL JOIN B ON A.uid = B.uid

Upvotes: 1

Kai G
Kai G

Reputation: 3492

SELECT A.uid, A.name, B.address FROM A LEFT OUTER JOIN B ON A.uid = B.uid

Upvotes: 2

mathematical.coffee
mathematical.coffee

Reputation: 56915

You say you tried a left join but didn't give any attempts --- one of the first logical attempts would have been:

SELECT A.uid, A.name, B.address
FROM A
LEFT JOIN B ON A.uid=B.uid

Hey presto! it gives you what you were after.

Upvotes: 1

Related Questions