Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Multiple MySQL Queries

    Hello,

    I'm having an issue, and I don't even know where to begin. I have approximately 4-5 tables with varying info, and what I'm trying to do is query all those tables, then sort the data by the timestamps in each table.

    I don't know if JOIN can handle 5 tables, and even then JOIN would need to compare A = B. The tables have nothing in common, and the timestamps that I'm trying to organize are all unique.

    Each table stores unique info, each one has different column names and data stored inside it, I just want to query all that info from all the tables and somehow sort it by the timestamps stored in column `time`. Then I’ll go ahead and somehow echo all that information.

    I don’t even know if MySQL can do this, or if I’d need to do it at a PHP level... Anyone have any ideas where to start?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Use a UNION. The only restriction is each query must return the same number, and datatype, of columns. So if the first query returns 4 columns-- a varchar, an int, a datetime, and a char, then every other query must also return 4 columns-- a varchar, an int, a datetime, and a char.

    Code:
    SELECT datetime_column as thedate, text_column FROM table1
    UNION
    SELECT datetime_column as thedate, text_column FROM table2
    ORDER BY thedate

  • #3
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfortunately the 5 tables storing the information are all unique, each has different columns, different keys, different number of columns, different everything. They all store unique data in it's own way.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,631 Times in 4,594 Posts
    Then your requirement doesn't make sense.

    How could you *USE* the results of a query if you wouldn't know what fields are in each record that is returned????

    Fumigator was *NOT* saying that the records IN THE TABLES had to look the same...only that the *DATA* that you SELECT from each one must be the same (or at least "compatible").

    The column NAMES make no difference at all. Only the number of columns and the types.

    Let's take a simple 2-table case:
    Code:
    table1:
        id : int
        name : varchar(100)
        email : varchar(200)
        salary: double
        zipcode : int
        lastcontacted : datetime
    
    table2:
        whenAdded : datetime
        userID : varchar(20)
        name : varchar(30)
    It may not make sense, but you *COULD* query both those tables thus:
    Code:
    SELECT 1 AS tablenum, lastcontacted AS theDate, CAST(id AS varchar(20)) AS theID, name FROM table1
    UNION
    SELECT 2, whenadded, userID, name FROM table2
    ORDER BY theDate, name
    And then the record you retrieve (in whatever language you use) will have fields named and typed:
    Code:
    tablenum : INT (so that you will know which table the data came from)
    theDate : datetime
    theID : varchar(20)
    name : varchar(100)
    If this is not adequate for your purposes, then you will have no choice but to do 5 separate queries.

  • #5
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My apologies for the slow reply, last few days has been time consuming.

    I was reviewing the replies and trying to deduce precisely what needs to be done, but so far anything I try either results in "The used SELECT statements have a different number of columns" or some other random error in my syntax.

    Would it be possible for someone to help me out with the query for my tables? I'm hoping with more familiar columns/tables in a query I can work with, I should be able to grasp the concept and hopefully recreate it with other tables...

    Code:
    events:
    
    id: tinyint(3) name: varchar(50)
    news:
    user: smallint(3) time: int(10) text: text

    I've used these two tables in my experimental unions but so far no success. Could anyone help me write the query for it? With the query hopefully I can take it apart and understand how it works so I can add more tables to it...

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,631 Times in 4,594 Posts
    Code:
    events:
        id: tinyint(3) name: varchar(50)
    
    news:
        user: smallint(3) time: int(10) text: text
    Well, not sure it really makes sense to put data that disparate into a single UNION, but...
    Code:
    SELECT 'event' AS rectype, id, name, NULL AS theTime FROM events
    UNION
    SELECT 'news',user,text,`time` FROM news
    ORDER BY rectype, id
    You might need a couple of explicit casts in there. Not sure.

    Note how I had to supply a dummy value for time in the first SELECT. I chose to use NULL, but since the field is INT in the other table, you could maybe just use -1 instead.

    The resulting record should have these fields and types, if I read the docs correctly:
    Code:
    rectype : varchar(5)
    id : smallint
    name : text
    theTime : int
    Note how the tinyint gets promoted to smallint so the two fields are compatible. Same as name being promoted from varchar to text.


  •  

    Posting Permissions

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