Mysql. Select data from 3 different tables based on column from 1 table

Question

Here is what I want to do:

I have 3 tables: musicItems, photoItems, textItems
Each table has different columns.

Table "musicItems" 
  id
  Owner_id
  audioId
  url
  duration

Table "photoItems" 
  id
  albumId
  userId 
  photoId
  url

Table "textItems" 
  id
  msgText

And I have one more table

"footprints" 
  id
  itemType
  itemId

I need to retieve last 30 items from table "footprints" with all properties depending on itemType and itemId. How can i do that?

possible duplicate of [Mysql JOIN of four tables with two key tables](http://stackoverflow.com/questions/3824389/mysql-join-of-four-tables-with-two-key-tables) — Stephen, Nov 30 '10 at 14:55
Can you elaborate on how the columns line up (which footpritns column lines up with which musicitems column for instance?) Also, I believe you're looking to perform a LEFT/INNER join (please see http://dev.mysql.com/doc/refman/5.0/en/join.html ) — Brad Christie, Nov 30 '10 at 14:55
You need to read up on JOIN Syntax: http://dev.mysql.com/doc/refman/5.1/en/join.html — Stephen, Nov 30 '10 at 14:56
Could you please post 5 lines of sample related data from each of your table? — Pentium10, Nov 30 '10 at 14:56
Brad Christie, "footprints" table contains column "itemType" which can be: 1,2 or 3 and it points to table which contains information about item with id = "footprints.itemId" — Konstantin, Nov 30 '10 at 15:05
Does your "footprints" table, column "ItemType" represent the table the footprint is associated... ec: "MUSIC", "PHOTO", "TEXT", and the footprints column "ItemID" correspond to the ID within the associated "ItemType" table? Finally, is the Footprints ID column auto incrementing sequence ID for the table? — DRapp, Nov 30 '10 at 15:31

score 0 · Answer 1 · answered Nov 30 '10 at 14:59

I have made the assumption that the ItemType column contains the table names the ItemID refers to

SELECT  *
FROM    footprints fp
        LEFT OUTER JOIN musicItems mi ON mi.ID = fp.itemID AND fp.ItemType = 'musicItems'
        LEFT OUTER JOIN photoItems pi ON pi.ID = fp.itemID AND fp.ItemType = 'photoItems'
        LEFT OUTER JOIN textItems ti ON ti.ID = fp.itemID AND fp.ItemType = 'textItems'
LIMIT   30

Mysql. Select data from 3 different tables based on column from 1 table

1 Answers1