Can I do mulitple outer joins on derived tables? 1

[mwolf00]

This query is giving me an error...

SELECT
b.c_loc
, r1.c_gsa_resp
, r2.c_gsa_resp
FROM dbo_tbldg b
LEFT JOIN (select c_loc, c_gsa_resp from dbo_tbldgresp where c_gsa_resp = '1') r1 on b.c_loc = r1.c_loc
LEFT JOIN (select c_loc, c_gsa_resp from dbo_tbldgresp where c_gsa_resp = '2') r2 on b.c_loc = r2.c_loc
WHERE b.c_region = '11'

Is the syntax ok? Can I use more than 1 derived table? I want to use a total of 9...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[cmmrfrds]

I don't know the answer on multiple derived tables, but if the field c_gsa_resp just returns 1 value per c_loc there could be 9 correlated sub queries.

 

[mwolf00]

c_gsa_resp can be up to 9 values


c_loc resp
AB123 5
AB123 2
AB123 9
AB123 1



I want to return
c_loc resp
AB123 1,2,5,9

I have achieved this creating 9 separate queries, was wondering if there is a better way....

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[cmmrfrds]

What I was thinking would produce a fixed number of columns, but if that is okay??

SELECT
b.c_loc,
(select c_gsa_resp from dbo_tbldgresp as A1 inner join b on b.c_loc = A1.c_loc where c_gsa_resp = '1') as resp1,
(select c_gsa_resp from dbo_tbldgresp as A2 inner join b on b.c_loc = A2.c_loc where c_gsa_resp = '2') as resp2,
etc....
FROM dbo_tbldg b
WHERE b.c_region = '11'

 

[mwolf00]

Thanks for trying - I get the message "At most one record can be returned by this subquery" - so there's something there that it doesn't like...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[cmmrfrds]

I forgot to add b.c_region = '11' to the subquery so it is probably getting other regions.

One thing I do when testing a subquery sometimes is to use Top 1 so that it only returns 1 record that way I know the syntax is okay at least.

 

[hindsjl]

Access 2000 -- the only version I have handy here -- wants multiple outside joins to be walled off from each other by parentheses. Example:

SELECT
b.c_loc
, r1.c_gsa_resp
, r2.c_gsa_resp
, r3.c_gsa_resp
FROM ((dbo_tbldg b
LEFT JOIN (select c_loc, c_gsa_resp from dbo_tbldgresp where c_gsa_resp = '1') r1 on b.c_loc = r1.c_loc)
LEFT JOIN (select c_loc, c_gsa_resp from dbo_tbldgresp where c_gsa_resp = '2') r2 on b.c_loc = r2.c_loc)
LEFT JOIN (select c_loc, c_gsa_resp from dbo_tbldgresp where c_gsa_resp = '3') r3 on b.c_loc = r3.c_loc
WHERE b.c_region = '11'

It may work in your case.

John

 

[mwolf00]

Thanks John - I had also tried that but to no avail. I'm guessing it's just a limitation of Access...

DOH!!! - Just tried it and it ran!!!! I must have screwed up somehow... Here's a star!!! Thanks.

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[hindsjl]

Thanks.

I think previous versions of Access were much less capable of handling subqueries, and rejected multiple outer joins completely. You get used to an irritating workaround, then MS lifts those restrictions in a later version and doesn't tell anyone!

John