I have two tables where I want to check if a value exists in the second table. The problem is that in the second table the group field could either be a individual value or a comma delimited value. I need a query to check if the group value exists in table 2 either as a single value or in a comma delimited value
Table 1
Id | Group |
---|---|
1 | Group1 |
2 | Group2 |
3 | Group3 |
5 | Group4 |
6 | Group4 |
7 | Group2 |
Table 2
Group |
---|
Group1, Group2 |
Group3 |
The results of the select query would be.
Table 1 Filtered Results
Id | Group |
---|---|
1 | Group1 |
2 | Group2 |
3 | Group3 |
7 | Group2 |
CodePudding user response:
Maybe this is what you want:
select * from Table1 t1 where exists (
select * from Table2 t2 where t2.[Group] like '%' t1.[Group] '%'
)
But be careful!!! "Group" is a keyword in SQL, you should not name a field like that.
UPDATE on Larnu comment:
To avoid Group1
and Group10
match, we can do that:
select * from Table1 t1 where exists (
select * from Table2 t2 where (t2.[Group] like '%' t1.[Group] or t2.[Group] like '%' t1.[Group] ',%')
)
CodePudding user response:
Try joining the two tables with a LIKE
clause, like this:
DECLARE @t1 TABLE (id INT, [group] VARCHAR(255))
DECLARE @t2 TABLE ([group] VARCHAR(255))
INSERT INTO @t1 VALUES (1,'g1'),(2,'g2'),(3,'g3'),(5,'g4'),(6,'g4'),(7,'g2')
INSERT INTO @t2 VALUES ('g1,g2'),('g3')
SELECT DISTINCT t1.*
FROM @t1 t1 INNER JOIN @t2 t2
ON t2.[group] LIKE '%' t1.[group] OR t2.[group] LIKE '%' t1.[group] ',%'
Result:
id | group |
---|---|
1 | g1 |
2 | g2 |
3 | g3 |
7 | g2 |
UPDATED to accommodate Larnu's comment to Carlos's post