DispencerID_Wholesaler

• JMI

  SSChampion

  Points: 11831

  More actions

  May 5, 2008 at 2:43 pm

  #206958

  if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#md'))

  DROP TABLE #md;

  if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#mr'))

  DROP TABLE #mr;

  if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#rd'))

  DROP TABLE #rd;

  create table #md (dispencerid char(3),wholesaler char(1))

  create table #mr (dispencerid char(3))

  create table #rd (dispencerid char(3),wholesaler char(1))

  insert #md(dispencerid,wholesaler) select '144','N'

  insert #md(dispencerid,wholesaler) select '145','N'

  insert #md(dispencerid,wholesaler) select '160','N'

  insert #md(dispencerid,wholesaler) select '170','Y'

  insert #mr select '144'

  insert #mr select '160'

  -------------------------------------------------------

  Can I write one SELECT statement to get these two sets?

  select #md.dispencerid, #md.wholesaler from #md inner join #mr mr1 on #md.dispencerid = mr1.dispencerid

  select #md.dispencerid from #md left outer join #mr on #md.dispencerid = #mr.dispencerid where #mr.dispencerid is null and #md.wholesaler = 'Y'

  Thanks,

  Rob

• Anam Verma

  SSCertifiable

  Points: 5365

  More actions

  May 6, 2008 at 12:10 am

  #811022

  Hi Rob

  I am not sure what you are after? Can you please provide the resultset?

  Do you want UNION of both statements?

  If you can provide more info, that would be great.

  Cheers

  Anam

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  May 6, 2008 at 3:38 am

  #811077

  Hi Rob,

  try this one.

  IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#md'))

  DROP TABLE #md;

  IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#mr'))

  DROP TABLE #mr;

  IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype in ('U') and o.id = object_id(N'tempdb..#rd'))

  DROP TABLE #rd;

  CREATE TABLE #md (dispencerid char(3),wholesaler char(1))

  CREATE TABLE #mr (dispencerid char(3))

  CREATE TABLE #rd (dispencerid char(3),wholesaler char(1))

  INSERT #md(dispencerid,wholesaler) SELECT '144','N'

  INSERT #md(dispencerid,wholesaler) SELECT '145','N'

  INSERT #md(dispencerid,wholesaler) SELECT '160','N'

  INSERT #md(dispencerid,wholesaler) SELECT '170','Y'

  INSERT #mr SELECT '144'

  INSERT #mr SELECT '160'

  SELECT *

  FROM #md AS D

  LEFT JOIN #mr AS R

  ON (D.dispencerid = R.dispencerid)

• JMI

  SSChampion

  Points: 11831

  More actions

  May 6, 2008 at 7:43 am

  #811197

  Guys,

  Here is what I'm trying to get.

  Table #md

  dispencerid wholesaler

  -----------------------

  144 N

  145 N

  160 N

  170 Y

  table #mr

  dispencerid

  -----------

  144

  160

  My goal is to get all the records from both tables that have a match by "dispencerid"

  plus

  all #md records that don't have a match in #rd where "wholesaler = Y"

  So I'm looking for this result set:

  dispencerid wholesaler

  ------------------------

  144 N

  160 N

  170 Y

• JMI

  SSChampion

  Points: 11831

  More actions

  May 6, 2008 at 8:03 am

  #811211

  Guys,

  Actually this statement below with UNION does the job:

  select #md.dispencerid from #md inner join #mr mr1 on #md.dispencerid = mr1.dispencerid

  UNION

  select #md.dispencerid from #md left outer join #mr on #md.dispencerid = #mr.dispencerid where #mr.dispencerid is null and #md.wholesaler = 'Y'

  But I am trying to get used to better query construction practices.

  Ia m still looking for on SELECT statement. Without UNION.

• GSquared

  SSC Guru

  Points: 260824

  More actions

  May 6, 2008 at 8:43 am

  #811259

  Actually, the query as written isn't all that bad. How well it works will depend on your indexes (assuming the temp tables have indexes created on them, or that you will be using permanent tables in the final query instead of temp tables).

  You could change it to:

  select #md.dispencerid

  from #md

  left outer join #mr

  on #md.dispencerid = #mr.dispencerid

  where #mr.dispencerid is null and #md.wholesaler = 'Y'

  or #mr.dispencerid is not null and #md.wholesaler != 'Y'

  Which might be easier to maintain (or might not), because of the lack of the Union statement. It might also be slower if the tables have the right indexes in them, because of the "or" in the Where clause.

  Another option might be:

  select #md.dispencerid from #md inner join #mr mr1 on #md.dispencerid = mr1.dispencerid

  where #mr.dispencerid is not null and #md.wholesaler != 'Y'

  UNION ALL

  select #md.dispencerid from #md left outer join #mr on #md.dispencerid = #mr.dispencerid where #mr.dispencerid is null and #md.wholesaler = 'Y'

  Again, further conditions in the Where clause, and the use of indexes, might speed that one up.

  But, in all cases, since you don't have much in the Where clause that could be used by indexes, just the join data, it's pretty much going to have to do table scans (clustered index scans if you have clustered indexes), so it might be a moot point.

  Try all of these, see which one works best in your system with your data.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon