mutliple conditions for same join for same column

[Destramic]

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

[benanamen]

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

[Psycho]

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

[benanamen]

Ahh yes, I had that little brain tickle that something was slightly amiss.

[Psycho]

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.

[Barand]

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.

[Destramic]

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 =/

[Barand]

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!

[Destramic]

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

[Psycho]

 

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

[Destramic]

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

[Psycho]

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.

[benanamen]

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

[Destramic]

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

[benanamen]

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.

 

1.  `sub_category_id` INT(11) NOT NULL,
2.   `sub_sub_category_id` INT(11) DEFAULT NULL,
3.   `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

[Barand]

Strange that your sub-categories do not belong to any category in that diagram

[Barand]

All items have the same category and subcategory in that dump. What results are you expecting from the data you provided?

[Destramic]

 

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.

 

1.  `sub_category_id` INT(11) NOT NULL,
2.   `sub_sub_category_id` INT(11) DEFAULT NULL,
3.   `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

[benanamen]

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

[Destramic]

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

[benanamen]

Why do you insist on trying to get a bad design to work? Stripped or not, what you have is simply no good.

[Destramic]

ok well i back to the drawing board it is...any suggestions are welcome

 

thank you all

[benanamen]

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.

[Destramic]

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

[benanamen]

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