CODEWITHSUNDEEP
mysqlsqldatabase
Jesús Cerezuela Zaplana
Jesús Cerezuela Zaplana

Reputation: 129

Improve this query to fetch data from different tables

This is my scenario. I have a table of events with a field type, with values 1 = food, 2 = recipe. Simplifying, my events table have this structure:

 id | entity_id | user_id | type | timestamp | field1 | ... field n

Field "entity_id" refers to a unique autoincremental value from "Entities" table. Food and recipe table structure is very similar, with entity_id and user_id fields.

What I want is to get all the common data from the table events of last 10 registers, and fetch some needed fields of corresponding table based on type value of table events. By now I have achieved some quite similar, but not exactly what I want, with this query:

SELECT a.*, b.name, b.field1, b.field2, c.name, c.field1, c.field2
FROM events a
LEFT JOIN foods b ON b.entity_id = a.entity_id
LEFT JOIN recipes c ON c.entity_id = a.entity_id 
ORDER BY timestamp DESC LIMIT 10

This allways returns all fields for all tables, with NULL values when the field is not of the type of this specific register. So I want to get all fields of events table, and name, field1, field2 of the corresponding table.

EDIT:

Here is the sqlfiddle sqlfiddle.com/#!2/18d45/9 I'd like the query returned different field values based on the table. In the example table recipes has description field while foods not. Is it possible?

Please helpe me with this!

Upvotes: 0

Views: 62

Answers (1)

dnoeth
dnoeth

Reputation: 60472

You might use a COALESCE to get the first not NULL column:

SELECT a.*, 
   COALESCE(b.name, c.name), 
   COALESCE(b.field1, c.field1),
   COALESCE(b.field2, c.field2)
FROM events a
...

Upvotes: 1

Related Questions