CODEWITHSUNDEEP
sql
Marek Barta
Marek Barta

Reputation: 389

SQL rows from multiple tables

I have a table which represents a list

id  |  item_type  |  item_id
----------------------------
1      'article'     24
2      'post'        9
3      'article'     120
4      'image'       9

item_type basically represent from which table the row is suppose to be and id is the id from that table. Which means that for example second and fourth line are not the same.

I would like to get lets say item_title and more columns which are going to be created differently based on each table.
Lets say if item_type = 'article', then item_title = title from that table.
But if item_type is 'image' item_title = author_first_name + author_second_name + image_id
etc...

I was googling it or how to approach it. Particularly I thought I could be solved by sql if statement with subqueries but so far was unable to make a real progress. So I would like to ask for help or point out the right direction.

Upvotes: 1

Views: 43

Answers (2)

MatBailie
MatBailie

Reputation: 86735

Although the accepted answer is accurate and relatively concise, it is also laborious. (The article table is being joined to post and image rows, even though the results are then discarded.)

To improve that you could start with...

select
    i.*,
    case 
      when i.item_type = 'article' then a.title
      when i.item_type = 'post'    then p.post_title
      when i.item_type = 'image'   then g.author_first_name + g.author_second_name + g.image_id
    end as item_title
  from item i
  left join article a on i.item_id = a.id AND i.item_type = 'article'
  left join post    p on i.item_id = p.id AND i.item_type = 'post'
  left join image   g on i.item_id = g.id AND i.item_type = 'image'

Many optimisers don't actually like that though. If you really want to get the most out of your database, I would put an index on item(item_type) and then use explicit logic for each type...

   SELECT i.*, a.title
     FROM item i
LEFT JOIN article a ON i.item_id = a.id
    WHERE i.item_type = 'acticle'

UNION ALL

   SELECT i.*, p.post_title
     FROM item i
LEFT JOIN post p ON i.item_id = p.id
    WHERE i.item_type = 'post'

UNION ALL

   SELECT i.*, g.author_first_name + g.author_second_name + g.image_id
     FROM item i
LEFT JOIN image g ON i.item_id = g.id
    WHERE i.item_type = 'image'

More long winded, but much more explicit and optimiser friendly.

Upvotes: 3

The Impaler
The Impaler

Reputation: 48810

You can perform OUTER JOINs to all those tables and compose a title using the appropriate columns, as in:

select
    i.*,
    case 
      when i.item_type = 'article' then a.title
      when i.item_type = 'post'    then p.post_title
      when i.item_type = 'image'   then
        g.author_first_name + g.author_second_name + g.image_id
    end as item_title
  from item i
  left join article a on i.item_id = a.id
  left join post    p on i.item_id = p.id
  left join image   g on i.item_id = g.id

Upvotes: 0

Related Questions