Problem with table joins

[czab]

I can handle joins when there are only 2 files involved, but I'm not sure what to do when I need to use 3 files.

Using the code below, my problem is that I'm not getting records when T1.TRF05 is blank because it doesn't have a matching record in T2. I need some data fields from T1 and T2. T3 is a security file to make sure that the user has security to view data in T1.

I've tried using a LEFT JOIN, but keep getting error messages since I'm not sure how to reference T3. Any help would be greatly appreciated.

SELECT

T1.TCONO,
T1.TDVNO,
SUM(T1.TAMGL),
T2.CNM25,
T1.TRF05

from 
  JCPPST T1,
  CSTMST T2,
  SECBI T3

WHERE

( T1.TCONO=T2.CCONO and T1.TDVNO=T2.CDVNO and T1.TRF05=T2.CCUST) 

AND

( T1.TCONO=T3.BICONO and  T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y' OR
  T3.BICONO=0 and T3.BIDVNO=0 and T3.BIA01D='Y' OR
  T3.BICONO=0 and T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y' OR
  T1.TCONO=T3.BICONO and T3.BIDVNO=0  and T3.BIA01D='Y')  

AND  

( T3.BIUSER=@Variable('BOUSER')  )

AND (T1.TCONO in (1,2,3, 7)
AND T1.TDVNO = 0
AND T1.TCSTY = 'I'
AND T1.TDTEN between @variable('1. Start Date (YYYYMMDD)') and @variable('2. End Date (YYYYMMDD)') )

GROUP BY T1.TCONO, T1.TDVNO, T1.TRF05, T2.CNM25

 

[SQLSister]

In the first place you cannot use this type of join structure to get a correct left join becasue SQL server 200 doesn;t support that, it wuill give you incorrect results. SQL server 2005 doesn't support this structure even for inner joins, so you need to stop using this structure immediately and use ANSI style joins.
example:

From table1 t1 
inner join table2 t2 on t1.field1 = t2.field1
left join table3 t3 on t3.field1 = t1.field1

Questions about posting. See faq183-874

 

[monksnake]

Assuming you have the correct relationships between the tables and using SQLSister's example above, you put anything after your WHERE clause that references 2 tables in you JOIN statement after the ON clause. Example:

From table1 T1 
inner join table2 T2 on [!]T1.TCONO=T2.CCONO and T1.TDVNO=T2.CDVNO and T1.TRF05=T2.CCUST[/!]
left join table3 T3 on [!]T1.TDVNO=T3.BIDVNO[/!]
WHERE ........rest of query

<.

 

[czab]

I tried changing my code to

From JCPPST T1 

inner join CSTMST T2 on (T1.TCONO=T2.CCONO and T1.TDVNO=T2.CDVNO and T1.TRF05=T2.CCUST)

left join SECBI T3 on (T1.TCONO=T3.BICONO and  T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y' OR
  T3.BICONO=0 and T3.BIDVNO=0 and T3.BIA01D='Y' OR
  T3.BICONO=0 and T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y' OR
  T1.TCONO=T3.BICONO and T3.BIDVNO=0  and T3.BIA01D='Y)

SELECT

T1.TCONO,
T1.TDVNO,
SUM(T1.TAMGL),
T2.CNM25,
T1.TRF05

WHERE

( T3.BIUSER=@Variable('BOUSER')  )

AND (T1.TCONO in (1,2,3, 7)
AND T1.TDVNO = 0
AND T1.TCSTY = 'I'
AND T1.TDTEN between @variable('1. Start Date (YYYYMMDD)') and @variable('2. End Date (YYYYMMDD)') )

GROUP BY T1.TCONO, T1.TDVNO, T1.TRF05, T2.CNM25

And got this error message:

Exception: DBD, [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword FROM not expected. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN.State: 42000

 

[r937]

that looks like a DB2 error

you do realize you're in the Microsoft SQL Server forum?

r937.com | rudy.ca

 

[MissyEd]

Can I be cheeky and ask if you are using Microsoft SQL Server?

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[monksnake]

<.

 

[monksnake]

Select statement always comes before joins in any SQL.

SELECT

T1.TCONO,
T1.TDVNO,
SUM(T1.TAMGL),
T2.CNM25,
T1.TRF05

From JCPPST T1 

inner join CSTMST T2 on (T1.TCONO=T2.CCONO and T1.TDVNO=T2.CDVNO and T1.TRF05=T2.CCUST)

left join SECBI T3 on (T1.TCONO=T3.BICONO and  T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y' OR
  T3.BICONO=0 and T3.BIDVNO=0 and T3.BIA01D='Y' OR
  T3.BICONO=0 and T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y' OR
  T1.TCONO=T3.BICONO and T3.BIDVNO=0  and T3.BIA01D='Y)

WHERE

( T3.BIUSER=@Variable('BOUSER')  )

AND (T1.TCONO in (1,2,3, 7)
AND T1.TDVNO = 0
AND T1.TCSTY = 'I'
AND T1.TDTEN between @variable('1. Start Date (YYYYMMDD)') and @variable('2. End Date (YYYYMMDD)') )

GROUP BY T1.TCONO, T1.TDVNO, T1.TRF05, T2.CNM25

<.

 

[czab]

OK, I'm completely embarassed. Thanks for your help.

Is there a different SQL forum I should be posting in?

 

[r937]

select T1.TCONO
     , T1.TDVNO
     , SUM(T1.TAMGL)
     , T2.CNM25
     , T1.TRF05
  from JCPPST T1
inner
  join SECBI T3
    on ( 
       T1.TCONO=T3.BICONO and  T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y'
    or T3.BICONO=0 and T3.BIDVNO=0 and T3.BIA01D='Y'
    or T3.BICONO=0 and T1.TDVNO=T3.BIDVNO  and T3.BIA01D='Y'
    or T1.TCONO=T3.BICONO and T3.BIDVNO=0  and T3.BIA01D='Y'
       )
   and ( T3.BIUSER=@Variable('BOUSER')  )      
left outer
  join CSTMST T2
    on ( 
       T1.TCONO=T2.CCONO 
   and T1.TDVNO=T2.CDVNO 
   and T1.TRF05=T2.CCUST
       ) 
 where T1.TCONO in (1,2,3, 7)
   and T1.TDVNO = 0
   and T1.TCSTY = 'I'
   and T1.TDTEN 
        between @variable('1. Start Date (YYYYMMDD)') 
            and @variable('2. End Date (YYYYMMDD)') 

group 
    by T1.TCONO
     , T1.TDVNO
     , T2.CNM25
     , T1.TRF05

r937.com | rudy.ca

 

[r937]

Is there a different SQL forum I should be posting in?"

at the top of each page there is a search box with a red [red]SEARCH[/red] button

underneath that is a dropdown list where you can specify "Find a Forum"

leave the dropdown alone (Find a FOrum is what you want), put DB2 into the search box, and let's see what you get ...



r937.com | rudy.ca

 

[czab]

Thank you all for your patience.

MissyEd, I don't know if I'm using Microsoft SQL Server per se. If there's another SQL forum to go to, I'd love to be directed there. I'm using the SQL code to pull data into a Business Objects report using Freehand SQL. I'm not quite sure what that means though. I just dabble in SQL from time to time to write reports.

Moonsnake, I did manage to figure out that the SELECT had to come before the FROM, but I took SQLSister's tip too literally. I tried coding it as you suggested, but I kept having a problem with the BOUSER variable.

r937, the way you wrote the joins is EXACTLY what I needed. I'm getting the numbers I need now.

I can't thank you all enough.

 

[SQLSister]

This might be a good place for future questions if you don't know what backend db you are using:

http://www.tek-tips.com/threadminder.cfm?pid=220

Questions about posting. See faq183-874