Joining three queries together.

[Anonymous]

Hi.

I've got a Q in TOS if anyone is interested.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21712821.html

Copy of q.

I have 3 queries.

Each query returns 2 columns - a code and a quantity.

The quantities each have a different name - Ordered, Invoiced, Received.

e.g.

Query1

11225BV31D     1
1756514CONTIECO     1
1756514DLPREINF     2
1756514FSTF590     2
1756514GYEARGT3     1
18514CONVANCO     4
18514FSTONE     1
1856514CONTICP     1
1856514GYRHNCT     3
1955015PIRVP6000     2
1956015CONTI     2
1956015CONTI V     3
1956015FSTTZ200     4


Query 2
B24570175/BZT     8
B26570175/BZT     4
P38565225/B2K     6
S11225/ZY     5
S20575175/ZT     5
S21575175/ZT     28
S28570195/BDE     7
S29580225/BDE1     30
S29580225/ZA     1
S31580225/BDE1     1
S38565225/ZA     27

Query 3
11-22.5             22.00
12-22.5             3.00
205-75-17.5         11.00
215-75-16           2.00
215-75-17.5         44.00
225-75-17.5         16.00
235-75-17.5         6.00
245-70-17.5         14.00
245-70-19.5         2.00
255-70-22.5         2.00
265-70-17.5         4.00
265-70-19.5         13.00
275-80-22.5         1.00
285-70-19.5         12.00
295-60-22.5         2.00
295-80-22.5         71.00
315-80-22.5         11.00
385-65-22.5         64.00
8.5-17.5            3.00

In the above examples, none of the codes tie up (at quick glance). This is OK.

What I want is all the codes in column 1.
In column 2 any values from Query1
In column 3 any values from Query2
In column 4 any values from Query3

That, possibly, is not too hard.

The final kicker (and it is an important one) is that each of these queries relate to different databases on different SQL servers.

I am using sp_droplinkedsrvlogin and sp_addlinkedsrvlogin to get one of the servers communicating to the others and this is working. I.e. I can get all the queries executing from a single server.

Ideally a single SQL statement (other than the sp's) would be needed. Only if absolutely essential should a temp table be used.

[NeoTeq]

If I understand correctly, below query is what you would want. This could be slower than you expect, so you might have to select the code from the source tables* to speed things up. Please note, this is pseudo-sql since I'm free from work and have no ms sql server instance running at home... So completely untested.

Code:

select tmp.code, q1.quantity, q2.quantity, q3.quantity
from (select query1.code union select query2.code union select query3.code) as tmp
left join query1 as q1 on tmp.code = q1.code
left join query2 as q2 on tmp.code = q2.code
left join query3 as q3 on tmp.code = q3.code

I can not really help you other than that since I have no experience working with more than one server.[/code]

* Edit: I mean in the subquery with the unions.

[Anonymous]

Exactly what I thought.

Create a distinct union of the codes from the 3 queries and use that as the left outer join on the another copy of the 3 queries.

Yep! I can do that. But oh boy it is going to be slow!

One of the queries took 4 minutes to run.

Admittedly, there is no optimization on the server yet as the db is just a conversion from a 4GL DB and no indexes. So to be expected.

It is going to be interesting to see what happens when the optimizer gets its teeth into it.

Hey! Answer the Q on TOS and get some points.

[NeoTeq]

Done  :D

[Kevin3NF]

It is going to be interesting to see what happens when the optimizer gets its teeth into it.

Indexes, indexes, indexes....

[VGR]

I thought (I'm not native English speaker) that the plural of "index" was "indices" ? ;-)

anyway, in such a situation (and even in MySql with its query optimiser & caches, temporary tables etc) I would NOT try to join the three independant queries. I would use temporary/memory tables in a stored procedure OR front-end [PHP?] script, to merge the datasets from the three original queries. This is much faster on SQL-Server and Oracle.

This is not a definitive answer thrown in the air by a fool, but the result of empiric measurements and practical experience ;-)

best of luck with your three queries...