Select from a second table if first select returns nothing

[Bobby100]

Hi,



I have been trying to work out how to create a SELECT query that if it returns nothing will then perform a second SELECT query on a second table.

So Select idnumber, age from TABLE_A where idnumber = 100

if that id number is not in TABLE_A then perform..

SELECT idnumber, age from TABLE_B where idnumber = 100

I tried to write this in SQL but it didn't work. You can't start a SQL query with an IIF statement? i.e. IIF(select query is null, perfrom second query, else perform first query)?

Can you write and save the first query then reference this in the second query in design view, so that you use the first query as the criteria?

Any help would be appreciated.

Thanks.

[CJ_London]

you can't do it as two separate queries except through vba but it might work as a single query - all depends on the data

if idnumber is in either table but not both - use a union query
if idnumber could be in either or both with the same age - use a union query and then group by or distinct to eliminate duplicates
if idnumber could be in both but age could be different - you need a combination of union queries and left joins plus a decision process of which to take when different - tableA or tableB or highest or lowest

[Micron]

You can't start a SQL query with an IIF statement?

No, you cannot start a query with any function reference.

Can you write and save the first query then reference this in the second query in design view

What if it returns no records?
The OP reads like the db is not normalized; or is there another reason why you'd find similar records in more than one table (aside from being in separate db's?).
Anyway, even if "idnumber could be in either or both with the same age - use a union query" I suspect a union query alone would suffice - just don't use the "All" keyword.
I think if you have this

tblA			tblB
ID	Age		ID	Age
100	10		100	40
100	20
100	30
100	40

that this
SELECT ID, Age from tblA WHERE ID = 100
UNION
SELECT ID, Age from tblB WHERE ID = 100
will give you this

ID	Age
100	10
100	20
100	30
100	40

without getting two records for 100 / 40. If you use the All keyword, you will get 2 records for 100 / 40.

[CJ_London]

good point micron - had forgotten that was the default position. A while since I've used a union query!

[Bobby100]

Thanks guys,

Some very helpful information.

I will try out the union query as suggested.

IN answer to your question.. yes the database is not normalized.

That would be the optimal solution.. to normalize the db, but this is outside of my remit at present.

Thanks.