CodeIgniter Forums

Full Version: query from tables
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]newbie boy[/eluser]
all tables have a date field that i must display the results through a descending order…

the query should be like the results came from a single table’s date field…

somehow i need to combine all the date field from all the tables then make my query…

this, really i just don’t know how…

thanks...

El Forum

[eluser]obiron2[/eluser]
You need to UNION all of the records together (you have already posted about this..)

then outside the UNION query you need to select from the union'ed dataset and order by date desc.

Code:
SELECT 'table1' as 'table_name', ID as 'ID', Date as 'Date' from table1
UNION
SELECT 'table2' as 'table_name', ID as 'ID', Date as 'Date' from table2

Note the addition of the fixed values table1 and table2 to ensure record uniqueness (assuming ID is unique within the table. then put the outer select around this. You will need to alias the union query so that you can do so.

Code:
SELECT myUnion.Table_name,myUnion.ID,myUnion.Date from
(
    SELECT 'table1' as 'table_name', ID as 'ID', Date as 'Date' from table1
    UNION
    SELECT 'table2' as 'table_name', ID as 'ID', Date as 'Date' from table2
) myUnion
Order by myUnion.Date DESC

I think you may be trying to run before you can walk. Read some books or tutorials on SQL

Please note, the above code has not been tested.

Obiron