CODEWITHSUNDEEP
mysqljoin
Rami.Q
Rami.Q

Reputation: 2476

MySQL JOIN Statement to filter rows by selected linked attributes

lets say i have the following two MySQL-Tables:

item: _ID,_CAT_ID,...

item_attribute: _ID,_ITEM_ID,...

i want to (filter items) get ONLY items which have ALL selected attributes (1,32,555,...an array of selected attributes )

something like this:

SELECT  _I.* 
FROM  item _I
INNER JOIN item_attribute _IA
ON (_I._ID = _IA._ITEM_ID AND (_IA._ID=1 OR _IA._ID=132, ...))
WHERE _I._CAT_ID=? ORDER BY _I._LAST_UPDATE ASC;"

this "wrong" statement returns items when one(due to OR) of the linked ids found, what i want is: only items which have all linked attributes.

if i change 

(_IA._ID=1 OR _IA._ID=132 OR...)

to 

(_IA._ID=1 AND _IA._ID=132 AND ...)

no matches and this makes sense, but how to rewrite the statement to get correct matches?

UPDATE:

here is a sqlfiddle: http://sqlfiddle.com/#!9/0f8ebe/6

CREATE TABLE item
    (`id` int, `pid` int, `name` varchar(55))
;

INSERT INTO item
    (`id`, `pid`, `name`)
VALUES
    (1, 2, 'A'),
    (2, 2, 'B'),
    (3, 2, 'C')
;
CREATE TABLE att
    (`id` int, `pid` int, `name` varchar(55))
;

INSERT INTO att
    (`id`, `pid`, `name`)
VALUES
    (7, 1, 'red'),
    (7, 3, 'red'),
    (2, 1, '30cm'),
    (1, 3, '40cm'),
    (5, 2, 'blue'),
    (1, 2, '40cm')
;
SELECT * 
FROM  item;

SELECT * 
FROM  att;

/* expected: items which are red AND 40cm, result should be then only item C (id=3)*/

SELECT  _I.name 
FROM  item _I
INNER JOIN att _IA
ON (_I.id = _IA.pid AND (_IA.id=7 AND _IA.id=1))
WHERE _I.pid=2 GROUP BY _I.id;

SOLUTION http://sqlfiddle.com/#!9/0f8ebe/8 (using Drews Answer):

SELECT  _I.name,count(_IA.id) as theCount 
FROM  item _I
INNER JOIN att _IA
ON (_I.id = _IA.pid) AND _IA.id in (7,1)
WHERE _I.pid=2
group by _I.id
having theCount=2

Upvotes: 3

Views: 61

Answers (2)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

It should bring all the items that have 3 particular item attributes (red, 40cm, large).

SQL Fiddle

SELECT kk.*
FROM item AS kk
INNER JOIN (
    SELECT aa.id
    FROM (
        SELECT DISTINCT bb.id
        FROM item AS bb
        INNER JOIN att AS cc 
        ON bb.id = cc.pid
        WHERE cc.name IN ('red', '40cm', 'large')
        GROUP BY bb.id 
        HAVING COUNT(*) = 3
    ) AS aa
) AS _aa
ON kk.id = _aa.id;

Result:

id  pid name
3   2   C

Upvotes: 1

Drew
Drew

Reputation: 24959

I think it is 7 lines of code, reading the tidbits from your question:

SELECT  _I.col1,_I.col2,count(_IA._ID) as theCount 
FROM  item _I
INNER JOIN item_attribute _IA
ON (_I._ID = _IA._ITEM_ID) AND _IA._ID in (1,32,555)
WHERE _I._CAT_ID=? ORDER BY _I._LAST_UPDATE ASC
group by _I.col1,_I.col2
having theCount=3

Note that theCount alias is allowable in a having clause.

Also note I put in col1 and col2 in line one, expand accordingly. The point is to list them, so the group by in line6 can mimic them for the non-aggregated columns. The value in line 7 must match the count of the values in the in clause

Upvotes: 1

Related Questions