data from different tables

[Wolfgang Flieger]

Hello everybody,
please help!
I have 3 tables and I retrieved the data in this way:

select field1 from tbl1 where id=5
union select field2 as field1 from tbl2 where id=3
union select field3 as field 1 from tbl3 where id=7
So I get the data in three rows. But I would like to get them in a single
row: field1, field2 and field3. Is this possible?
Thanks!
Wolfgang

 

[John W. Vinson]

Hello everybody,
please help!
I have 3 tables and I retrieved the data in this way:

select field1 from tbl1 where id=5
union select field2 as field1 from tbl2 where id=3
union select field3 as field 1 from tbl3 where id=7
So I get the data in three rows. But I would like to get them in a single
row: field1, field2 and field3. Is this possible?
Thanks!
Wolfgang

Not directly with a UNION, but with a join of some sort. A UNION is designed
to assemble data "top to bottom", not "side by side". How (if at all) are your
tables related? Where do your ID's come from, and what is their significance?
Do you want to compile just one row, or many rows with different choices of
IDs? What's the context?

 

[Wolfgang Flieger]

Hello John,

Not directly with a UNION, but with a join of some sort. A UNION is
designed
to assemble data "top to bottom", not "side by side". How (if at all) are
your
tables related? Where do your ID's come from, and what is their
significance?
Do you want to compile just one row, or many rows with different choices
of
IDs? What's the context?
--

Yes, I need the data "side by side". Well this is the context:
There are three tables, each with 78 texts corresponding to 78 tarot cards.
Each tables has id-numbers from 1-78, the tables are not related. Till now I
have used three recordsets, for every table a separate sql query but now for
the print function (SQL reporting services) it would be easier to have all
three texts in one query. The user gets three random numbers 1-78 and so are
the id-numbers determined.
I could naturally insert all data in the same table, then I would have one
table with three fields. But so remains the same question: how to get them
all "side by side", three fields with different ids.
Thank you very much!
Wolfgang

 

[Bob Barrows]

Hello everybody,
please help!
I have 3 tables and I retrieved the data in this way:

select field1 from tbl1 where id=5
union select field2 as field1 from tbl2 where id=3
union select field3 as field 1 from tbl3 where id=7
So I get the data in three rows. But I would like to get them in a
single row: field1, field2 and field3. Is this possible?
Thanks!
Wolfgang

Provided that each subquery can never retrieve more than a single record,
you will need a cartesian join of three derived tables:

select t1.field1,t2.field2,t3.field3 from
(select field1 from tbl1 where id=5) as t1,
(select field2 from tbl2 where id=3) as t2,
(select field3 from tbl3 where id=7) as t3

 

[Bob Barrows]

Hello everybody,
please help!
I have 3 tables and I retrieved the data in this way:

select field1 from tbl1 where id=5
union select field2 as field1 from tbl2 where id=3
union select field3 as field 1 from tbl3 where id=7
So I get the data in three rows. But I would like to get them in a
single row: field1, field2 and field3. Is this possible?
Thanks!
Wolfgang

For a little simpler query, you can join the tables themselves rather than
use derived tables:

select t1.field1,t2.field2,t3.field3 from
tbl1 as t1,tbl2 as t2,tbl3 as t3
where t1.id=5 and t2.id=3 and t3.id=7

 

[Wolfgang Flieger]

Thank you Bob,
your tipps are very helpfull!
Cheers
Wolfgang

 

[John W. Vinson]

Yes, I need the data "side by side". Well this is the context:
There are three tables, each with 78 texts corresponding to 78 tarot cards.
Each tables has id-numbers from 1-78, the tables are not related. Till now I
have used three recordsets, for every table a separate sql query but now for
the print function (SQL reporting services) it would be easier to have all
three texts in one query. The user gets three random numbers 1-78 and so are
the id-numbers determined.
I could naturally insert all data in the same table, then I would have one
table with three fields. But so remains the same question: how to get them
all "side by side", three fields with different ids.

Bob's Cartesian join query is indeed the way to do this. You can use a
parameter query; for instance if you have a Tarot table with fields for
CardID, Suit and Rank with values like

1; "Trumps"; "Magician"
23; "Wands"; "Ace"
78; "Swords"; "King"

you could use a form with combo boxes to select the desired card, and use the
names of those combos as criteria in your query.

(Yes, I've read the Tarot at times).