Jump to content

mutliple conditions for same join for same column


Destramic
Go to solution Solved by benanamen,

Recommended Posts

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 by Destramic
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 by benanamen
Link to comment
Share on other sites

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 :happy-04:

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.