0

I have two tables as such. 

t1                                 
id name                 
1  foo                  
2  bar 

t2
tid id address
1   1  foss

I can execute select satement to select id 1 from t1 from both tables.

select * from t1 inner join t2 on (t1.id = 1 and t2.id = 1)

But, what I actually want is select bar from t1, and only bar, even though it isn't present in t2.

The above tables are similar to what I have been working on my project, and resembles to the same problem.

Here is what I tried.

1. I used left join t1.id = 2 and t2.id = 2

It lists all the values from t1, which I only wanted t1.id = 2. 

2.inner join could be used, but since t2.id = 2 is not present.

Therefore, it doesn’t return anything.

But I wanted something that would display data from t1 and t2 if id is present in both tables, and if not display only from table t1 of only that id.

So, if I wanted to display t1.id = 2, the answer I would expect is using

select * from t1 join .....

id name  tid   id    address
2  bar   null null    null
Rockink
  • 180
  • 4
  • 17
  • You may want to revisit this thread in SO [link](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) and some different ideas for different joins. – krause Jul 26 '15 at 11:54

2 Answers2

1

Based on your expected result this is a simple Left Join:

select * 
from t1 left join t2 
on t1.id = t2.id
where t1.id = 2
dnoeth
  • 59,503
  • 4
  • 39
  • 56
0

Use left join to get all elements of t1 even if a element of t2 with same id not exists. Then use null to get only t1 when t2 not exists. At last, you can add a where filter to get a subset of t1 (ie: id = 2)

SELECT t1.*, t2.*
    FROM t1
    LEFT JOIN t2
        ON t1.id = t2.id
    WHERE t2.id IS NULL
          AND t1.id = 2;
BufferUnderRun
  • 161
  • 1
  • 14