1

I have a normalized database, where a superior table contains different products and a subordinated table characteristics. The subordinate table is linked to the superior table by a foreign key. This means that there may be several occurrences of one and the same characteristic_id (= foreign key) in the subordinate table if several characteristics match the properties of a product, e.g.

Superior table  (product_main_table):

product_id      product_name            
23          apple   
24          orange

25          strawberry      

Subordinate table (product_characteristics):

characteristic_id   product_name            characteristic

23          apple               green           
23          apple               sweet   
23          apple               small   
23          apple               american    
24          orange              orange          
24          orange              sourly  
24          orange              big 

24          orange              african 

25          strawberry          red

25          strawberry          sweet

I have an html-form to read out the data and there should also be a possibility to search for all products that DO NOT match certain characteristics. However, this does not work. When I enter the following request:

SELECT DISTINCT main.product_name 
FROM product_main_table main, product_characteristics prodchar 
WHERE prodchar.characteristic != 'sweet'

the result is 'apple', 'orange' and 'strawberry', wheresas it should be 'orange', only. The other two products are selected of course, because they have other characteristics than 'sweet' that are selected.

I would be more than happy, if the solution would fit into my general SQL-request

$sql = "SELECT DISTINCT $selection FROM $tabelle WHERE $masterarray";

where $selection, $tabelle and $masterarray get their contents from the html-entries.

How do I have to make my where-statement, so that a product is not selected, if it contains a single characteristic that should not be present?

Alex
  • 16,739
  • 1
  • 28
  • 51
Columbus
  • 89
  • 1
  • 9
  • 1
    You need one more condition. add main.product_name=sub_ord.product_name :) – WannaBeCoder Oct 19 '16 at 18:17
  • If you had another field in your characteristics database for category (color, taste, etc), then you could do WHERE prodchar.category = 'taste' AND prodchar.characteristic != 'sweet' – Stevish Oct 19 '16 at 18:22
  • 1
    Thank you for your answer! :) However, the result is the same as before. – Columbus Oct 19 '16 at 18:23
  • Thanks Stevish, but the characteristics can be numerous for any product and therefore I would need a general solution with variables. – Columbus Oct 19 '16 at 18:26

4 Answers4

0
SET @characteristic='sweet,sour,bland';
...
WHERE FIND_IN_SET(prodchar.characteristic, @characteristic) = 0

or per your requested example

$sql = "SET @characteristic='sweet,sour,bland';SELECT DISTINCT $selection FROM $tabelle WHERE FIND_IN_SET(prodchar.characteristic, @characteristic) = 0;";

you can also use php string concatenation to echo in your variable into the $sql variable if needed.

Jay Lane
  • 1,379
  • 15
  • 28
0

I think you could use the NOT IN function:

... WHERE prodchar.characteristic NOT IN (SELECT characteristic FROM product_characteristics WHERE name = main.product_name)

Stevish
  • 734
  • 5
  • 17
0

You could do some thing like

SELECT DISTINCT prodchar.product_name FROM product_characteristics prodchar WHERE prodchar.product_name not in (select product_name from product_characteristics where characteristic in ('sweet'))

http://sqlfiddle.com/#!9/ce6f6/7

Performance would be bad, Try using Joins. Join vs. sub-query

Community
  • 1
  • 1
WannaBeCoder
  • 1,280
  • 2
  • 17
  • 40
0

http://sqlfiddle.com/#!9/7fd63b/5

SELECT pmt.product_name
FROM product_characteristics pc
JOIN product_main_table pmt
ON pmt.product_id = pc.characteristic_id
GROUP BY characteristic_id
HAVING SUM(characteristic IN ('sweet'))=0
Alex
  • 16,739
  • 1
  • 28
  • 51
  • Thanks Alex! Your solution brings the right result! :) Is it possible to integrate the JOIN-Statement in my Select-query with the Where-Statement? Because, in parallel, multiple other queries should be possible. – Columbus Oct 19 '16 at 20:11
  • Alex, a Problem with this solution just occured: if there is only one value that should not be present, it works perfect! However, if there are further values added (e.g. HAVING SUM(characteristic IN ('sweet, big, red'))=0) the list becomes longer and there are values, that shouldn´t be selected. – Columbus Oct 19 '16 at 21:32
  • I can not see any problem if you add more values in `IN` array. please provide any proof: sample of row data and list of values that should be excluded. – Alex Oct 20 '16 at 12:41
  • Thank you Alex, I got it! :) – Columbus Oct 23 '16 at 14:01