On Oct 29, 2006, at 2:00 PM, Børge Holen wrote:
Would you suggest to use a extra field to hold the table name as
default?
At 10/29/2006 04:16 PM, Ed Lazor wrote:
Definitely not. Review your design. If you need to know which table
data comes from, then perform table specific queries. If you need to
combine data from more than one table, code your application to
respond accordingly. You may also need to review your database
schema design in order that it best meet your needs.
That seems unreasonably harsh. What in your view is wrong with a union query that preserves an indicator of which component table a particular record comes from?
I can easily imagine a circumstance in which this could be valuable, say the union of several mailing lists that are in separate tables simply because they originate from different sources. We union them to print a single alphabetical list, for example, but we want an indicator as to the source of each record.
I can imagine modifying the OP's query to read:
$sql=" (select '1' as tableId, * from table_1 WHERE pid='0' order by id desc limit 10) union
(select '2' as tableId, * from table_2 WHERE pid='0' order by id desc limit 10) union
(select '3' as tableId, * from table_3 WHERE pid='0' order by id desc limit 10) union
(select '4' as tableId, * from table_4 WHERE pid='0' order by id desc limit 10)
order by date desc limit 10 ";
Would this be so egregious? and if so why?
You say,
If you need to combine data from more than one table,
code your application to respond accordingly.
What does this mean, exactly? Surely you're not suggesting that we code an application to somehow divine the source of a record in a union query when the query itself could simply supply that datum easily.
Curiously,
Paul