CODEWITHSUNDEEP
sql-server
Rlogical techsoft
Rlogical techsoft

Reputation: 13

Multiple tables in the where clause SQL

I have 2 tables:-

Table_1

GetID UnitID
1     1,2,3
2     4,5
3     5,6
4     6

Table_2

ID UnitID UserID
1  1      1
1  2      1
1  3      1
1  4      1
1  5      2
1  6      3

I want the 'GetID' based on 'UserID'.

Let me explain you with an example. For e.g.

  1. I want all the GetID where UserID is 1. The result set should be 1 and 2. 2 is included because one of the Units of 2 has UserID 1.

  2. I want all the GetID where UserID is 2 The result set should be 2 and 3. 2 is included because one of Units of 2 has UserID 2.

I want to achieve this.

Thank you in Advance.

Upvotes: 0

Views: 57

Answers (3)

Sahi
Sahi

Reputation: 1484

try this:

    declare @Table_1 table(GetID INT, UnitId VARCHAR(10))
    declare @Table_2 table(ID INT, UnitId INT,UserId INT)



    INSERT INTO @Table_1
    SELECT 1,'1,2,3'
    union
    SELECT 2,'4,5'
    union
    SELECT 3,'5,6'
    union
    SELECT 4,'6'


    INSERT INTO @Table_2
    SELECT 1,1,1
    union
    SELECT 1,2,1
    union
    SELECT 1,3,1
    union
    SELECT 1,4,1
    union
    SELECT 1,5,2
    union
    SELECT 1,6,3


    declare @UserId INT = 2
    DECLARE @UnitId VARCHAR(10)

    SELECT @UnitId=COALESCE(@UnitId + ',', '') + CAST(UnitId AS VARCHAR(5)) from @Table_2 WHERE UserId=@UserId


    select distinct t.GetId
    from @Table_1 t
    CROSS APPLY [dbo].[Split](UnitId,',') AS AA
    CROSS APPLY [dbo].[Split](@UnitId,',') AS BB
    WHERE AA.Value=BB.Value

Split Function:

    CREATE FUNCTION [dbo].Split(@input AS Varchar(4000) )

    RETURNS

          @Result TABLE(Value BIGINT)

    AS

    BEGIN

          DECLARE @str VARCHAR(20)

          DECLARE @ind Int

          IF(@input is not null)

          BEGIN

                SET @ind = CharIndex(',',@input)

                WHILE @ind > 0

                BEGIN

                      SET @str = SUBSTRING(@input,1,@ind-1)

                      SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)

                      INSERT INTO @Result values (@str)

                      SET @ind = CharIndex(',',@input)

                END

                SET @str = @input

                INSERT INTO @Result values (@str)

          END

          RETURN

    END

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

The query for this will be relatively ugly, because you made the mistake of storing CSV data in the UnitID column (or maybe someone else did and you are stuck with it).

SELECT DISTINCT
    t1.GetID
FROM Table_1 t1
INNER JOIN Table_2 t2
    ON ',' + t1.UnitID + ',' LIKE '%,' + CONVERT(varchar(10), t2.UnitID) + ',%'
WHERE
    t2.UserID = 1;

enter image description here

Demo

To understand the join trick being used here, for the first row of Table_1 we are comparing ,1,2,3, against other single UnitID values from Table_2, e.g. %,1,%. Hopefully it is clear that my logic would match a single UnitID value in the CSV string in any position, including the first and last.

But a much better long term approach would be to separate those CSV values across separate records. Then, in addition to requiring a much simpler query, you could take advantage of things like indices.

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17146

You can try a query like this:

See live demo

select 
distinct userid,getid 
from Table_1 t1 
join Table_2 t2
on t1.unitId+',' like '%' +cast(t2.unitid as varchar(max))+',%'
and t2.userid=1

Upvotes: 1

Related Questions