Using 'IN' to replace 'EXCEPT'

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

Other Forums

MS Access

Using 'IN' to replace 'EXCEPT'

Author

Topic

wilby
Starting Member

3 Posts

Posted - 2008-05-28 : 10:19:58

Hi everyone,

I have wriiten a query that works great in sql server but MS Access does not support EXCEPT. I have to rewrite this query using 'IN' or 'NOT IN' but can not figure it out because it must be compared on all fields. I'm sure this will require multiple subqueries because in must return one field but I can not rap my brain around it. Could someone please help with this? Here is the 'EXCEPT' query I know works.

SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
            sh.Balance, sh.Available_Bal FROM
            (((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
            sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
            INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
            Participation_Type ON ac.Participation_Id = Participation_Type.ID)
			WHERE ac.INDIVIDUAL_ID = 5249
            Except
SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
            sh.Balance, sh.Available_Bal FROM
            (((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
            sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
            INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
            Participation_Type ON ac.Participation_Id = Participation_Type.ID)
            WHERE ((ac.participation_id <> 101) AND (sh.share_type > 49 )) AND (ac.Individual_Id = 5249);

This code using 'IN' gets me close but is still not quite correct.

SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
            sh.Balance, sh.Available_Bal FROM
            (((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
            sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
            INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
            Participation_Type ON ac.Participation_Id = Participation_Type.ID)
			WHERE (ac.INDIVIDUAL_ID = 5249) AND sh.member_nbr not in
(SELECT sh.member_nbr from Share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49) AND sh.account_nbr not in
(SELECT sh.account_nbr from share sh inner join accountindividual ac on sh.member_nbr = ac.member_nbr AND sh.account_nbr = ac.account_nbr
WHERE ac.participation_id <> 101 AND sh.share_type > 49)

Wilby Jackson

wilby
Starting Member

3 Posts

Posted - 2008-05-28 : 13:13:03

Here is the answer to my own post. I was making it more difficult than it needed to be.

SELECT DISTINCT sh.Member_Nbr, sh.Account_Nbr, st.Share_Desc,
            sh.Balance, sh.Available_Bal FROM
            (((SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
            sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr)
            INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID) INNER JOIN
            Participation_Type ON ac.Participation_Id = Participation_Type.ID)
			WHERE ac.INDIVIDUAL_ID = 5249
			AND ( ac.Individual_Id NOT IN 
(SELECT DISTINCT ac.individual_id FROM
            SHARE sh INNER JOIN AccountIndividual ac ON ac.Member_Nbr =
            sh.Member_Nbr AND ac.Account_Nbr = sh.Account_Nbr
            INNER JOIN SHARETYPE st ON sh.Share_Type = st.Share_Type_ID INNER JOIN
            Participation_Type ON ac.Participation_Id = Participation_Type.ID
            WHERE ac.participation_id <> 101 AND sh.share_type > 49));

Wilby Jackson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources