UNIONs and field quantity

[adrianle]

I've got a select statement using UNIONs between four tables so that I can pull records that have no relation to each other.  The problem is each table has a different number of fields, so the UNIONs will fail. 

 

Is there a different/better way for me to pull unrelated recordsets from 4 different tables without having 4 different select statements?

[Barand]

If you do a union like that then select dummy fields in the table with less data

SELECT a, b, c FROM tablea
UNION
SELECT x, y, NULL as c FROM tableb

[adrianle]

Thanks, I'd heard about this too.. so the fields themselves don't matter, just the quantity of fields matters?  This would suggest that I'd need to 'pad' all union'ed select statements to match the largest table in the group?

[Barand]

The fields in each column should be of matching types too

[adrianle]

Ouch, so that means that even with the extra false columns in the select, this will not work because there might be different column types in each table.  

[Barand]

Perhaps this is an occasion for separate queries. How different are the tables?

[Psycho]

Do you need ALL the data from the tables? Just determine what fields you need and line up the ones that are of the same type and create dummy fields for the ones that don't line up. You sure as heck shouldn't be using SELECT * anywhere