Help with a query to find atleast

• Pink123

  Default port

  Points: 1486

  More actions

  October 2, 2012 at 12:35 pm

  #145711

  I have a table A and a Table B

  Table A

  Id,

  Seq,

  Table B

  Table B Id

  Table A Id

  Table A seq

  Services

  Now for each Id in Table A there can be multiple services in table B.

  I want to write a query to find out the ids and sequence which are not having atleast one of the services(1,2,3,4,5) assigned.

  i.e all Ids which are missing any one of services (1,2,3,4,5)

  Please help

  Thanks,

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  October 2, 2012 at 12:39 pm

  #1544545

  Can you please elaborate on your table schema.

  Present some sample data and what you want the results to look like.

  Read this to help us help you[/url]

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  October 2, 2012 at 1:18 pm

  #1544565

  Have a look at my post on this thread - it's a similar problem:

  http://www.sqlservercentral.com/Forums/Topic1366725-391-2.aspx

• Pink123

  Default port

  Points: 1486

  More actions

  October 2, 2012 at 1:40 pm

  #1544574

  Thanks so much for the reply.I am confused .Can you explain with refernce to my example above

• Pink123

  Default port

  Points: 1486

  More actions

  October 2, 2012 at 1:55 pm

  #1544577

  Table A

  Id seq

  1231

  1242

  1252

  1263

  Table B

  TAbleBIDAIDAseqService

  112311

  212312

  312313

  412314

  512315

  612421

  712522

  i want to write a query which will give Table A id 124,125 ,126 as they dont have all of service(1,2,3,4,5)

  i dont want duplicate ids i.e if one id is missing all five then it should be counted only once

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  October 2, 2012 at 2:42 pm

  #1544588

  Pink123 (10/2/2012)

  ---

  Table A

  Id seq

  1231

  1242

  1252

  1263

  Table B

  TAbleBIDAIDAseqService

  112311

  212312

  312313

  412314

  512315

  612421

  712522

  i want to write a query which will give Table A id 124,125 ,126 as they dont have all of service(1,2,3,4,5)

  i dont want duplicate ids i.e if one id is missing all five then it should be counted only once

  Can you post ddl and sample data like found in the article at the first link in my signature? This is the same article that Ray already mentioned.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  October 2, 2012 at 2:45 pm

  #1544592

  one method to get you started......others may chime in with alternatives.

  please note how I created the set up scripts....makes it so much easier for everyone else to help you....I hope you understand.

  USE [tempdb]

  GO

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableA]') AND type in (N'U'))

  DROP TABLE [dbo].[TableA]

  GO

  CREATE TABLE [dbo].[TableA](

  [ID] [int] NOT NULL

  ) ON [PRIMARY]

  INSERT INTO [dbo].[TableA]([ID])

  SELECT 123 UNION ALL

  SELECT 124 UNION ALL

  SELECT 125 UNION ALL

  SELECT 126

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableB]') AND type in (N'U'))

  DROP TABLE [dbo].[TableB]

  GO

  CREATE TABLE [dbo].[TableB](

  [ID] [int] NOT NULL,

  [Service] [int] NOT NULL

  ) ON [PRIMARY]

  INSERT INTO [dbo].[TableB]([ID], [Service])

  SELECT 123, 1 UNION ALL

  SELECT 123, 2 UNION ALL

  SELECT 123, 3 UNION ALL

  SELECT 123, 4 UNION ALL

  SELECT 123, 5 UNION ALL

  SELECT 124, 1 UNION ALL

  SELECT 125, 2

  DECLARE @servicecnt AS int;

  SELECT @servicecnt = COUNT( DISTINCT Service )

  FROM TableB;

  --print @servicecnt

  SELECT A.ID

  FROM

  TableA A LEFT OUTER JOIN TableB B ON A.ID = B.ID

  GROUP BY A.ID

  HAVING COUNT( DISTINCT B.Service ) < @servicecnt

  OR COUNT( DISTINCT B.Service )IS NULL;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day