CODEWITHSUNDEEP
mysql
Paul S.
Paul S.

Reputation: 1537

Properly joining a key-value table to a master table

We have a primary table and a secondary table to hold a bunch of key-value pairs that are associated with the main table. The reasoning behind this is that as our data grows, so will the number of different types of 'keys'. Current structure:

Main Table:
id|name

Secondary Table:
id|key|value

I would like to select all records from the main table, and select a few keys from the secondary table. The way I am doing it now is:

SELECT main_table.id, main_table.name, s1.value, s2.value
FROM main_table
LEFT JOIN secondary_table s1 ON s1.id = main_table.id AND s1.key = 'cats'
LEFT JOIN secondary_table s2 ON s2.id = main_table.id AND s2.key = 'dogs'

I have to use LEFT JOIN as some records will not have a corresponding record in the second table, but I need the record to return anyway. This seems inefficient to me and quite slow. Perhaps I am going about it the wrong way? How can I improve the structure/query?

Upvotes: 2

Views: 1845

Answers (2)

eggyal
eggyal

Reputation: 125985

You can use GROUP BY:

SELECT   main_table.id, main_table.name,
         MAX(IF(s.key='cats',s.value,NULL)),
         MAX(IF(s.key='dogs',s.value,NULL))
FROM     main_table LEFT JOIN secondary_table s USING (id)
GROUP BY id

Upvotes: 2

Mike Brant
Mike Brant

Reputation: 71422

I would say you should abandon your approach of trying to return all you query data in one record and just query the relational database the way it is meant to be queried. So do something like this":

SELECT m.id, m.name, s.key, s.value
FROM main_table AS m
INNER JOIN secondary_table AS s ON m.id = s.id
WHERE key IN ('cats', 'dogs', 'tigers', 'elephants')

I am imaging in this case you have a primary key on m.id and a compound primary key on s.id, s.key. You would also need a separate index on s.key to optimize this query.

This would return multiple rows in the result set.

Upvotes: 3

Related Questions