Using results of an EXCEPT query

Question

Post reply

Using results of an EXCEPT query

lulu_lutz

SSCrazy

Points: 2155
More actions
November 15, 2010 at 7:41 am

#226635

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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 1

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

lulu_lutz SSCrazy Points: 2155 More actions · Answer 2

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.

Vishal.Gajjar SSCrazy Points: 2285 More actions · Answer 3

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

lulu_lutz SSCrazy Points: 2155 More actions · Answer 4

lulu_lutz

SSCrazy

Points: 2155

November 15, 2010 at 8:18 am

#1250753

that worked...thanks so much!!!

lulu_lutz SSCrazy Points: 2155 More actions · Answer 5

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 🙁

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA