mutliple conditions for same join for same column

[Destramic]

SELECT DISTINCT(i.item_id),
       i.title
FROM items i
LEFT JOIN item_attributes ia ON ia.item_id = i.item_id
LEFT JOIN item_attributes ia2 ON ia2.item_id = i.item_id
WHERE ia.attribute = 'Clock Type' AND ia.value = 'Wall Clock'
AND ia2.attribute = 'Condition' AND ia2.value = 'New'
AND MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE)

how about this?

Edited December 30, 2015 by Destramic

[benanamen]

One too many joins and you probably want a LEFT join

SELECT i.title
FROM items i
LEFT JOIN item_attributes ia ON ia.item_id = i.item_id
WHERE ia.attribute = 'Clock Type' AND ia.value = 'Wall Clock'
AND ia.attribute = 'Condition' AND ia.value = 'New'

I dont think you need the Clock Type and condition in there. Post your current DB schema. Something still not right. 

 

Perhaps more like

SELECT i.title
FROM items i
RIGHT JOIN item_attributes ia ON ia.item_id = i.item_id
WHERE ia.value = 'Wall Clock' AND ia.value = 'New'

Even still, something wrong there due to your DB design.

Edited December 30, 2015 by benanamen

[Barand]

SELECT DISTINCT(i.item_id),
       i.title
FROM items i
LEFT JOIN item_attributes ia ON ia.item_id = i.item_id
LEFT JOIN item_attributes ia2 ON ia2.item_id = i.item_id
WHERE ia.attribute = 'Clock Type' AND ia.value = 'Wall Clock'
AND ia2.attribute = 'Condition' AND ia2.value = 'New'
AND MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE)

how about this?

 

 

You are right about joining twice to the attribute table, but you would use INNER JOINS if both attributes must match. Not sure you need the MATCH..AGAINST - if an attribute is "clock type" then the chances are it's a clock.

 

If you only join once, as benanamen suggests, then you are back to finding someone who is both male and female at the same time, to quote the earlier example.

[benanamen]

If you only join once, as benanamen suggests, then you are back to finding someone who is both male and female at the same time, to quote the earlier example.

 

As I said, there is still something wrong with his DB design. The condition of an item is not an attribute of the item. It is more of an item status that should go as an int in the items table tied to a status_options table, IE new, used. same thing with the condition. That is not an attribute, it is a condition status that should also be a key in the items table, IE:  fair, poor, good, excellent, etc.., not in the attributes table. Using a clock, attributes would be like analog, digital, round, square, electric, battery operated, pendulum, wind up..etc

Edited December 31, 2015 by benanamen

[Destramic]

You are right about joining twice to the attribute table, but you would use INNER JOINS if both attributes must match. Not sure you need the MATCH..AGAINST - if an attribute is "clock type" then the chances are it's a clock.

 

 

If you only join once, as benanamen suggests, then you are back to finding someone who is both male and female at the same time, to quote the earlier example.

 

yeah join the attributes table twice filtering out the items that way seems the only way possible...thanks for the inner join advice.

 

thank you for all your posts in this painful thread guys haha