MySQL selecting from 2 tables at once?

[someonewhois]

I'm wondering if this is possible.

I basicly want this:

select * from tableone LIMIT 10 ORDER BY date
select * from tabletwo LIMIT 10 ORDER BY date

But I only want it to show 10 ALL together.

I could go LIMIT 5 for each, and do one after the other, but I would prefer to make it so that if the last 10 things where put into tableone, then it would show 10 from tableone, and none from tabletwo, but if out of the last 10 out of both, 8 were tableone, 2 would show from tabletwo etc.

Any ideas?

Thanks,
~someonewhois

[randem]

I'm not aware of any SQL syntax to accomplish this.

However, you could just pipe the top 10 from each table into an array and do a sort on the array. No?

[CyberFuture]

SQL syntax for this is called "UNION". The bad news is MySQL doesn't support this. The good news is it will in the new version. randem's suggestion is a good alternative for what you're doing.

[Aes]

Why would a simple join query not work?

<?php
mysql_query("SELECT * FROM tableone, tabletwo LIMIT 10 ORDER BY date DESC");
?>

-Colin

[CyberFuture]

Try it Aes... With more than one table you will need to show how they are joined. If you don't what you get is every possible combination of the two tables. For example if table1 has 200 rows and table2 has 500 rows, the number of rows returned is 100,000!!! YIKES...

[samsm]

You beat me Cyberfuture! I wasted too much time checking it out. Redundant post follows:
With a join, the results would be filed under different column names, plus each row would be returned as many times as there are rows in the joined column:

 mysql> select * from article_category, subcategory;
+------------+-------------+--------+----------+---------------+
| article_ID | category_ID | sub_ID | super_ID | main_category |
+------------+-------------+--------+----------+---------------+
|          4 |          11 |      8 |        9 |          NULL |
|          2 |           8 |      8 |        9 |          NULL |
|          4 |          11 |     11 |       10 |          NULL |
|          2 |           8 |     11 |       10 |          NULL |
+------------+-------------+--------+----------+---------------+
4 rows in set (0.01 sec)

mysql> select * from article_category;             
+------------+-------------+
| article_ID | category_ID |
+------------+-------------+
|          4 |          11 |
|          2 |           8 |
+------------+-------------+
2 rows in set (0.00 sec)

mysql> select * from subcategory;
+--------+----------+---------------+
| sub_ID | super_ID | main_category |
+--------+----------+---------------+
|      8 |        9 |          NULL |
|     11 |       10 |          NULL |
+--------+----------+---------------+
2 rows in set (0.00 sec)

[Aes]

I see your point regarding the column names, however, with a LIMIT clause it's not going to output 100,000 rows of data nor anything near that!

-Colin

[CyberFuture]

True, but fields with in the LIMIT are useless/meaningless.

[Aes]

Originally posted by CyberFuture
True, but fields with in the LIMIT are useless/meaningless.

Well ... so .... FINE!!! I don't care anyway.

-Colin

[someonewhois]

So does it work or not?

It better not kill my server..

Thanks for hleping,
~someonewhois

[someonewhois]

Error 1052: Column: 'username' in where clause is ambiguous

I added a WHERE cluas, and it didn't work.

Any ideas?

Thanks,
~someonewhois

[cyngon]

The error you recieved is being caused because you are SELECTing from two tables which have a username column, and it doesn't know which username column you are refering to in your WHERE clause.

Try using table_name.username instead of just username.

[CyberFuture]

It's not going to work.

The best thing to do right now is to is to follow randem's suggestion. When MySQL 4 comes out you can do it using UNION, but who knows when that's going to happen.

[someonewhois]

Ok, I'll try tablename.username!

Even if it won't work..

Can I do "table1.username, table2.username" for "table1, table2"?

Thanks,
~someonewhois

[oodie]

Just a thought ...
If both tables have the same column definition, you can create a temporary table and select from there. More of less it'll be like this
INSERT INTO temp_table SELECT * FROM tableone ORDER BY date DESC LIMIT 0, 10
INSERT INTO temp_table SELECT * FROM tabletwo ORDER BY date DESC LIMIT 0, 10
SELECT * FROM temp_table ORDER BY date DESC LIMIT 0, 10

[CyberFuture]

MySQL doesn't subport subqueries... but you could do it with select query and loop through it for the insert. Personally, it looks like alot of work (23 queries to the DB) for something that could be done with 2 queries and an array.