CODEWITHSUNDEEP
sqljoin
Benjamin Allison
Benjamin Allison

Reputation: 2154

Join with select SQL query?

I'm trying to run a query on a table (the one Wordpress uses) where I want to select the ID and post_type columns from one table, then do a Left Join to another table, two separate times (getting separate data).

This is what I have so far, but it's not cutting the mustard:

SELECT derby_posts.id        AS pID, 
       derby_posts.post_type AS tier 
FROM   derby_posts 
       LEFT JOIN (SELECT derby_postmeta.post_id  AS dbID1, 
                         derby_postmeta.meta_key AS dbMeta1) 
              ON pid = dbid1 
                 AND dbmeta1 = 'twitter' 
       LEFT JOIN (SELECT derby_postmeta.post_id  AS dbID2, 
                         derby_postmeta.meta_key AS dbMeta2) 
              ON pid = dbid2 
                 AND dbmeta2 = 'website' 
WHERE  tier IN ('local', 'regional', 'national')

I'm sure I'm missing something super simple...

Edit: here's the solution that worked for me. Table alias helped, putting all my SELECT statements together cleaned things up. Also, I realized I could remove items from the SELECT, even though I'm using them in the Join, which cleans up the results a lot.

SELECT 
    db.ID as id, 
    db.post_type as tier,
    dpm1.meta_value as twitter,
    dpm2.meta_value as website
FROM derby_posts db
LEFT JOIN derby_postmeta dpm1 ON (db.ID = dpm1.post_id AND dpm1.meta_key = 'twitter' )
LEFT JOIN derby_postmeta dpm2 ON (db.ID = dpm2.post_id AND dpm2.meta_key = 'website' )
WHERE db.post_type IN ('local','regional','national')

Upvotes: 1

Views: 123

Answers (2)

Leeish
Leeish

Reputation: 5213

SELECT 
    db.derby_posts.ID as pID, 
    db.derby_posts.post_type as tier,
    dpm1.post_id as dbID1,
    dpm1.meta_key as dbMeta1,
    dpm2.post_id as dbID2,
    dpm2.meta_key as dbMeta2
FROM derby_posts db
LEFT JOIN derby_postmeta dpm1 ON (db.pID = dpm1.post_id AND dpm1.meta_key= 'twitter')
LEFT JOIN derby_postmeta dpm2 ON (db.pID = dbm2.post_id AND dbm2.meta_key = 'website')
WHERE tier IN ('local','regional','national')

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726539

I 'm sure I'm missing something super simple...

You are right!

You need to give your selects an alias, and use that alias in the ON clause. You are also missing a FROM <table> - a required part of a SELECT statement that reads from a table:

LEFT JOIN (
    SELECT derby_postmeta.post_id  AS dbID1, 
           derby_postmeta.meta_key AS dbMeta1
    FROM someTable
) dpm ON pid = dpm.dbid1 AND dpm.dbmeta1 = 'twitter'

I gave the results of your SELECT an alias dpm, and used it to "link up" the rows from the inner select to the rows of your outer select.

Upvotes: 2

Related Questions