October 2, 2012 at 12:35 pm
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,
October 2, 2012 at 12:39 pm
October 2, 2012 at 1:18 pm
Have a look at my post on this thread - it's a similar problem:
http://www.sqlservercentral.com/Forums/Topic1366725-391-2.aspx
October 2, 2012 at 1:40 pm
Thanks so much for the reply.I am confused .Can you explain with refernce to my example above
October 2, 2012 at 1:55 pm
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
October 2, 2012 at 2:42 pm
Pink123 (10/2/2012)
Table AId 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/
October 2, 2012 at 2:45 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply