CODEWITHSUNDEEP
mysqlsqljoin
petek
petek

Reputation: 3

Inner join multiple entries in table comparison

SELECT produkte_eintraege.id, produkte_eintraege.hersteller, produkte_eintraege.titel, produkte_eintraege.img_url 
  FROM produkte_eintraege JOIN produkte_eigenschaften 
    ON produkte_eintraege.id = produkte_eigenschaften.produkte_eintraege_id 
 WHERE (produkte_eigenschaften.eigenschaften_merkmale_id = 1 
    OR produkte_eigenschaften.eigenschaften_merkmale_id = 2) 
   AND (produkte_eigenschaften.eigenschaften_merkmale_id = 3) 
 ORDER by hits DESC

Result = empty

SELECT produkte_eintraege.id, produkte_eintraege.hersteller, produkte_eintraege.titel, produkte_eintraege.img_url 
  FROM produkte_eintraege JOIN produkte_eigenschaften 
    ON produkte_eintraege.id = produkte_eigenschaften.produkte_eintraege_id 
 WHERE (produkte_eigenschaften.eigenschaften_merkmale_id = 1 
    OR produkte_eigenschaften.eigenschaften_merkmale_id = 2) 
 ORDER by hits DESC

Result = works

Whats wrong in this part?:

AND (produkte_eigenschaften.eigenschaften_merkmale_id = 3)

Here is the table: 

INSERT INTO `produkte_eigenschaften` (`produkte_eintraege_id`, `eigenschaften_merkmale_id`)
VALUES (1, 2), (7, 1), (1, 3);

That means that one product has multiple entries in this table.

Upvotes: 0

Views: 97

Answers (3)

peterm
peterm

Reputation: 92795

Taking into account your comments to Walter's answer, which you should've put in your question in the first place, to achieve your goal you have to GROUP BY product id and put your conditions in HAVING clause

SELECT produkte_eintraege_id 
  FROM produkte_eigenschaften
-- WHERE eigenschaften_merkmale_id IN(1, 2, 3)
 GROUP BY produkte_eintraege_id
HAVING MAX(eigenschaften_merkmale_id IN (1, 2)) = 1
   AND MAX(eigenschaften_merkmale_id = 3) = 1

Here is SQLFiddle demo

Then you can JOIN back to produkte_eintraege

SELECT e.id, e.hersteller, e.titel, e.img_url
  FROM
(
    SELECT produkte_eintraege_id 
      FROM produkte_eigenschaften
    -- WHERE eigenschaften_merkmale_id IN(1, 2, 3)
     GROUP BY produkte_eintraege_id
    HAVING MAX(eigenschaften_merkmale_id IN (1, 2)) = 1
       AND MAX(eigenschaften_merkmale_id = 3) = 1
) q JOIN produkte_eintraege e
    ON q.produkte_eintraege_id = e.id
  ORDER BY hits DESC

Upvotes: 0

Ryan
Ryan

Reputation: 96

In your query you have 

WHERE (produkte_eigenschaften.eigenschaften_merkmale_id = 1 OR produkte_eigenschaften.eigenschaften_merkmale_id = 2) 
AND (produkte_eigenschaften.eigenschaften_merkmale_id = 3) ORDER by hits DESC

To paraphrase the query you are saying

SELECT * 
FROM a join b 
WHERE (a.id=1 OR a.id=2) AND (a.ID=3)

A value cannot equal both 2 and three.

SOLUTIONS:There are two solutions that may solve your problem:

SELECT pe.id, pe.hersteller, pe.titel, pe.img_url 
FROM produkte_eintraege pe JOIN produkte_eigenschaften pe2 ON pe.id = pe2.produkte_eintraege_id 
WHERE (pe2.eigenschaften_merkmale_id = 1 
  OR pe2.eigenschaften_merkmale_id = 2 
  OR pe2.eigenschaften_merkmale_id = 3) ORDER by hits DESC

Or you can just use IN

SELECT pe.id, pe.hersteller, pe.titel, pe.img_url FROM produkte_eintraege pe JOIN produkte_eigenschaften pe2 ON pe.id = pe2.produkte_eintraege_id 
WHERE pe2.eigenschaften_merkmale_id IN (1,2,3) ORDER by hits DESC

I hope that helps.

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18940

You aren't going to have a product whose Merkmale_id is both 2 and 3, no matter what data you put in. Do you mean OR instead of AND?

Upvotes: 0

Related Questions