SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Wizard silver trophy someonewhois's Avatar
    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

  2. #2
    morphine for a wooden leg randem's Avatar
    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?

  3. #3
    SitePoint Evangelist CyberFuture's Avatar
    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.

  4. #4
    SitePoint Wizard Aes's Avatar
    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
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  5. #5
    SitePoint Evangelist CyberFuture's Avatar
    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...

  6. #6
    SitePoint Wizard samsm's Avatar
    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?

  7. #7
    SitePoint Wizard Aes's Avatar
    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!

    -Colin
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  8. #8
    SitePoint Evangelist CyberFuture's Avatar
    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.

  9. #9
    SitePoint Wizard Aes's Avatar
    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.
    Well ... so .... FINE!!! I don't care anyway.

    -Colin
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  10. #10
    SitePoint Wizard silver trophy someonewhois's Avatar
    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

  11. #11
    SitePoint Wizard silver trophy someonewhois's Avatar
    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

  12. #12
    SitePoint Evangelist cyngon's Avatar
    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.

  13. #13
    SitePoint Evangelist CyberFuture's Avatar
    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.

  14. #14
    SitePoint Wizard silver trophy someonewhois's Avatar
    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

  15. #15
    SitePoint Zealot oodie's Avatar
    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

  16. #16
    SitePoint Evangelist CyberFuture's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •