CODEWITHSUNDEEP
phpmysqlsqlphp-5.5
Run
Run

Reputation: 57206

How to left join another nested table?

How can I left join another nested table?

The first query,

SELECT *
FROM category AS a
WHERE a.type = 'content'

Result,

category_id  type       code
1            content    content_1
2            content    content_2
3            content    content_3

Second query,

SELECT*
FROM content AS c

LEFT JOIN article_has_content AS x
ON x.content_id = c.content_id

WHERE x.article_id = '4'

Result,

category_id  value       article_id
1            xxa         4
2            xxb         4

The result I am after,

category_id  type       code          value
1            content    content_1     xxa
2            content    content_2     xxb
3            content    content_3     NULL

The third query,

SELECT*
FROM 
(
    SELECT *
    FROM category AS a
    WHERE a.type = 'content'
) a
LEFT JOIN b
(
    SELECT*
    FROM content AS c

    LEFT JOIN article_has_content AS x
    ON x.content_id = c.content_id

    WHERE x.article_id = '4'
) b
ON b.category_id = a.category_id

error,

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near '( SELECT* FROM content AS c

LEFT JOIN article_has_content AS x
ON x.content' at line 9

Upvotes: 2

Views: 6002

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You put the alias after the subquery. But in general, your query is just over-complicated. I think this does what you want:

SELECT c.category_id, c.type, c.code, con.value
FROM category c left join
     content con
     on c.category_id = con.category_id left join
     article_has_content ahc
     on con.content_id = ahc.content_id and
        ahc.article_id = '4';

Upvotes: 1

Bla...
Bla...

Reputation: 7288

How about combining those 2 query, like below:

SELECT*
FROM content AS c

LEFT JOIN article_has_content AS x
ON x.content_id = c.content_id

LEFT JOIN category a ON a.category_id =  c.category_id AND a.type = 'content'

WHERE x.article_id = '4'

Upvotes: -1

VMai
VMai

Reputation: 10336

You were nearly right:

SELECT *
FROM 
(
    SELECT *
    FROM category AS a
    WHERE a.type = 'content'
) a
LEFT JOIN                              -- the alias name here was wrong
(
    SELECT*
    FROM content AS c

    LEFT JOIN article_has_content AS x
    ON x.content_id = c.content_id

    WHERE x.article_id = '4'
) b                                    -- and you had the alias already here at the right position
ON b.category_id = a.category_id

Upvotes: 3

Related Questions