SitePoint Sponsor |
|
User Tag List
Results 1 to 16 of 16
-
Jun 23, 2002, 16:15 #1
- Join Date
- Jan 2002
- Location
- Canada
- Posts
- 6,364
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
MySQL selecting from 2 tables at once?
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- Nathan
-
Jun 23, 2002, 18:57 #2
- Join Date
- Jun 2002
- Location
- .chicago.il.us
- Posts
- 957
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jun 24, 2002, 08:27 #3
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jun 24, 2002, 08:31 #4
- Join Date
- Jun 2001
- Location
- Oklahoma
- Posts
- 3,392
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Why would a simple join query not work?
PHP Code:<?php
mysql_query("SELECT * FROM tableone, tabletwo LIMIT 10 ORDER BY date DESC");
?>Colin Anderson
Ambition is a poor excuse for those without
sense enough to be lazy.
-
Jun 24, 2002, 08:45 #5
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
-
Jun 24, 2002, 08:51 #6
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code: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)
Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
-
Jun 24, 2002, 09:55 #7
- Join Date
- Jun 2001
- Location
- Oklahoma
- Posts
- 3,392
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-ColinColin Anderson
Ambition is a poor excuse for those without
sense enough to be lazy.
-
Jun 24, 2002, 10:16 #8
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
True, but fields with in the LIMIT are useless/meaningless.
-
Jun 24, 2002, 10:18 #9
- Join Date
- Jun 2001
- Location
- Oklahoma
- Posts
- 3,392
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by CyberFuture
True, but fields with in the LIMIT are useless/meaningless.I don't care anyway.
-ColinColin Anderson
Ambition is a poor excuse for those without
sense enough to be lazy.
-
Jun 24, 2002, 10:42 #10
- Join Date
- Jan 2002
- Location
- Canada
- Posts
- 6,364
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So does it work or not?
It better not kill my server..
Thanks for hleping,
~someonewhois- Nathan
-
Jun 24, 2002, 10:49 #11
- Join Date
- Jan 2002
- Location
- Canada
- Posts
- 6,364
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Error 1052: Column: 'username' in where clause is ambiguous
I added a WHERE cluas, and it didn't work.
Any ideas?
Thanks,
~someonewhois- Nathan
-
Jun 24, 2002, 10:56 #12
- Join Date
- Aug 2001
- Location
- Livonia, MI, USA
- Posts
- 513
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jun 24, 2002, 10:57 #13
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jun 24, 2002, 11:44 #14
- Join Date
- Jan 2002
- Location
- Canada
- Posts
- 6,364
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ok, I'll try tablename.username!
Even if it won't work..
Can I do "table1.username, table2.username" for "table1, table2"?
Thanks,
~someonewhois- Nathan
-
Jun 25, 2002, 00:19 #15
- Join Date
- Jul 2000
- Location
- Misty Mountain
- Posts
- 125
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jun 25, 2002, 08:28 #16
- Join Date
- May 2001
- Location
- San Diego, CA
- Posts
- 434
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks