HOW TO SEARCH IN SAME-TYPE COLUMNS GIVEN A SET OF UNSORTED VALUES?

Tanuj Kumar

Total Post:134

Points:940
Posted by  Tanuj Kumar
 1072  View(s)
Ratings:
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!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: How to search in same-type columns given a set of unsorted values?

    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.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!