JOIN conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

JOIN conversion

Author

Topic

holland07
Starting Member

7 Posts

Posted - 2004-01-26 : 17:43:41

Can anyone convert the following SQL statement from the JOINs it is
using, to where the tablenames are in the FROM and the joining of the
tables is in the WHERE clause?

SELECT ENROLLMS.GUAR_NO, EOBPAY.E_CLAIM, EOBPAY.E_CLAIMSUB, ENROLLMS.K_EM, EOBPAY.MBR_STATUS,
PRECLAIM.C_DATEREC, EOBPAY.E_PAYNAME, PROCLAIM.C_PROCLAIM, PROVIDER.MP_NAME, PROVIDER.MP_FEDID,
EOBPAY.E_CHKNO, EOBPAY.E_DATE, EOBPAY.E_PAYDATE, PRECLAIM.C_CLMPRO, PROCLAIM.C_FDATE,
PROCLAIM.C_TDATE, ADJCLAIM.C_EOBDESC, EOBHIST.E_TOTCHG, ADJCLAIM.C_MAXOVR, ADJCLAIM.C_DENYAMT,
ADJCLAIM.C_RANDCOVR, ADJCLAIM.C_PPODISC, ADJCLAIM.C_ALWCHG, ADJCLAIM.C_DEDAMT, ADJCLAIM.C_OOPAMT,
PROCLAIM.C_ALWBYOTH, EOBHIST.E_PAYAMT, PRECLAIM.C_CLAIMTOT, EOBPAY.E_PAYTOT, PRECLAIM.C_EMPLOYEE,
PRECLAIM.C_PATIENT, GroupMain.GR_CODE, ADJCLAIM.C_MEMOCOD1, TPDENY1.DY_MEMOTYP AS DY_MEMOTYP1,
ADJCLAIM.C_MEMOCOD2, TPDENY2.DY_MEMOTYP AS DY_MEMOTYP2, ADJCLAIM.C_MEMOCOD3,
TPDENY3.DY_MEMOTYP AS DY_MEMOTYP3, ADJCLAIM.C_MEMOCOD4, TPDENY4.DY_MEMOTYP AS DY_MEMOTYP4, GROUP1.GR_MAIL1,
GROUP1.GR_MAIL2, GROUP1.GR_MCITY, GROUP1.GR_MSTATE, GROUP1.GR_MZIP, ENROLLMS.SUB_NO,
PROVIDER.PROV_NO, PROCLAIM.SUB_NO AS EMP_SUB_NO, GroupMain.GR_NAME
FROM TPDENY TPDENY1 RIGHT OUTER JOIN
TPDENY TPDENY2 RIGHT OUTER JOIN
TPDENY TPDENY4 RIGHT OUTER JOIN
EOBHIST INNER JOIN
PROCLAIM ON EOBHIST.DETAIL_NO = PROCLAIM.DETAIL_NO INNER JOIN
PROVIDER ON PROCLAIM.PROV_NO = PROVIDER.PROV_NO INNER JOIN
PRECLAIM ON PROCLAIM.CLAIM_NO = PRECLAIM.CLAIM_NO INNER JOIN
EOBPAY ON EOBHIST.E_PAYNO = EOBPAY.E_PAYNO INNER JOIN
ENROLLMS ON EOBPAY.E_CLAIMANT = ENROLLMS.SUB_NO INNER JOIN

ADJCLAIM ON EOBHIST.DETAIL_NO = ADJCLAIM.DETAIL_NO INNER JOIN
GroupMain ON ENROLLMS.GROUP_NO = GroupMain.GROUP_NO INNER JOIN
GROUP1 ON GroupMain.GROUP_NO = GROUP1.GROUP_NO ON
TPDENY4.DY_NUMBER = ADJCLAIM.C_MEMOCOD4 LEFT OUTER JOIN
TPDENY TPDENY3 ON ADJCLAIM.C_MEMOCOD3 = TPDENY3.DY_NUMBER ON TPDENY2.DY_NUMBER = ADJCLAIM.C_MEMOCOD2 ON
TPDENY1.DY_NUMBER = ADJCLAIM.C_MEMOCOD1

ORDER BY ENROLLMS.SUB_NO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 17:46:27

Why would you want to do that? The JOIN syntax is SQL-92 syntax.

Tara

holland07
Starting Member

7 Posts

Posted - 2004-01-26 : 17:49:37

Because I am more familiar with the other kind of joins and I need to
make some changes but am having a heck of a time making it work
with this style of join.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-26 : 17:57:15

Here's an example:

SELECT t1.Column1, t2.ColumnA
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Column1 = t2.Column1

is equal to

SELECT t1.Column1, t2.ColumnA
FROM Table1 t1, Table2 t2
WHERE t1.Column1 = t2.Column1

So move all of the JOINed tables into the FROM, then move all of the ON conditions to the WHERE. I can't remember what the OUTER JOIN syntax looks like though. *= maybe.

BTW, now is the time to get more familiar with the JOIN syntax.

Tara

holland07
Starting Member

7 Posts

Posted - 2004-01-26 : 18:03:57

Tara, great thanks. My issue is that I work with SQL Server and with Oracle so I try
to keep my SQL as simple as possible. I will try what you said. Thanks again.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-27 : 11:47:50

quote:
Originally posted by holland07

Tara, great thanks. My issue is that I work with SQL Server and with Oracle so I try
to keep my SQL as simple as possible. I will try what you said. Thanks again.

Oracle 8i no doubt...9i finally uses AMSI standard coding...so get used to it now....

SELECT * FROM tbl1, tbl2 WHERE tbl1.col1 = tbl2.col1(+)

That's a Oracle 8i LEFT JOIN ladies and gentlemen...painful

Brett

8-)

holland07
Starting Member

7 Posts

Posted - 2004-01-27 : 11:54:33

Brett, so Oracle 9i uses the ANSI JOIN syntax of:
<table1> <JOIN type> <table2> ON <table1.field> = <table2.field> ?

mfemenel
Professor Frink

1421 Posts

Posted - 2004-01-27 : 12:16:30

Yes it does. The older join syntax(SQL-92) is no longer recommended. Some of the join syntax +=,=+ that you have to use can return inaccurate data. It's also been hinted in some things I've read that there is a performance difference, although honestly, I haven't been able to find an actual example of that. Also, Microsoft says in future releases the join syntax +=,=+ may not be supported. Do what you have to do to get your job done of course, but I'd strongly suggest making the switch when you can.

Mike
"oh, that monkey is going to pay"

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-27 : 12:43:49

quote:
Originally posted by holland07

Brett, so Oracle 9i uses the ANSI JOIN syntax of:
<table1> <JOIN type> <table2> ON <table1.field> = <table2.field> ?

(not that this is an Oracle forum...but) What version are you on?

And here...

http://www.dbforums.com/f4/

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources