CODEWITHSUNDEEP
sqlmysql
Latox
Latox

Reputation: 4695

SELECT Data from multiple tables?

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.

Upvotes: 5

Views: 15744

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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

Upvotes: 4

Weltkind
Weltkind

Reputation: 687

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'

Upvotes: 2

awm
awm

Reputation: 6570

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

Upvotes: 1

Related Questions