INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL Multiple Joins + Subquery limitation

SQL Multiple Joins + Subquery limitation

(OP)
Hello,

I searched already through the web and the forum but could not find a solution for my issue. The issue is I have a query with several left and inner joins and I want to limit one of the joins with a subselect. But obviously this is not working. Here is the pseydocode of my query:

CODE

select * from FROM (((Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
and Table2.Field2='01' and Table2.Field3=1) LEFT JOIN Table3 ON Table1.Field4 = Table3.Field4
and Table3.Field5<=CURRENT DATE AND Table3.Field6>CURRENT DATE and
Table3.Field7 = (select max(Field7) from Table4 where Table4.Field4 = Table1.Field4 AND
Table4.Field5<=CURRENT DATE AND Table4.Field6>CURRENT DATE and Table4.Field7 in ('01','02','35')))
LEFT JOIN Table5 ON Table3.Field8= Table5.Field1 and Table5.Field2='PE') 

Error message im am getting is: SQL0338N An on clause associated with a join operator or in a merge statement is not valid.

But how can I limit one of my left joins with a bit more complex condition (as the subselect really seems to fail.)

Thanks in advance for any help!

RE: SQL Multiple Joins + Subquery limitation

Suggest you re-post the actuial query rather than pseudo-code and use proper indentation.

RE: SQL Multiple Joins + Subquery limitation

(OP)
Hi,

here is the query without pseudocode and I tried to format it manually a bit better.

CODE

Select Employeegroup.* 
FROM
(
 (
  ( SRS.Employeegroup AS Employeegroup INNER JOIN SRS.Employeedetail AS Employeedetail
    ON Employeegroup.ID = Employeedetail.ID 
    and Employeedetail.ENTITYTYPE='01' and Employeedetail.ENTITYTYPSEQ=1)
  LEFT JOIN SRS.Address AS Address ON Employeegroup.PRKEY = Address.PRKEY
  and Address.DEFF<=CURRENT DATE AND Address.STOPDATE>CURRENT DATE
  and Address.ADCD =
        (select max(ADCD) from SRS.Address as Address2 where Address2.PRKEY = Employeegroup.PRKEY
        AND Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE
        and Address2.ADCD in ('01','02','35')))
LEFT JOIN SRS.Lookuptable as Lookuptable_COUNTRY ON Address.ADDR_CNTRY = Lookuptable_COUNTRY.CODE
and Lookuptable_COUNTRY.GROUPCODE='PE') 

The issue is as said the subquery with the select max()... thing, as since I entered this one, I get the below mentioned error.

Reason why I need to enter it is because an Employeegroup can have more that one valid address (a mailing address, a registered address, and addtional mailing address, etc) and I want to show the valid one with the highest ADCD (internal number field in the database) value.

Thanks for any help,
Martin

RE: SQL Multiple Joins + Subquery limitation

Hi Martin,
I could be wrong as I'm finding the SQL very difficult to understand, but from your brackets and everything, it looks to me as if the very last join

LEFT JOIN SRS.Lookuptable as Lookuptable_COUNTRY ON Address.ADDR_CNTRY = Lookuptable_COUNTRY.CODE
and Lookuptable_COUNTRY.GROUPCODE='PE')

refers to the 'Address' table, but that table is not defined in that part of the query. It seems to me that 'Address' is defined one level in and so is not avaiable for reference in this outer query.

As I said, very tricky bit of SQL to get one's head around, so I might not have got this correct.

Marc

RE: SQL Multiple Joins + Subquery limitation

lots of other things wrong on that SQL

the first inner query (Qry 1)
( SRS.Employeegroup AS Employeegroup INNER JOIN SRS.Employeedetail AS Employeedetail
ON Employeegroup.ID = Employeedetail.ID
and Employeedetail.ENTITYTYPE='01' and Employeedetail.ENTITYTYPSEQ=1)
does not have any select list fields.

the second inner query (Qry 2)
(
( SRS.Employeegroup AS Employeegroup INNER JOIN SRS.Employeedetail AS Employeedetail
ON Employeegroup.ID = Employeedetail.ID
and Employeedetail.ENTITYTYPE='01' and Employeedetail.ENTITYTYPSEQ=1)
LEFT JOIN SRS.Address AS Address ON Employeegroup.PRKEY = Address.PRKEY
and Address.DEFF<=CURRENT DATE AND Address.STOPDATE>CURRENT DATE
and Address.ADCD =
(select max(ADCD) from SRS.Address as Address2 where Address2.PRKEY = Employeegroup.PRKEY
AND Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE
and Address2.ADCD in ('01','02','35')))
again does not have any select list and the join fields are wrong as the inner select (Qry 1) does not have an alias, so can not be joined at all, hence the join clauses are wrong.

and the final join again is wrong as (Qry 2) does not have a alias so again it can not be joined to SRS.Lookuptable


and finally... depending on your version of DB2 (which you did not tell us) this type of related subquery is not allowed

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: SQL Multiple Joins + Subquery limitation

(OP)
Hello,

Please be advised that I tried to simplify the SQL a bit (so showed pseudocode). Therefore I just wrote select * from. The important part was the integration of the select max().. into my multiple joins. And this is the solution (again just showing the join part):

CODE

...
LEFT JOIN SRS.Address AS Address ON Employeegroup.PRKEY = Address.PRKEY) INNER JOIN (select Address2.PRKEY, max(ADCD) as ADCD from SRS.Address as Address2 where Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE and Address2.ADCD in ('01','02','35') group by Address2.PRKEY) as ADDRESS_MAXIMUM on Address.PRKEY = ADDRESS_MAXIMUM.PRKEY and Address.ADCD = ADDRESS_MAXIMUM.ADCD and Address.DEFF<=CURRENT DATE AND Address.STOPDATE>CURRENT DATE)
... 


Thanks!

RE: SQL Multiple Joins + Subquery limitation

Well.. if you don't show us the full SQL, or at least a fully working subset of it, we can't really tell what the error is.

Some of us go through the trouble of creating tables that simulate the ones supplied (table names and field names) and then see what the issue is with the SQL - We can't do this if the SQL is incorrect, neither will we try and guess what is missing

But glad you found your solution

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: SQL Multiple Joins + Subquery limitation

(OP)
Sorry for this. I was not aware that this "truncation" of the Sql would have such an impact as I thought it would be enough to post just the part were I have the issue with. The next time I face such an issue I will post the full SQL statement.


Thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close