Using results of an EXCEPT query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Using results of an EXCEPT query

Using results of an EXCEPT query

Rate Topic

Display Mode

Topic Options

Author

Message

lulu_lutz

Posted Monday, November 15, 2010 7:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 05, 2012 11:53 AM
Points: 128, Visits: 270

I have written the following except query...

select PtID, MedID from dbo.Pyxis
except
select [Hospital #], BASEITEMNUMBER from dbo.HMM
order by pyxis.PtID

I want to add more fields from the Pyxis table to the results of this query. Anyone have any suggestions of the best practice to do this? Should I just copy the results to another table and run another query, or can this somehow be nested? Thanks!

Post #1020781

Vishal.Gajjar

Posted Monday, November 15, 2010 7:55 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 939, Visits: 567

With MyTable
As
(
select PtID, MedID from dbo.Pyxis
except
select [Hospital #], BASEITEMNUMBER from dbo.HMM
order by pyxis.PtID
)
Select PtID, MedID, PX.AnotherColumn From MyTable
Inner Join Pyxis PX ON MyTable.PtID = PX.PtID

Vishal Gajjar
http://SqlAndMe.com

Post #1020791

lulu_lutz

Posted Monday, November 15, 2010 8:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 05, 2012 11:53 AM
Points: 128, Visits: 270

Thanks for the reply. I tried this, but I can't get it to do exactly what I want

I want the result to show addtl fields to the PtID and the MedID such as PtID; MedID; GenericMedName; MedCharge. The problem is that the additional fields are not in the second HMM table.

Post #1020793

Vishal.Gajjar

Posted Monday, November 15, 2010 8:09 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 939, Visits: 567

Have you tried this?

select PtID, MedID, GenericMedName, MedChange from dbo.Pyxis
except
select [Hospital #], BASEITEMNUMBER, '', '' from dbo.HMM
order by pyxis.PtID

Vishal Gajjar
http://SqlAndMe.com

Post #1020798

lulu_lutz

Posted Monday, November 15, 2010 8:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 05, 2012 11:53 AM
Points: 128, Visits: 270

that worked...thanks so much!!!

Post #1020804

lulu_lutz

Posted Monday, November 15, 2010 1:40 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 05, 2012 11:53 AM
Points: 128, Visits: 270

Upon closer inspection, that query didn't work. I'm thinking that this is a dead end since the same columns have to be in the query Sad

Post #1021063

Evil Kraig F

Posted Monday, November 15, 2010 3:06 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 5,672, Visits: 6,114

Ran into the same problem myself tinkering with EXCEPT. No, it's not going to do what you want, it can't completely replace LEFT JOIN t ON x/y WHERE t.x IS NULL. It only works for exact matches on all columns.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1021117

« Prev Topic | Next Topic »

Permissions