April 1, 2013 at 6:52 am
I am looking for a query to get all records from tableA that exists in tableb inclusive.
tableB contains the combination of ID that must all exists in table A in order to be returned.
Query should return
1, 'Yes1'
2, 'Yes1'
99, 'Yes1'
2, 'Yes2'
99, 'Yes2'
30, 'Yes3'
Would like to avoid dynamic queries if possible.
CREATE TABLE [dbo].[tableA](
[A_ID] [int] NOT NULL,
[A_text] varchar(6) NOT NULL
)
--these records should be returned by query
Insert tableA
SELECT 1, 'Yes1'
Insert tableA
SELECT 2, 'Yes1'
Insert tableA
SELECT 99, 'Yes1'
Insert tableA
SELECT 2, 'Yes2'
Insert tableA
SELECT 99, 'Yes2'
Insert tableA
SELECT 30, 'Yes3'
--query should not return these records because not all records are in Table B
Insert tableA
SELECT 1, 'No1'
Insert tableA
SELECT 99, 'No1'
Insert tableA
SELECT 99, 'No2'
--these have no records in tableB and should not be returned
Insert tableA
SELECT 100, 'No3'
Insert tableA
SELECT 83, 'No4'
Insert tableA
SELECT 19, 'No5'
CREATE TABLE [dbo].[tableB](
[B_ID] [int] NOT NULL,
[A_ID] [int] NOT NULL
CONSTRAINT [PK_tableb] PRIMARY KEY CLUSTERED
(
[B_ID] ASC,
[A_ID] ASC
)
)
insert tableB
Select 1, 1
insert tableB
Select 1, 2
insert tableB
Select 1, 99
insert tableB
Select 2, 99
insert tableB
Select 2, 2
insert tableB
Select 3, 30
insert tableB
Select 4, 30
insert tableB
Select 4, 35
insert tableB
Select 5, 31
insert tableB
Select 5, 32
insert tableB
Select 5, 33
insert tableB
Select 5, 34
insert tableB
Select 5, 35
insert tableB
Select 6, 300
insert tableB
Select 7, 300
insert tableB
Select 7, 100
April 1, 2013 at 7:55 am
Great job posting ddl, sample data and desired output!!!
Can you explain how you get the results? I can't figure out what the logic is supposed to be here.
_______________________________________________________________
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/
April 1, 2013 at 8:20 am
TableB contains the combination that all must be met in tableA order to be returned from tableA. For instance
Desired Output
A_ID A_text
1, Yes1
2, Yes1
99, Yes1
The A_id 1, 2 and 99 exists in tableB as combination b_id=1
B_id is just an ID used for TableB.
Select * from tableb where b_ID=1
The following should not be returned from tableA becuase no combination exists in tableB for A_id 1 and 99 inclusive.
A_IDA_text
1No1
99No1
April 1, 2013 at 8:50 am
Todd Bernstein-449184 (4/1/2013)
TableB contains the combination that all must be met in tableA order to be returned from tableA. For instanceDesired Output
A_ID A_text
1, Yes1
2, Yes1
99, Yes1
The A_id 1, 2 and 99 exists in tableB as combination b_id=1
B_id is just an ID used for TableB.
Select * from tableb where b_ID=1
The following should not be returned from tableA becuase no combination exists in tableB for A_id 1 and 99 inclusive.
A_IDA_text
1No1
99No1
Wow your structures are making this difficult. It seems like you are trying to do some sort of foreign key based on a number of rows. EEK!!!
This code works against your sample data.
;with TableAStuffed as
(
select A_ID, A_text,
STUFF((select ',' + cast(A_ID as varchar(5))
from tableA a2 where a2.A_text = a1.A_text
order by A2.A_ID
for XML path('')), 1,1 , ' ') as FullList
from tableA a1
group by A_text, A_ID
)
, TableBStuffed as
(
select B_ID,
STUFF((select ',' + cast(A_ID as varchar(5))
from TableB b2 where b2.B_ID = b1.B_ID
order by b2.B_ID
for XML path('')), 1, 1, ' ') as FullList
from tableB b1
group by B_ID
)
select a.*
from TableBStuffed b
join TableAStuffed a on b.FullList = a.FullList
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply