Jstanfield - 7:05 pm on Oct 27, 2011 (gmt 0)

You can make the database server heap it together via UNION provided all queries return the same number of columns with the same datatypes -- and in your examples they do. Just add a "type" column so you can tell the difference between a newbuild, rma, and comment.


SELECT
0 `type`, `newbuild`.`id_number`, `newbuild`.`date`
FROM
`newbuild`
WHERE
`newbuild`.`serialnumber` IN(" & String.Join(",", PassedArray) & ")
AND
`newbuild`.`current` = TRUE

UNION

SELECT
1 `type`, `rma`.`id_number`, `rma`.`date`
FROM
`rma`
WHERE
`rma`.`serialnumber`
IN(" & String.Join(",", PassedArray) & ")

UNION

SELECT
2 `type`, `comments`.`id_number`, `comments`.`date`
FROM
`comments`
WHERE
`comments`.`serialnumber`
IN(" & String.Join(",", PassedArray) & ")

ORDER BY type,date

When UNIONing queries together, you can only have one ORDER BY clause, and it must be at the end. I chose to make type numeric so that I could preserve your order (first newbuilds by date, then rmas by date, then comments by date)...


You'd end up with data that looks like this:
type,id_number,date
0,12345,2011-01-31
1,12345,2011-02-03
2,12345,2011-02-03
2,12345,2011-02-10

You can examine the contents of the type column and act accordingly. (i.e. Click here to view build, click here to view comment, click here to view rma). You can make that column invisible in your grid so users don't look at it and wonder.