JOIN 4 Tables with meta_table

Question

I have this database structure:

sites
id | name
1  | Site 1
2  | Site 2

locations
id | city
23 | Baltimore
24 | Annapolis

people
id | name
45 | John
46 | Sue

sites_meta
id | site_id | meta_name | meta_value
1  |    1    |   local   |     23
2  |    1    |   person  |     45
3  |    2    |   local   |     24
4  |    2    |   person  |     46

So, as you can see, Site 1 (id 1) is in Baltimore and is associated with John, Site 2 (id 2) is in Annapolis and associated with Sue.

I need to figure out a clever sql statement that can return

id |   name   | id |    city    | id | name
 1 |  Site 1  | 23 |  Baltimore | 45 | John
 2 |  Site 2  | 24 |  Annapolis | 46 | Sue

I would be super appreciative if anyone can help me out. I've tried a few combinations of a select statement, but I keep getting stuck with using two values from the sites_meta table.

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

select
    s.id as siteId,
    s.name as siteName,
    max(l.id) as locationId,
    max(l.city) as city,
    max(p.id) as personId,
    max(p.name) as personName
from
    sites_meta sm
    join sites s on s.id = sm.site_id
    left join locations l on l.id = sm.meta_value and sm.meta_name = 'local'
    left join people p on p.id = sm.meta_value and sm.meta_name = 'person'
group by
    s.id,
    s.name

You can probably imagine how this kind of "meta" table might become a pain... especially as more items are added to it.

Instead, you might consider replacing it with two new tables, sites_locations and sites_people.

edited May 23 '17 at 11:56

Community

1
1

answered Feb 24 '12 at 20:24

Michael Fredrickson

36,839
5
92
109

Thanks so much for your quick response. However, when I run your query, I get a blank result set. Any ideas why or another approach? – danbrellis Feb 24 '12 at 20:40
@user1231466 Please see my updated answer... since a single `sites_meta` row can only `join` to either locations or people, it has to do a `left join`, and then roll up the two matching records using `max()`. – Michael Fredrickson Feb 24 '12 at 20:51
Hey, thanks again. This works great. In reality though, my sites, people and locations table all have more columns than two. Is there a way to select all columns from locations and people? SELECT s.* works but I can't do a max(l.*) or max(p.*). I can certainly list them in the select query, but I didn't know if there was an easier way. Thanks again. – danbrellis Feb 24 '12 at 21:33
Glad it helped... if it answered your question, [please mark it as the answer.](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). I think it would probably be [best to list out all of the columns in your select statement](http://stackoverflow.com/questions/3180375/select-vs-select-column), even if the `max()` allowed it. – Michael Fredrickson Feb 24 '12 at 21:38

RolandoMySQLDBA · Answer 2 · 2012-02-24T21:52:26.937

0

SELECT
    s.id,s.name,l.id,l.city,p.id,p.name
FROM
    sites s
    INNER JOIN sites_meta sm1 ON s.id = sm1.site_id
    INNER JOIN sites_meta sm2 ON s.id = sm2.site_id
    INNER JOIN locations l ON sm1.meta_value = l.id AND sm1.meta_name = 'local' 
    INNER JOIN people p ON sm2.meta_value = p.id AND sm2.meta_name = 'person'
;

edited Feb 24 '12 at 21:52

answered Feb 24 '12 at 21:02

RolandoMySQLDBA

43,883
16
91
132

JOIN 4 Tables with meta_table

2 Answers2