...

View Full Version : Sort Multiple MySQL Queries



Luignata
03-12-2009, 10: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, 10: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.



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, 11: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-13-2009, 12:38 AM
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:


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:


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:


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, 05: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...



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, 07:03 AM
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...


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:


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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum