Query Multiple Tables (how to)

Question

Query Multiple Tables (how to)

kirkm 55368

SSC Enthusiast

Points: 179
More actions
April 29, 2012 at 2:15 am

#392141

Hi,
I want to build a query on Multiple Tables... there's 3 tables in total. If they all had a 'Field1'
how could I output all fields (in all 3 tables) where Field1 like *200* ?
I guess this is so basic... I can usually get what I want with one table, but two and I'm stuck. I've
tried a few things, joins etc, but don't even come close.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

kirkm 55368 (4/29/2012)
Hi,
I want to build a query on Multiple Tables... there's 3 tables in total. If they all had a 'Field1'
how could I output all fields (in all 3 tables) where Field1 like *200* ?
I guess this is so basic... I can usually get what I want with one table, but two and I'm stuck. I've
tried a few things, joins etc, but don't even come close.
Thanks.

You need a UNION query. Something like this (I added the second column so that you know which table each of the selected results comes from):

select Field1, 'Table1' Table from table1 where Field1 like '%200%' union all

select Field1, 'Table2' Table from table2 where Field1 like '%200%' union all

select Field1, 'Table3' Table from table3 where Field1 like '%200%'

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kirkm 55368 SSC Enthusiast Points: 179 More actions · Answer 2

Thanks for the response, Phil.

I see now using the actual field and table names would have been better.

I'm getting :-

Syntax error (missing operator) in query expression "tblBritburn' Table'.

And the actual query structure is

select Prefix, 'tblBritburn' Table from tblBritburn where Prefix like '%200%' union all

select Prefix, 'tblEPData2' Table from tblEPData2 where Prefix like '%200%' union all

select Prefix, 'tblSheetMusicFull' Table from tblSheetMusicFull where Prefix like '%200%'

This is new to me and I can't see the fault. The word 'Table' puzzled me so I removed it, and it worked. But I'm not seeing every field, just the Prefix field and tablename in a 2nd field called Expr1001.

Ultimately I want to specify each field to return, which will vary between the three tables. My thinking was to show everything first, then remove the unwanted fields. (Which may be the wrong approach but the aim is to learn, trial & error etc.)

Thank you for the help.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

kirkm 55368 (4/29/2012)
Thanks for the response, Phil.
I see now using the actual field and table names would have been better.
I'm getting :-
Syntax error (missing operator) in query expression "tblBritburn' Table'.
And the actual query structure is
select Prefix, 'tblBritburn' Table from tblBritburn where Prefix like '%200%' union all
select Prefix, 'tblEPData2' Table from tblEPData2 where Prefix like '%200%' union all
select Prefix, 'tblSheetMusicFull' Table from tblSheetMusicFull where Prefix like '%200%'
This is new to me and I can't see the fault. The word 'Table' puzzled me so I removed it, and it worked. But I'm not seeing every field, just the Prefix field and tablename in a 2nd field called Expr1001.
Ultimately I want to specify each field to return, which will vary between the three tables. My thinking was to show everything first, then remove the unwanted fields. (Which may be the wrong approach but the aim is to learn, trial & error etc.)
Thank you for the help.

Oops, sorry. 'Table' is a reserved word and I was trying to use it as a column name - my fault, not yours. Try this instead:

select Prefix, 'tblBritburn' Tablename from tblBritburn where Prefix like '%200%' union all

select Prefix, 'tblEPData2' from tblEPData2 where Prefix like '%200%' union all

select Prefix, 'tblSheetMusicFull' from tblSheetMusicFull where Prefix like '%200%'

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kirkm 55368 SSC Enthusiast Points: 179 More actions · Answer 4

Phil, I'm still finding the inclusion of 'TableName' to be a problem. This may be because I'm using Access, not SQL Server. But it is destined to be used in an ADO connection and needs e.g. to use "%" instead of "*". I'm converting this manually in Access to see the results and confirm it works before applying it to the ADO connection.

I think I'm getting there, and can see the chosen fields with this:-

select Prefix, Title, Artist from tblBritburn where Prefix like '*200*' union all

select Prefix, Title, Artist from tblEPData2 where Prefix like '*200*' union all

select Prefix, Title, [76th Week] from tblSheetMusicFull where Prefix like '*200*'

Can I also include the tablename in the query result ?

tblSheetMusicFull does not have a field called Artist, and I have substituted '[76th Week]' instead, which is a blank field. Is it possible to have some other text display there ? (Or must it be a field from that record).