CODEWITHSUNDEEP
mysqlsqljoinleft-joininner-join
Morpheus
Morpheus

Reputation: 9075

Join two part SQL query into one query

This is what I have:

Query

select
bc.short_desc as cdesc
from
blog_post as bp 
left join blog_post_category_link as blpc on bp.post_id = blpc.post_id
inner join blog_category as bc on blpc.cat_id = bc.cat_id
where bp.post_id = 1

Result

----------------
| |cdesc       |
----------------
|1|Top level   |
----------------
|2|Sub level   |
----------------
|3|SubSub level|
----------------

Second query

select
bt.short_desc as tdesc
from
blog_post as bp 
left join blog_post_tag_link as blpt on bp.post_id = blpt.post_id
inner join blog_tag as bt on blpt.tag_id = bt.tag_id
where bp.post_id = 1

Second result

----------------
| |tdesc       |
----------------
|1|Tag1        |
----------------
|2|Tag2        |
----------------

The desired result after joining these two queries

----------------------
| |cdesc       |tdesc|
----------------------
|1|Top level   |Tag1 |
----------------------
|2|Sub level   |Tag2 |
----------------------
|3|SubSub level|     |
----------------------

Is it possible at all?

Upvotes: 0

Views: 187

Answers (3)

Meherzad
Meherzad

Reputation: 8563

Try this query 

SET @rn1 = 0;
SET @rn2 = 0;

SELECT t1.rId, t1.cdesc, t2.tdesc
FROM 
(select @rn1 := rn1 + 1 AS rId, bc.short_desc as cdesc
from blog_post as bp 
left join blog_post_category_link as blpc on bp.post_id = blpc.post_id
inner join blog_category as bc on blpc.cat_id = bc.cat_id
where bp.post_id = 1) t1, 
(select @rn2 := rn2 + 1 AS rId, bt.short_desc as tdesc
from blog_post as bp 
left join blog_post_tag_link as blpt on bp.post_id = blpt.post_id
inner join blog_tag as bt on blpt.tag_id = bt.tag_id
where bp.post_id = 1) t2
WHERE t1.rId = t2.rId;

For MS SQL server

SELECT t1.rId, t1.cdesc, t2.tdesc
FROM 
(select row_number() over(order by bp.post_id) AS rId, bc.short_desc as cdesc
from blog_post as bp 
left join blog_post_category_link as blpc on bp.post_id = blpc.post_id
inner join blog_category as bc on blpc.cat_id = bc.cat_id
where bp.post_id = 1) t1, 
(select row_number() over(order by bp.post_id) AS rId, bt.short_desc as tdesc
from blog_post as bp 
left join blog_post_tag_link as blpt on bp.post_id = blpt.post_id
inner join blog_tag as bt on blpt.tag_id = bt.tag_id
where bp.post_id = 1) t2
WHERE t1.rId = t2.rId;

Upvotes: 2

Jakub Kania
Jakub Kania

Reputation: 16487

SELECT
bc.short_desc AS cdesc, bt.short_desc AS tdesc
FROM
blog_post AS bp
LEFT JOIN blog_post_category_link AS blpc ON bp.post_id = blpc.post_id
INNER JOIN blog_category AS bc ON blpc.cat_id = bc.cat_id
LEFT JOIN blog_post_tag_link AS blpt ON bp.post_id = blpt.post_id
INNER JOIN blog_tag AS bt ON blpt.tag_id = bt.tag_id
WHERE bp.post_id = 1

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

Use this template, and simply insert the queries from above in as sub-queries for T1 and T2:

Select *
from ( 
) T1 
join ( 
) T2  on T2.tdesc = T1.cdesc and T2.Tag1 = T1.TopLevel and T2.Tag2 = T1.SubLevel

Upvotes: 0

Related Questions