Using results of an EXCEPT query

  • 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!

  • 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

  • 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.

  • 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

  • that worked...thanks so much!!!

  • 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 🙁

  • 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

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

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