Sort Multiple MySQL Queries

Luignata · 03-12-2009, 09:34 PM

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?

Fumigator · 03-12-2009, 09:56 PM

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

Luignata · 03-12-2009, 10:28 PM

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.

Old Pedant · 03-12-2009, 11:38 PM

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.

Luignata · 03-17-2009, 04:49 AM

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...

Old Pedant · 03-17-2009, 06:03 AM

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.