forum

Home / DeveloperSection / Forums / How to search in same-type columns given a set of unsorted values?

Ask Question

How to search in same-type columns given a set of unsorted values?

Anonymous User182003-Apr-2013
Hi Everyone!

I have a system which backlogs products users receive within a box. The whole system is based on these kits we create with products inside.

So I got a Customers and Products which lists everything I have. And I have a Boxes table which has ProductID1, ProductID2, ProductID3 and ProductID4 fields 

and CustomerID field to point which customer received which box.

All I want to do is, given a set of products not in the same order (Ex.: ID4, ID2, ID3, ID1) I need to know all customers that hadn't received any products 

of this given set.

Is my database bad designed? What would be the best way to do this or how to make the SELECT query

Thanks in advance!
mssql servermssql server 
Updated on 03-Apr-2013
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

03-Apr-2013
Hi Tanuj!

You can do like this

where id4 not in (productid1, productid2, productid3, productid4) or
      id2 not in (productid1, productid2, productid3, productid4) or
      id3 not in (productid1, productid2, productid3, productid4) or
      id1 not in (productid1, productid2, productid3, productid4)

You should have a table called something like BoxesrProducts that would have a BoxesId and a ProductId. That way, boxes can have different sizes -- even 

beyond 4 products -- if that is desirable.

Liked By