SELECT Data from multiple tables?

Question

I have 3 tables, with 3 fields all the same. I basically want to select information from each table

For example:

userid = 1

I want to select data from all 3 tables, where userid = 1

I am currently using:

   SELECT r.*, 
          p.*, 
          l.*
     FROM random r
LEFT JOIN pandom p ON r.userid = p.userid
LEFT JOIN landom l ON l.userid = r.userid
    WHERE r.userid = '1'
    LIMIT 0, 30

But it doesn't seem to work.

Answer 1

with 3 fields all the same

So you mean that you want the same 3 fields from all 3 tables?

   SELECT r.col1, r.col2, r.col3
     FROM random r
    WHERE r.userid = '1'
    LIMIT 0, 30
UNION ALL 
   SELECT p.pcol1, p.pcol_2, p.p3
     FROM pandom p
    WHERE p.userid = '1'
    LIMIT 0, 30
UNION ALL 
   SELECT l.l1, l.l2, l.l3
     FROM landom l
    WHERE l.userid = '1'
    LIMIT 0, 30

The fields don't have to be named the same, but the same types need to line up in position 1, 2 and 3.

The way the limits work is:

• it will attempt to get 30 from random.
• If it has 30 already, it won't even look at the other 2 tables
• if it has less than 30 from random, it will try to fill up to 30 from pandom and only finally landom

Answer 2

SELECT t1.*, t2.*, t3.* 
   FROM `random` as t1, `pandom` as t2, `landom` as t3 
WHERE t1.`userid`='1' AND t2.`userid`='1' AND t3.`userid`='1'

Answer 3

SELECT * FROM `random`
         JOIN `pandom` USING (`userid`)
         JOIN `landom` USING (`userid`)
WHERE `userid`='1'