Member Avatar for cubespeed

Hello to everybody

I have a problem with a MYSQL query and I almost got it but am stuck for a while now. I would appreciate if anyone could help me or point me in the right direction.

I have 3 tables:

[B]t1 (products)[/B]

id | name 
---+-------
 1 | john
 2 | mark
 3 | peter
 4 | tom
 5 | tracy
 6 | fillip 

[B]t2 (categories)[/B]

id | t1_id | cat_id
---+-------+--------
 1 |   1   |   70
 2 |   2   |   70
 3 |   5   |   70
 4 |   5   |   80
 5 |   6   |   70
 6 |   6   |   80

[B]t3 (attributes)[/B]

id | t1_id | att_id | value
---+-------+--------+-------
 1 |   1   |   30   | val1|val2
 2 |   1   |   50   | a.jpg
 3 |   2   |   30   | val3|val4
 4 |   2   |   40   | val6
 5 |   2   |   50   | b.jpg
 6 |   3   |   30   | val2|val3
 7 |   3   |   40   | val5|val6
 8 |   4   |   40   | val6
 9 |   4   |   50   | c.jpg
10 |   5   |   30   | val2|val3|val4
11 |   5   |   40   | val5
12 |   5   |   50   | d.jpg
13 |   6   |   30   | val1|val3|val4
14 |   6   |   40   | val5|val6

possible values for att_id 30: val1, val2, val3, val4
possible values for att_id 40: val5, val6, val7, val8

I would like to display all products that are in category 70
and have an attribute with att_id = 30 and value "val1"
and have an attribute with att_id = 40 and value "val5".

All I got is this query:

SELECT  DISTINCT t1.id, t1.name, t2.cat_id, t3.value
FROM t1 
Left Join t2 ON t2.t1_id = t1.id 
Left Join t3 ON t3.t1_id = t1.id
WHERE t2.cat_id = 70
AND
(t3.att_id = 30 OR t3.att_id = 40)
AND MATCH ( t3.value ) AGAINST ('"val1" "val5"' IN BOOLEAN MODE);

That gives me this output:

id |  name  | cat_id |   value
---+--------+-------------------------
 1 |  john  |   70   | val1|val2
 5 |  tracy |   70   | val5
 6 | fillip |   70   | val1|val3|val4
 6 | fillip |   70   | val5|val6

Output should be:

id |  name  | cat_id 
---+--------+--------
 6 | fillip |   70

So on output from my previous query I need the record that has both val1 and val5 (the one that is duplicated).

I would appreciate any help because I really needed this working like one month ago and I totally screwed up :(
Please to any kind soul - help me.

Simon

Edited by cubespeed because: n/a
  • 2 Contributors
  • 1 Reply
  • 166 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by griswolf
Member Avatar for griswolf

What you have written makes sense against the output: You are getting an 'OR' condition on 'val1' and 'val5', you want an 'AND' condition. You may be able to use the '+' operator:

...
AND MATCH ( t3.value ) AGAINST ('"+val1" "+val5"' IN BOOLEAN MODE);

or you may need two MATCH clauses. I have not experimented. See here: http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html (if 5.0 is not your mysql version, start here instead: http://dev.mysql.com/doc/ and dive in along your version)

Edited by griswolf because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.