CODEWITHSUNDEEP
sqlsql-server
J K
J K

Reputation: 505

Join tables with comma separated list

SELECT * 
FROM  Products, 
      ProductDetails 
WHERE Products.ProductID  IN (111,222,333,444,555) 
AND Products.ProductID    =  ProductDetails.ProductID

My current SQL is like the above. Now, there is a new requirement, where I want the ProductID from the comma separated list to still be available in the recordset even if it's not found in the Products and ProductDetails tables.

How can I do this? Maybe can achieve that using LEFT JOIN? I am not really good with SQL, hope someone can help with working codes.

Thanks.

Upvotes: 2

Views: 5781

Answers (5)

alzaimar
alzaimar

Reputation: 4622

Your IN clause will not help you in this case. You have to provide a table which contains the IDs. One way was introduced already: a UDF which converts a comma separated list into a table.

There is another quite simple way to get what you want:

select * 
  from (
         SELECT * FROM (VALUES(111),(222),(333),(444),(555)) as x(ID)
        ) x 
        left join Products on x.ID = Products.ID 
        left join ProductDetails 
          on Products.ProductID = ProductDetails.ProductID

This lists all IDs from the list. In case there is a matching Product it will list that Product. And in case there are ProductDetails for that product, it will list those details.

Upvotes: 2

Suresh
Suresh

Reputation: 1169

I suggest you should create a 'Table-Values Function', which will return you a table based result set from your comma separate string

CREATE FUNCTION [dbo].[funcSplit]
(
    @param      NVARCHAR(MAX),
    @delimiter  CHAR(1)
)
RETURNS @t TABLE (val NVARCHAR(MAX))
AS
BEGIN
    SET @param += @delimiter

    ;WITH a AS
    (
        SELECT CAST(1 AS BIGINT) f,
               CHARINDEX(@delimiter, @param) t,
               1 seq
        UNION ALL
        SELECT t + 1,
               CHARINDEX(@delimiter, @param, t + 1),
               seq + 1
        FROM   a
        WHERE  CHARINDEX(@delimiter, @param, t + 1) > 0
    )
    INSERT @t
    SELECT SUBSTRING(@param, f, t - f)         
    FROM   a
           OPTION(MAXRECURSION 0)

    RETURN
END

and use LEFT JOIN, so you will always get result set from your first query, which is your input string, which has product IDs, Below is an example query. (You don't need to write Stored Procedure, you can just use query, and pass your ProductIds in @str variable.)

DECLARE @str nvarchar(MAX)
SET @str='111,222,333,444,555'

SELECT val , p.ProductID, pd.ProductID from dbo.[funcSplit](@str,',')  a
LEFT JOIN Products p ON p.ProductID=a.val
LEFT JOIN ProductDetails pd ON pd.ProductID=a.val

You will get output like

val p.ProductID pd.ProductID
111 111      NULL
222 222      222
333 333      NULL
444 NULL     NULL

Hope this will work!

Thanks Suresh

Upvotes: 3

Aravinth Kannan
Aravinth Kannan

Reputation: 535

Left Join should work. I have created sample and executed it is working. It shows all the product even through it is not present in the ProductDetail.

 select p.*,pd.* from Product p left join 
 ProductDetails pd  on (p.id = pd.ProductId) where
 p.id in (1,2,3,5)

List of Products

enter image description here

List of Product Details

enter image description here

And the result of the Query above query is

enter image description here

Upvotes: 0

AK47
AK47

Reputation: 3797

Try Left join,

;With CTE as
(
select 111 as a
Union All
select 222
Union All
select 333
Union All
select 444
Union All
select 555
)
Select CTE.*,P.*, PD.*  from CTE
left join Products P on P.ProductID =CTE.a
left join ProductDetails PD 
ON 
    P.ProductID = PD.ProductID 
WHERE 
    CTE.a IN (111,222,333,444,555)

Upvotes: 0

Elixir Techne
Elixir Techne

Reputation: 1856

Please try below Query.

SELECT 
    P.*, PD.* 
FROM 
    Products P inner join ProductDetails PD 
ON 
    P.ProductID = PD.ProductID 
WHERE 
    P.ProductID IN (111,222,333,444,555)

Let me know in case you need more details.

Upvotes: -1

Related Questions