Sorting

[helenmarnold]

Hello,

I would like to join together the results of two different SELECT queries on two different tables and sort all of the records altogether by their entrydate

i.e.

SELECT * FROM mytable 1 WHERE status='s' ORDER BY entrydate DESC

&

SELECT * FROM mytable 2 WHERE status='s' ORDER BY entrydate DESC

I'm not sure that I can do this by joining the tabes in Mysql as it is ambiguous to ORDER BY the entrydate field - but can anyone help me do this via php?

Any help much appreciated from a fledgeling newbee!

Thanks in advance

[pete_bisby]

There is a UNION command available with certain versions of SQL databases but, unfortunately, not available with MySQL (unless I have an older version to other people).

The UNION command is placed between two SELECT queries - e.g.:

SELECT field1_a, field2_a, field3_a
FROM table_a
UNION
SELECT field1_b, field2_b, field3_b
FROM table_b;

To use this effectively, you must make sure that you have the same number of selected fields within your SELECT statement, and also be of the same datatype (i.e. field1_a must be of the same datatype of field2_a, etc.).

For MySQL, you will have to create a temporary table and place your data from separate queries within this temporary table. Then, sort your data from within the temporary table. E.g.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

Hope this helps with your problem!

[danielle]

to join them you are going to need correlating fields in the tables like a foreign key

would this work for you?

SELECT * FROM table1 left join table2 on table1.entrydate=table2.entrydate where status='s' orderby table1.entrydate

[helenmarnold]

thanks for these replys - I'll go work on it.