Καλησπέρα,

Έχω ένα table που περιέχει τα 4 χρώματα τα οποία μπορεί να έχει κάθε αντικείμενο.

table(id, productname, color1,color2,color3,color4)

Ο χρήστης πρέπει να επιλέγει από ένα interface έως 4 τιμές χρωμάτων (μπορεί να είναι και ίδιες μεταξή τους) και να αναζητά αν υπάρχει αυτό το αντικείμενο (με τα συγκεκριμένα χρώματα). Τα χρώματα είναι 20 στον αριθμό.

Το πρόβλημα είναι ότι ο χρηστης μπορεί να επιλέξει λιγότερες από 4 τιμές και ότι η σειρά με την οποία δίνονται δεν είναι υποχρεωτικά ίδια με αυτή που αποθηκεύονται στη βάση.

Πχ αντικείμενο 1 κόκκινο κίτρινο μπλε κόκκινο

ο χρήστης ζητά να βρεί αντικείμενο με τα χρωμματα κόκκινο, κόκκινο μπλε και οτιδίποτε άλλο.

Υπάρχει τρόπος να καταφέρω κάτι τέτοιο χωρίς να γράψω 50 or ή 10 εμφωλευμένα select?

Υποψιάζομαι ότι αυτό που περιγράφεις είναι μια ατυχής ανάλυση και υλοποίηση του χρώμα-μέγεθος σε εμπορική εφαρμογή. Έτσι όπως έχει σχεδιαστεί, με τις 4 δοτές στήλες μέσα στον πίνακα του προϊόντος, πρέπει να καταφύγεις σε επικίνδυνα ακροβατικά για να κάνεις την οποιαδήποτε διαχείριση. Ήδη, ξεκινώντας από αυτή τη φαινομενικά απλή αναζήτηση έχουν αρχίσει τα προβλήματα. Σκέψου ότι αύριο θα έχεις περισσότερα διαθέσιμα χρώματα, θα θέλεις δίχρωμα προϊόντα ή τα 4 χρώματα ανά προϊόν θα γίνουν 5. Αν αυτό που περιγράφεις δεν αφορά πανεπιστημιακή εργασία, κάποιες από αυτές τις αλλαγές είναι βέβαιες.

Θα ήταν προτιμότερο να έχεις τον πίνακα των προϊόντων Product(ProductId, ProductName), τον πίνακα των διαθέσιμων χρωμάτων Color(ColorId, ColorName, RGB) και να τα συνδέεις μεταξύ τους με σχέση πολλά προς πολλά ProductColors(ProductId, ColorId). Τότε το ερώτημά σου θα ήταν κάτι του τύπου:

SELECT Product.ProductId, ProductName
FROM Product
INNER JOIN ProductColor PC1 ON Product.ProductId = PC1.ProductId AND PC1.ColorId=@Color1
INNER JOIN ProductColor PC2 ON Product.ProductId = PC2.ProductId AND PC2.ColorId=@Color2
INNER JOIN ProductColor PC3 ON Product.ProductId = PC3.ProductId AND PC3.ColorId=@Color3

Αν πάλι δεν έχεις δυνατότητα να επέμβεις στο σχήμα των πινάκων σου, τότε μπορείς να προσομοιώσεις το παραπάνω σχήμα με ένα view του τύπου

CREATE VIEW ProductColor
AS
SELECT ProductId, Color1 AS Color
FROM Product
UNION
SELECT ProductId, Color2 AS Color
FROM Product AS P2
UNION
SELECT ProductId, Color3 AS Color
FROM Product AS P3
UNION
SELECT ProductId, Color4 AS Color
FROM Product AS P4

Αλλά αν σου πω ότι μου αρέσει αυτό θα σου πω ψέματα

Συμφωνώ και επαυξάνω με τη Νατάσα ως προς την ανάγκη σωστότερης σχεδίασης. Στην περίπτωση που δεν μπορείς να πειράξεις τους πίνακές σου, σκέφτηκα και το παρακάτω:

select *
from product
where case color1
   when @color1 THEN 1
   when @color2 THEN 1
   when @color3 THEN 1
   when @color4 THEN 1
   else 0
end +
case color2
   when @color1 THEN 1
   when @color2 THEN 1
   when @color3 THEN 1
   when @color4 THEN 1
   else 0
end +
case color3
   when @color1 THEN 1
   when @color2 THEN 1
   when @color3 THEN 1
   when @color4 THEN 1
   else 0
end +
case color4
   when @color1 THEN 1
   when @color2 THEN 1
   when @color3 THEN 1
   when @color4 THEN 1
   else 0
end = @colorcount

Το μειονέκτημα αυτής της λύσης είναι ότι χρειάζεσαι άλλη μία παράμετρο (στο query "@colorcount") που είναι το πλήθος των χρωμάτων που ψάχνει ο χρήστης. Υποθέτω αυτό μπορείς να το πάρεις από το UI.

Σας ευχαριστώ πολύ και τους 2 για τις απαντήσεις. Έχω δυνατότητα αλλαγής της δομή. Πάντως ακόμα και η φαινομενικά καλύτερη λύση με τα joins απαιτεί 3-4 joins... Τι γίνεται αν είμαστε σε αυτήν την θέση και μας ενδιαφέρει η απόδοση;

Πιστεύω ότι τo performance έρχεται αφού εξασφαλίσεις ότι η εφαρμογή σου θα έχει συντηρησιμότητα και επεκτασιμότητα. Όπως σου είπε και η Νατάσα, η σχεδίαση πάσχει σε αυτό.

Από την άλλη μεριά, γιατί να είναι πρόβλημα η λύση με τα joins; Ίσα-ίσα, θα έχει καλύτερη απόδοση από οποιαδήποτε άλλη λύση παρουσιάσαμε. Το view της Νατάσας, πάντοτε θα κάνει τέσσερα SELECT ακόμα και αν ψάχνεις για ένα χρώμα. Το δικό μου, πάντοτε θα κάνει evaluate τέσσερα CASE για την ίδια περίπτωση. Την λύση των OR δεν την κουβεντιάζουμε καν, αποχαιρέτα τα indexes. Από την άλλη μεριά, τα joins μπορούν να εκτελεστούν με πολλούς διαφορετικούς τρόπους εσωτερικά στον SQL Server, ανάλογα με το προφίλ των δεδομένων (το selectivity που έχει η κάθε τιμή της αναζήτησης) ώστε να έχεις το πιο eficient execution plan. Επιπρόσθετα, ακόμα και αν ψάχνεις για όλα τα χρώματα, αρκεί ένα από αυτά να έχει μικρό selectivity ώστε να ευεργετήσει ολόκληρη την αναζήτηση.