I'm trying to SELECT from two tables and ORDER BY date (a column they both have). One table (tableA) has a column called "A" and the other table (tableB) has a column called "B", I use array_key_exists() to differentiate between the two (If "A" key exists, I run the array through FunctionA(), if "B" key exists, I run the array through FunctionB()). I only need the 20 latest (date wise) entries. I need the SQL Query to accomplish this.
I already know a reply will be "if they're similarly structured, then you should just use a single table", but I don't want to do that because tableA is drastically different from tableB (a lot more columns in tableA), and using a single table to store the data would result in a LOT of empty columns for entries formatted for tableB, not to mention it'd be a very ugly looking table format due to tableB not needing the majority of tableA's columns).
I just want to display data from both tables in an ordered (by date) fashion, and in one single stream.
I need to SELECT WHERE tableA.poster_id = $id and tableB.receiver_id = $id by the way.
SOLUTION:
I'm updating this just in case anyone else with the same dilemma comes along. After implementing the SQL query that @Erik A. Brandstadmoen had graciously given me, this is basically what my code ended up as:
$MySQL->SQL("SELECT * FROM
(SELECT A.id AS id, A.date AS date, 'tableA' AS source
FROM tableA A WHERE A.poster_id = $id
UNION
SELECT B.id AS id, B.date AS date, 'tableB' AS source
FROM tableB B WHERE B.receiver_id = $id) AS T
ORDER BY T.date DESC LIMIT 0, 20");
$GetStream = array();
$i = 0;
while ($row = mysql_fetch_array($MySQL->Result))
{
$GetStream[$i]['id'] = $row['id'];
$GetStream[$i]['date']=$row['date'];
$GetStream[$i]['source'] = $row['source'];
$i++;
}
*** later on down the code ***
$i = 0;
while ($i<count($GetStream))
{
if ($GetStream[$i]['source'] == "tableA")
{
FunctionA($GetStream[$i]);
}
else
{
FunctionB($GetStream[$i]);
}
$i++;
}