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

Total Post:134

Points:940
Posted on    April-03-2013 2:39 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 832  View(s)
Rate this:
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!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    April-03-2013 8:16 AM

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.

Don't want to miss updates? Please click the below button!

Follow MindStick