Destramic Posted December 30, 2015 Author Share Posted December 30, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 30, 2015 Share Posted December 30, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted December 30, 2015 Share Posted December 30, 2015 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. 1 Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 31, 2015 Share Posted December 31, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 31, 2015 Author Share Posted December 31, 2015 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.