need help with converting old style subselect to ANSI joins?

[ColdCanuck]

Help!

I'm trying to understand the new ANSI join syntax (after many years of
coding using the old style). I am now working with an application that only
understands ANSI syntax so I am struggling.

My first (old style syntax) SQL statement below produces 60 rows:

SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1,
ADDRESS A6
WHERE A1.ADDRESSID=A6.ADDRESSID
AND A1.CONTACTID IN
(SELECT A4.CONTACTID
FROM CONTACT_LEADSOURCE A4,
LEADSOURCE A5
WHERE A4.LEADSOURCEID = A5.LEADSOURCEID
AND A5.DESCRIPTION = 'some_description' )
AND A1.CONTACTID IN
(SELECT A2.CONTACTID
FROM TICKET A2,
ENROLLHX A3,
EVENT A7
WHERE A3.STATUS IN ('R', 'Confirmed')
AND A2.TICKETID = A3.EVXEVTICKETID
AND A3.EVENTID = A7.EVENTID
AND A7.CODE IN
('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))
ORDER BY A1.LASTNAME ASC

I am trying to convert this to the newer ANSI sytax. My second SQL statement
below produces 67 rows (duplicates):

SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1
JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)
JOIN
( SELECT C.CONTACTID
FROM CONTACT C
JOIN CONTACT_LEADSOURCE A4 ON (C.CONTACTID
= A4.CONTACTID)
JOIN LEADSOURCE A5 ON (A4.LEADSOURCEID =
A5.LEADSOURCEID
AND A5.DESCRIPTION =
'some_description' )) AS C1 ON C1.CONTACTID = A1.CONTACTID
JOIN
(SELECT C2.CONTACTID
FROM CONTACT C2
JOIN TICKET A2 ON (C2.CONTACTID =
A2.CONTACTID)
JOIN ENROLLHX A3 ON (A2.TICKETID =
A3.TICKETID AND A3.STATUS in ('R', 'Confirmed'))
JOIN EVENT A7 ON (A3.EVENTID = A7.EVENTID

AND A7.CODE IN ('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')))
AS C3 ON C3.CONTACTID = A1.CONTACTID
Can anyone shed some light on what I am missing?

cheers,
Norm

[ColdCanuck]

"ColdCanuck" <co**@canuck.ca> wrote in message
news:2lZGc.2076$iw3.1593@clgrps13...

Help!

I did manage to figure it out. The key was using DISTINCT.

FWIW, my SQL ended up being :

SELECT DISTINCT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A2.CITY,
A2.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1
JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID)
LEFT JOIN CONTACT_LEADSOURCE A3 ON
(A1.CONTACTID=A3.CONTACTID)
JOIN TICKET A5 ON (A3.CONTACTID=A5.CONTACTID)
JOIN ENROLLHX A6 ON (A5.TICKETID=A6.TICKETID)
JOIN EVENT A7 ON (A6.EVENTID=A7.EVENTID)
LEFT JOIN LEADSOURCE A4 ON
(A3.LEADSOURCEID=A4.LEADSOURCEID)
WHERE A4.DESCRIPTION = 'some_description'
AND A7.CODE IN
('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')
ORDER BY A1.LASTNAME ASC

[Erland Sommarskog]

ColdCanuck (co**@canuck.ca) writes:

I am trying to convert this to the newer ANSI sytax. My second SQL
statement below produces 67 rows (duplicates):

SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1
JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)
JOIN
( SELECT C.CONTACTID
...

You should have left those IN clauss alone. Or rewritten them as EXISTS
clauses. If you ADDRESS with the subquery, a row from ADDRESS is included
for every time it matches the derived table. You only want it included
once if it matches at all. Whether it matches 1 or ten times is irrelevant
to you.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bell]

Hi

The join syntax only requires the method that the two tables are being
related to each other, the rest of the where clause can stay the same:

SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1
JOIN ADDRESS A6 ON A1.ADDRESSID=A6.ADDRESSID
WHERE A1.CONTACTID IN
(SELECT A4.CONTACTID
FROM CONTACT_LEADSOURCE A4
JOIN LEADSOURCE A5 ON A4.LEADSOURCEID = A5.LEADSOURCEID
WHERE A5.DESCRIPTION = 'some_description' )
AND A1.CONTACTID IN
(SELECT A2.CONTACTID
FROM TICKET A2
JOIN ENROLLHX A3 ON A2.TICKETID = A3.EVXEVTICKETID
JOIN EVENT A7 ON A3.EVENTID = A7.EVENTID
WHERE A3.STATUS IN ('R', 'Confirmed')
AND A7.CODE IN
('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))
ORDER BY A1.LASTNAME ASC

Without checking I think this may be the same!

SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1
JOIN ADDRESS A6 ON A1.ADDRESSID=A6.ADDRESSID
JOIN CONTACT_LEADSOURCE A4 ON A1.CONTACTID = A4.CONTACTID
JOIN LEADSOURCE A5 ON A4.LEADSOURCEID = A5.LEADSOURCEID AND
A5.DESCRIPTION = 'some_description'
JOIN TICKET A2 ON A1.CONTACTID ON A2.CONTACTID
JOIN ENROLLHX A3 ON A2.TICKETID = A3.EVXEVTICKETID AND A3.STATUS IN ('R',
'Confirmed')
JOIN EVENT A7 ON A3.EVENTID = A7.EVENTID AND A7.CODE IN
('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')
ORDER BY A1.LASTNAME ASC

John

"ColdCanuck" <co**@canuck.ca> wrote in message
news:2lZGc.2076$iw3.1593@clgrps13...

Help!

I'm trying to understand the new ANSI join syntax (after many years of
coding using the old style). I am now working with an application that only understands ANSI syntax so I am struggling.

My first (old style syntax) SQL statement below produces 60 rows:

SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1,
ADDRESS A6
WHERE A1.ADDRESSID=A6.ADDRESSID
AND A1.CONTACTID IN
(SELECT A4.CONTACTID
FROM CONTACT_LEADSOURCE A4,
LEADSOURCE A5
WHERE A4.LEADSOURCEID = A5.LEADSOURCEID
AND A5.DESCRIPTION = 'some_description' )
AND A1.CONTACTID IN
(SELECT A2.CONTACTID
FROM TICKET A2,
ENROLLHX A3,
EVENT A7
WHERE A3.STATUS IN ('R', 'Confirmed')
AND A2.TICKETID = A3.EVXEVTICKETID
AND A3.EVENTID = A7.EVENTID
AND A7.CODE IN
('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))
ORDER BY A1.LASTNAME ASC

I am trying to convert this to the newer ANSI sytax. My second SQL statement below produces 67 rows (duplicates):

SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,
A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
FROM CONTACT A1
JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)
JOIN
( SELECT C.CONTACTID
FROM CONTACT C
JOIN CONTACT_LEADSOURCE A4 ON (C.CONTACTID = A4.CONTACTID)
JOIN LEADSOURCE A5 ON (A4.LEADSOURCEID =
A5.LEADSOURCEID
AND A5.DESCRIPTION =
'some_description' )) AS C1 ON C1.CONTACTID = A1.CONTACTID
JOIN
(SELECT C2.CONTACTID
FROM CONTACT C2
JOIN TICKET A2 ON (C2.CONTACTID =
A2.CONTACTID)
JOIN ENROLLHX A3 ON (A2.TICKETID =
A3.TICKETID AND A3.STATUS in ('R', 'Confirmed'))
JOIN EVENT A7 ON (A3.EVENTID = A7.EVENTID

AND A7.CODE IN ('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')))
AS C3 ON C3.CONTACTID = A1.CONTACTID
Can anyone shed some light on what I am missing?

cheers,
Norm