Selecting from 2 tables

Question

I'm using this code:

SELECT *
  FROM admins, vips
  WHERE admins.Expired < CURDATE() + INTERVAL 3 DAY
    AND vips.Expired < CURDATE() + INTERVAL 3 DAY

I have 2 rows in first and 2 rows in second. But I only getting 2 rows in "vips" table. Why?

What is the relationship between the tables? You have no joining condition between them. — Michael Berkowski, Dec 22 '12 at 23:03
[Explicit joins](http://stackoverflow.com/q/2241991/) should be used in favor of [implicit joins](http://stackoverflow.com/q/44917/). — outis, Dec 22 '12 at 23:47

score 0 · Accepted Answer · edited Dec 23 '12 at 01:39

0

Try specifying the * for both tables, like this:

SELECT admins.*, vips.*
FROM admins, vips
WHERE admins.Expired < CURDATE() + INTERVAL 3 DAY
AND vips.Expired < CURDATE() + INTERVAL 3 DAY

EDIT: The answer above isn't going to help, you need a UNION SELECT:

SELECT * 
FROM (SELECT * FROM vips
    UNION
    SELECT * FROM admins) derivedTable       -- this is an alias of the subquery
WHERE Expired < CURDATE() + INTERVAL 3 DAY

edited Dec 23 '12 at 01:39

John Woo

249,283
65
481
481

answered Dec 22 '12 at 23:02

Robin Kanters

4,798
2
19
36

This will make no difference. – Michael Berkowski Dec 22 '12 at 23:03
Yeah I was re-reading the question, I misunderstood. Editing now – Robin Kanters Dec 22 '12 at 23:03
Thank You. The interesting thing is this works too: SELECT * FROM admins, vips WHERE admins.Expired < CURDATE() + INTERVAL 3 DAY – iamart Dec 22 '12 at 23:15
It probably didn't apply the WHERE to the vips. – Robin Kanters Dec 22 '12 at 23:17
your edit doesn't work too. mysql replied "Every derived table must have its own alias". – iamart Dec 22 '12 at 23:58
add an alias after the subquery eg `...(SELECT * FROM vips UNION SELECT * FROM admins) x` – John Woo Dec 23 '12 at 01:33
thank you, but it's not work for me: #1222 - The used SELECT statements have a different number of columns. I has 2 columns more in one table(another columns are the same) – iamart Dec 24 '12 at 04:16

Selecting from 2 tables

1 Answers1