Inner Join issue

• Lamprey13

  SSCertifiable

  Points: 6210

  More actions

  July 1, 2009 at 11:41 am

  #1018616

  Nuts (6/30/2009)

  ---

  Hi

  Thanks for your reply.

  I tried your query but there are still few patient IDs appearing in the result who have been on both type of Drugs..

  I do not see how that is possible given the query. Can you post some sample data that causes the unexpected output?

• nidhisharma01

  SSC Veteran

  Points: 268

  More actions

  July 2, 2009 at 12:28 am

  #1018923

  My query is: -

  select * from [dbo].[Main Table]a

  where [drug name]='typeA' and not exists

  (select * from [dbo].[Main Table]b

  where a.[calendar year]=b.[calendar year] and

  a.[Patient ID]=b.[Patient ID] and

  b.[drug name]='typeB'

  --b.[drug name]'typeA'

  )

  ---added .[Patient ID] in inner query to return data for the same patient

  1. Inner query will filter out drugs of typeB having same year as of typeA

  select * from [dbo].[Main Table]b

  where

  a.[calendar year]=b.[calendar year] and

  a.[Patient ID]=b.[Patient ID] and

  b.[drug name]='typeB'

  2. NOT EXISTS clause will filter out drugs of same year and typeB.

  3. Complete query will return only TypeA with no typeB in same year.

  I used the same test data to verify:

  USE [main table]

  GO

  INSERT INTO [main table]([Drug name],

  [calendar year],[calendar month],[quarter],[patient id])

  SELECT 'typeA' ,2000,1,'q1','ac1'

  UNION ALL

  SELECT 'typeA' ,2001,2,'q1','qwe2'

  UNION ALL

  SELECT 'typeB' ,2000,1,'q1','ac1'

  UNION ALL

  SELECT 'typeB' ,2005,12,'q4','bgresd'

  UNION ALL

  SELECT 'typeB',2001,2,'q1','qwe2'

  UNION ALL

  SELECT 'typeA' ,2002,2,'q1','qwe2'

  UNION ALL

  SELECT 'typeA',2005,1,'q1','bgresd'

  UNION ALL

  SELECT 'typeA',2005,1,'q1','bgresd1'

  This will return

  Drug name Calendar yearCalendar monthQuarterPatient IDtypeA 2005 1 q1 bgresd1

  In 2000, Patient ID 'ac1' uses drug typeA and typeB. so ignored

  In 2001, Patient ID 'qwe2' uses drug typeA and typeB. so ignored

  In 2005, Patient ID 'bgresd' uses drug typeA and typeB. so ignored

  In 2005, Patient ID 'bgresd1' uses only drug typeA. so resulted in query.

  Can I have a look at your test data where you din't receive desired output?

• Nuts

  SSCrazy

  Points: 2547

  More actions

  July 2, 2009 at 2:56 pm

  #1019483

  This query works fine.

  There were some issues in my database.

  Thanks