Destramic Posted December 28, 2015 Share Posted December 28, 2015 hey guys I'm trying to join tables together but i want to be able to have multiple conditions for the same column. ie: WHERE sv.value = 'Alarm Clock' AND s.specific_id = '15' AND sv.value = 'New' AND s.specific_id = '19' now i know this is complicated as you can't see my table structure and table data but here is the query i'm trying to execute which returns 0 results when having multiple conditions from same column... SELECT i.title FROM items i LEFT JOIN category_specifics cs ON cs.category_id = i.category_id LEFT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id LEFT JOIN sub_sub_category_specifics sscs ON sscs.sub_sub_category_id = i.sub_sub_category_id LEFT JOIN sub_sub_sub_category_specifics ssscs ON ssscs.sub_sub_sub_category_id = i.sub_sub_sub_category_id INNER JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id LEFT JOIN sub_sub_category_specifics sscs2 ON sscs2.specific_id = s2.specific_id LEFT JOIN sub_sub_sub_category_specifics ssscs2 ON ssscs2.specific_id = s2.specific_id ) AS `s` LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id LEFT JOIN item_specific_values isv ON isv.item_id = i.item_id WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND sv.value = 'Alarm Clock' AND s.specific_id = '15' AND sv.value = 'New' AND s.specific_id = '19' GROUP BY i.item_id if i take away AND sv.value = 'Alarm Clock' AND s.specific_id = '15' -------OR --------- AND sv.value = 'New' AND s.specific_id = '19' then it'll works i'll be happy to post table structures and data if needed...hopefully you can see what i'm trying to do and tell me if it's possible or not and what it is i need to be doing. any help would be appreciated and like i said i can provide data if need. thank you Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 28, 2015 Share Posted December 28, 2015 (edited) I believe you answered your own question. AND (sv.value = 'Alarm Clock' AND s.specific_id = '15') OR (sv.value = 'New' AND s.specific_id = '19') Edited December 28, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 28, 2015 Share Posted December 28, 2015 (edited) I believe you answered your own question. AND (sv.value = 'Alarm Clock' AND s.specific_id = '15') OR (sv.value = 'New' AND s.specific_id = '19') Yes, but with the other conditions in the WHERE clause that would be interpreted incorrectly. The result would look for matches where the previous conditions and the first pair above match OR ones where second pair match (regardless of the previous condition, e.g. title, description, etc.). The full condition should look like this: WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND ( (sv.value = 'Alarm Clock' AND s.specific_id = '15') OR (sv.value = 'New' AND s.specific_id = '19') ) Edited December 28, 2015 by Psycho Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 28, 2015 Share Posted December 28, 2015 Ahh yes, I had that little brain tickle that something was slightly amiss. Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 28, 2015 Share Posted December 28, 2015 Ahh yes, I had that little brain tickle that something was slightly amiss. No worries, I was just about to post the same as you when I realized that gap and was in the process of revising my response when you posted. Quote Link to comment Share on other sites More sharing options...
Barand Posted December 28, 2015 Share Posted December 28, 2015 It appears that most of the tables in that query are just fluff. Most of those tables that are LEFT JOINed are not used. For example, your subquery "s" will contain all rows from specifics table (since the others are left joined) and the only condition you're using on that subquery is from the specifics table. Also you have no join condition for that subquery. Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 28, 2015 Author Share Posted December 28, 2015 this query and table structure is quite complicated...for me anyways... i've made changes as you've said: SELECT i.title FROM items i LEFT JOIN category_specifics cs ON cs.category_id = i.category_id LEFT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id LEFT JOIN sub_sub_category_specifics sscs ON sscs.sub_sub_category_id = i.sub_sub_category_id LEFT JOIN sub_sub_sub_category_specifics ssscs ON ssscs.sub_sub_sub_category_id = i.sub_sub_sub_category_id INNER JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id LEFT JOIN sub_sub_category_specifics sscs2 ON sscs2.specific_id = s2.specific_id LEFT JOIN sub_sub_sub_category_specifics ssscs2 ON ssscs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id OR s.specific_id = sscs.specific_id OR s.specific_id = ssscs.specific_id LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.item_id = i.item_id WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND ( (sv.value = 'Alarm Clock' AND s.specific_id = '15') AND (sv.value = 'Used' AND s.specific_id = '19') ) GROUP BY i.item_id i need AND ( (sv.value = 'Alarm Clock' AND s.specific_id = '15') AND (sv.value = 'Used' AND s.specific_id = '19') ) not OR as i want to match items with both specific value and id. query returns no results with both sv.value's and s.specific_id's but returns singularly. i've checked my data inserted into tables which looks fine =/ Quote Link to comment Share on other sites More sharing options...
Barand Posted December 28, 2015 Share Posted December 28, 2015 So you need to find at least one record where the specific_id has both the value 15 and the value 19 at the same time. Good hunting! Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 28, 2015 Author Share Posted December 28, 2015 yeah thats the problem there are records there which match my conditions but just aren't showing here i used group_concat with the conditions to what values im dealing with: so i know i have specific_id's of 15 and 19 and values of alarm clock and new... doesn't make sense to me why when i use my query nothing shows what am i missing here? =/ SELECT i.title FROM items i LEFT JOIN category_specifics cs ON cs.category_id = i.category_id LEFT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id LEFT JOIN sub_sub_category_specifics sscs ON sscs.sub_sub_category_id = i.sub_sub_category_id LEFT JOIN sub_sub_sub_category_specifics ssscs ON ssscs.sub_sub_sub_category_id = i.sub_sub_sub_category_id INNER JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id LEFT JOIN sub_sub_category_specifics sscs2 ON sscs2.specific_id = s2.specific_id LEFT JOIN sub_sub_sub_category_specifics ssscs2 ON ssscs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id OR s.specific_id = sscs.specific_id OR s.specific_id = ssscs.specific_id LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.item_id = i.item_id WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND ( (sv.value = 'Alarm Clock' AND s.specific_id = 15) AND (sv.value = 'New' AND s.specific_id = 19) ) GROUP BY i.item_id Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 28, 2015 Share Posted December 28, 2015 (edited) yeah thats the problem there are records there which match my conditions but just aren't showing No, there are no records that match that condition. It is impossible for a record to have a sv.value = 'Alarm Clock' AND sv.value='New'. If you dispute this, please provide a sample record with values that you think would match the condition. You need an OR operator on those last two pairs of condition. You are thinking like a human - not a machine. Let me provide an example: Let's take an example of a health insurance premium. Women live longer than men on average. So, an insurance rate might jump to a new lever at different ages for women than men. Let's say the highest rate starts for men at age 70, but for women that same rate doesn't kick in until age 74. As a human you might state that the highest rates apply to men that are 70 and above and to women that are 74 and above. So, you might be thinking that the condition for those people to be (gender='male' and age>=70) and (gender='female' and age>=74). but, that is incorrect, because no one person can be male and female (Bruce Jenner aside). The correct condition would be: (gender='male' and age>=70) OR (gender='female' and age>=74) So, either the person must be male and at least age 70 or the person must be female and at least 74. Edited December 28, 2015 by Psycho Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 28, 2015 Author Share Posted December 28, 2015 (edited) No, there are no records that match that condition. It is impossible for a record to have a sv.value = 'Alarm Clock' AND sv.value='New'. If you dispute this, please provide a sample record with values that you think would match the condition. You need an OR operator on those last two pairs of condition. You are thinking like a human - not a machine. Let me provide an example: Let's take an example of a health insurance premium. Women live longer than men on average. So, an insurance rate might jump to a new lever at different ages for women than men. Let's say the highest rate starts for men at age 70, but for women that same rate doesn't kick in until age 74. As a human you might state that the highest rates apply to men that are 70 and above and to women that are 74 and above. So, you might be thinking that the condition for those people to be (gender='male' and age>=70) and (gender='female' and age>=74). but, that is incorrect, because no one person can be male and female (Bruce Jenner aside). The correct condition would be: (gender='male' and age>=70) OR (gender='female' and age>=74) So, either the person must be male and at least age 70 or the person must be female and at least 74. haha....ok i understand well the results i have returned from the or statement isn't what i was after as its returning sv.values on alarm clock and new....and i want to select sv.values with both conditions only....if i can explain what is it i'm trying to do with this picture please... clock 1 has a sv.value of alarm clock and new, with s.specific_id 15 and 19 how am i able to get this item with those exact conditions? to only return clock 1...is it possible?...is the way I've created my relationship structure or table incorrect? sorry to be a pain but i'm struggling to do what it is im trying to do thank you for your patience Edited December 28, 2015 by Destramic Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 28, 2015 Share Posted December 28, 2015 Ok, that is something else entirely. You are not looking for ONE record that matches those two conditions. You are looking for a record which has TWO associated records that match each of those conditions. Barand might have a better suggestion, but I would JOIN the dependent records twice: once with the first conditions and again with the second conditions. But, looking at your query, I don't see how that can logically be done. You are JOINing the 's' table (a subquery) independently from the 'sv' table (specific_values). I don't see the specific relation ship between the values and specific_ids. Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 28, 2015 Share Posted December 28, 2015 (edited) How about we start at the beginning. Post an SQL dump of your DB so we can make sure your DB is correct. It appears everyone is replying to how YOU think the joins should be done. Give us the DB and the expected output and let us fill in the "How". On a quick glance, it doesn't look right to me. Edited December 28, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 29, 2015 Author Share Posted December 29, 2015 ok well i've stripper everything back to basics...here is the database dumb (no foreign keys ect etc) : http://pastebin.com/J3chVrQu this is how i designed my table relationship: lines on picture doesn't point to column that they relate to but you can gather from column name....each category has different specifics that specific having values linked to the item via the item_specific_values hope this a lot clearer for you guys now sql so far as we know: SELECT i.title FROM items i LEFT JOIN category_specifics cs ON cs.category_id = i.category_id LEFT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id INNER JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id LEFT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.item_id = i.item_id WHERE MATCH (i.title, i.description) AGAINST ('clock' IN BOOLEAN MODE) AND isv.specific_value_id = sv.specific_value_id AND cs.category_id = 1 AND scs.sub_category_id = 4 AND ( (sv.value = 'Alarm Clock' AND s.specific_id = 15) OR (sv.value = 'New' AND s.specific_id = 19) ) GROUP BY i.item_id what im trying to achieve is to retrieve an item by adding multiple conditions to the specific value and specific id please ask if you need any more information....thank for your help much appreciated Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 29, 2015 Share Posted December 29, 2015 (edited) I am going to give a minute for others to chime in, but this DB Schema is just no good. Off hand, a category is a category is a category, just that some have parent categories and others dont. You also have some timestamp issues. `sub_category_id` INT(11) NOT NULL, `sub_sub_category_id` INT(11) DEFAULT NULL, `sub_sub_sub_category_id` INT(11) DEFAULT NULL, Really? Sub sub sub? enum('Town/City','County','Country','Continent','MENA Region','Worldwide') Enum??? You only have one table that has any foreign keys. Not sure if you stripped them out or not. This is just really bad. But dont worry, it will get straightened out on this forum. * I think it would be helpful for you to google shopping cart DB schemas in the images tab. Edited December 29, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted December 29, 2015 Share Posted December 29, 2015 Strange that your sub-categories do not belong to any category in that diagram Quote Link to comment Share on other sites More sharing options...
Barand Posted December 29, 2015 Share Posted December 29, 2015 All items have the same category and subcategory in that dump. What results are you expecting from the data you provided? Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 29, 2015 Author Share Posted December 29, 2015 (edited) I am going to give a minute for others to chime in, but this DB Schema is just no good. Off hand, a category is a category is a category, just that some have parent categories and others dont. You also have some timestamp issues. `sub_category_id` INT(11) NOT NULL, `sub_sub_category_id` INT(11) DEFAULT NULL, `sub_sub_sub_category_id` INT(11) DEFAULT NULL, Really? Sub sub sub? enum('Town/City','County','Country','Continent','MENA Region','Worldwide') Enum??? You only have one table that has any foreign keys. Not sure if you stripped them out or not. This is just really bad. But dont worry, it will get straightened out on this forum. * I think it would be helpful for you to google shopping cart DB schemas in the images tab. yeah i know the database schema isnt good but the one you have is stripped bare just to get the query can data that i want...no forgien keys, index etc....even stripped data as cats, sub cats have thousands of entries. All items have the same category and subcategory in that dump. What results are you expecting from the data you provided? as show here it displays what specific values are given to an item and the specific id's http://s30.postimg.org/v1y8370m9/Untitled_2.gif for instance i'd like to create a query where i can select any items where the sv.value = 'Alarm Clock' AND s.specific_id = 15 as well as the sv.value = 'New' AND s.specific_id = 19 retrieving clock 1 or any of them that match the specific values and id's hope you can help and understand what it is im trying to do Edited December 29, 2015 by Destramic Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 29, 2015 Share Posted December 29, 2015 Just like building a house or a building, you need to have a solid foundation before you hammer the first nail. The place to start right now is getting your database correct and then building upon that otherwise you are just going to have problems down the road Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 29, 2015 Author Share Posted December 29, 2015 (edited) Just like building a house or a building, you need to have a solid foundation before you hammer the first nail. The place to start right now is getting your database correct and then building upon that otherwise you are just going to have problems down the road i understand that like i said i stripped tables before dumping...i still believe there is a away for me to do this...i come across the IN() function which seems to be what i need i used it in my query like so: SELECT DISTINCT(i.item_id), i.title FROM items i RIGHT JOIN category_specifics cs ON cs.category_id = i.category_id RIGHT JOIN sub_category_specifics scs ON scs.sub_category_id = i.sub_category_id RIGHT JOIN (SELECT s2.specific_id FROM specifics s2 LEFT JOIN category_specifics cs2 ON cs2.specific_id = s2.specific_id LEFT JOIN sub_category_specifics scs2 ON scs2.specific_id = s2.specific_id ) s ON s.specific_id = cs.specific_id OR s.specific_id = scs.specific_id RIGHT JOIN specific_values sv ON sv.specific_id = s.specific_id RIGHT JOIN item_specific_values isv ON isv.specific_value_id = sv.specific_value_id WHERE MATCH (i.title) AGAINST ('clock' IN BOOLEAN MODE) AND isv.item_id = i.item_id AND (sv.value IN ('Alarm Clock', 'New') AND sv.specific_id IN (15, 19)) GROUP BY isv.specific_value_id clock 1 AND (sv.value IN ('Alarm Clock', 'New') AND sv.specific_id IN (15, 19)) clock 2 AND (sv.value IN ('Wall Clock', 'Used') AND sv.specific_id IN (15, 19)) when searching for specifics match clock 3 - clock 3 and clock 1 show AND (sv.value IN ('Cuckoo Clock', 'New') AND sv.specific_id IN (15, 19)) doesn't return clock 4 as it should AND (sv.value IN ('Alarm Clock', 'Used') AND sv.specific_id IN (15, 19)) doesn't return clock 5 as it should AND (sv.value IN ('Wall Clock', 'Used') AND sv.specific_id IN (15, 19)) please tell me i'm on to something here? Edited December 29, 2015 by Destramic Quote Link to comment Share on other sites More sharing options...
Solution benanamen Posted December 29, 2015 Solution Share Posted December 29, 2015 Why do you insist on trying to get a bad design to work? Stripped or not, what you have is simply no good. Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 29, 2015 Author Share Posted December 29, 2015 ok well i back to the drawing board it is...any suggestions are welcome thank you all Quote Link to comment Share on other sites More sharing options...
benanamen Posted December 29, 2015 Share Posted December 29, 2015 For the best feedback, detail out about the site and the functionality desired and how it should work. Based on your posts, it appears to be some sort of auction application. Quote Link to comment Share on other sites More sharing options...
Destramic Posted December 30, 2015 Author Share Posted December 30, 2015 (edited) For the best feedback, detail out about the site and the functionality desired and how it should work. Based on your posts, it appears to be some sort of auction application. yeah my plan is to design a auction application...i apologizes cause after re-assessing my thinking and design (thanks to you guys, sorry!)...what i was trying to do wasn't what i really needed. what i was trying to achieve is when a user adds an item, the user will add item attributes (specifics) for that item...ie. manufacturer, color, etc... so...i made a table called item_attributes which contains all attributes of the item specified by the selling user. item_attributes -------------------------- item_attribute_id item_id attribute value ----------------------- and with a simple query like so i was able to get the items i want by the attributes i want... SELECT i.title FROM items i RIGHT JOIN (SELECT item_id FROM item_attributes WHERE attribute IN ('Clock Type') AND value IN ('Wall Clock')) ai ON ai.item_id = i.item_id RIGHT JOIN (SELECT item_id FROM item_attributes WHERE attribute IN ('Condition') AND value IN ('New')) ai2 ON ai2.item_id = ai.item_id IN not really needed as i could just use WHERE the specifications i be banging on about throughout this painful thread is so i can add attributes (specifications) to certain categories, sub categories etc.. so when the user comes to the add item page the specifications will appear depending on categories selected....when they submit item and attributes it all gets added to the database what i've done here seems logical? thank you for your time and patience 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) Glad your getting some direction, but your latest query is still not right. Your item to attributes should be joined on the item id in both tables, not this "right join select" you have going on. You have THREE querys. You only need ONE. And like I already told you, there are no "Sub Categorys". Everything is a category, Each category is either a parent or a child of another category. Edited December 30, 2015 by benanamen 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.