Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using results of an EXCEPT query Expand / Collapse
Author
Message
Posted Monday, November 15, 2010 7:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 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
Posted Monday, November 15, 2010 7:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:08 AM
Points: 1,335, Visits: 658
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
Posted Monday, November 15, 2010 8:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 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
Posted Monday, November 15, 2010 8:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:08 AM
Points: 1,335, Visits: 658
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
Posted Monday, November 15, 2010 8:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 2012 11:53 AM
Points: 128, Visits: 270
that worked...thanks so much!!!
Post #1020804
Posted Monday, November 15, 2010 1:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 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
Post #1021063
Posted Monday, November 15, 2010 3:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:31 PM
Points: 6,174, Visits: 7,251
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 »

Add to briefcase

Permissions Expand / Collapse