I have two mysql tables. ie,
//db_post
id || name || username || unique_key || pub
1 Jit jit11 unkey1 demo
2 Rah rah11 unkey2 demo1
3 dee dee11 unkey3 demo2
//db_like
id || post_id || unique_key
1 2 unkey3
My question is, how to mix this two tables according to unique_key field in table db_post.
//output should be like below. (WHERE unique_key='unkey3')
id || name || unique_key || pub
3 dee unkey3 demo2
2 Rah unkey3 demo1 -> Result from table db_like
I don't understand why the answer given by @tango has been accepted, the query does not give the desire output, it returns this:
id || name || unique_key || id
3 dee unkey3 1
In fact I don't see how you can obtain the output you wrote in your question by joining these two tables with a single join.
Either you join using the tables using the unique_key column, like this :
select db_post.id, db_post.name, db_post.unique_key, db_post.pub
from db_post
left join db_like on db_post.unique_key = db_like.unique_key
where db_post.unique_key = 'unkey3';
and you obtain the first row of your desired output :
id || name || unique_key || pub
3 dee unkey3 demo2
either you join the two tables using db_post.id = db_like.post_id:
select db_post.id, db_post.name, db_like.unique_key, db_post.pub
from db_post
left join db_like on db_post.id = db_like.post_id
where db_like.unique_key = 'unkey3';
and you obtain the second row of your desired output:
id || name || unique_key || pub
2 Rah unkey3 demo1
To obtain both rows you have to use union:
select db_post.id, db_post.name, db_post.unique_key, db_post.pub
from db_post
left join db_like on db_post.unique_key = db_like.unique_key
where db_post.unique_key = 'unkey3'
union
select db_post.id, db_post.name, db_like.unique_key, db_post.pub
from db_post
left join db_like on db_post.id = db_like.post_id
where db_like.unique_key = 'unkey3';
as per my understanding, you are asking for SQL for said problem. if its the case, then following would be join between two tables.
select p.id, p.name, p.unique_key, l.id
from db_post p
left outer join db_like l on
p.unique_key = l.unique_key
where p.unique_key='unkey3'
if my comment satisfies your problem then mark it as correct answer to help other reader in future.
use this code for joining two tables
select a.id, a.name, a.unique_key
from db_post a, db_like b WHERE
a.unique_key = b.unique_key AND a.unique_key='unkey3' GROUP BY a.unique_key