Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT Data from multiple tables?

Tags:

sql

mysql

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.

like image 417
Latox Avatar asked Feb 20 '11 07:02

Latox


2 Answers

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
like image 105
RichardTheKiwi Avatar answered Oct 01 '22 00:10

RichardTheKiwi


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'
like image 30
Weltkind Avatar answered Sep 30 '22 23:09

Weltkind