SQL Suggestion

Discussion in 'OT Technology' started by CyberBullets, Jul 31, 2003.

  1. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    ok i have 2 tables, entry1 and entry2. each table has a datetime. what i wanna do is go though the tables and pull out the last 5 posts between the 2 of them.

    any suggestions?

    here is an example of what i have, and want.
    Code:
    entry 1 table
    e1id        date
    1            2003-01-01 00:00:01
    2            2003-02-01 00:10:01
    
    
    entry 2 table
    e2id        date
    1            2003-01-01 00:00:02
    2            2003-04-01 00:12:11
    
    and it look like

    Code:
    1            2003-01-01 00:00:01
    1            2003-01-01 00:00:02
    2            2003-02-01 00:10:01
    2            2003-04-01 00:12:11
    

    i hope it makes sence

    TIA

    oh i prefer sql. my lang is php.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I'd union query the tables first, sorted by date. Then make a table with this query. Then add an autonumber field and then select from that table the autonumber field <=5.

    You could also step through each table but the above way is a little cleaner (although perhaps not as efficient).
     
  3. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    Can I ask why you have two tables if they both contain similar information?
     
  4. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    cause one is my blog table, other is my buddys blog table. i wanna show the last 5 posts between us.


    5Gen, ill try a union and see what happens tkx. (i didnt pay much attention in my Database and Database Managment system, it was friday moring 8:30. hardly went, slept in.
     
  5. CompiledMonkey

    CompiledMonkey New Member

    Joined:
    Oct 26, 2001
    Messages:
    8,528
    Likes Received:
    0
    Location:
    Richmond, VA
    Ah, makes sense now. :big grin:
     
  6. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Could this work:

    SELECT e1id, e2id, table1.date AS date1, table2.date AS date2 FROM table1, table2 ORDER BY date DESC LIMIT 0, 5

    Thats one way. Although it looks correct here, MySQL may complain about something - especially since you're getting into possibily duplicate field names. Creating a temp table like how 5Gen suggested would definitely work.

    Another way that might work is taking the two tables and creating a merge table. I'm doing this at work and MySQL can create it very quickly. It would combine the two tables into one, yet leave the original two tables alone (and I believe as you update the two individual tables, it updates this merge table - but you can't update the merge table directly - I think). I just use merge tables to combine like tables when needed and then delete the merge table when I'm done. In your case, you may not need to do that - just create once and you're good to go. You can create the merge table on the fly via PHP/SQL code or create the merge from within MySQL (or phpmyadmin). If you're willing to learn something new, you might give it a whirl. If you get stuck or need some PHP code, let me know...
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Didn't know about the limit command - sounds like the merge table is very similar to a union query?
     
  8. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    i got the union all working under mysql 4.0.x but gigabean.com (my host) has 3.2.x mysql. i e-mailed and asked for them to update it.

    all my query was:

    Code:
     SELECT * FROM entry UNION ALL SELECT * FROM mike_entry ORDER BY date DESC LIMIT 5;
    
    i will try the AS tomorrow Astro, i have to get to bed i work tomorrow. ill also try the merge table, sounds like a cool idea. wouldnt mind reading into it some more.
     

Share This Page