How to organize my query with so many ANDs
up vote
0
down vote
favorite
My query looks like:
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id=10
AND cpa.attribute_value_id=36
AND cpa.attribute_id=2
AND cpa.attribute_value_id=5
AND cpa.attribute_id=7
AND cpa.attribute_value_id=31
AND cpa.attribute_id=9
AND cpa.attribute_value_id=28
AND cpa.attribute_id=8
AND cpa.attribute_value_id=25
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
In simple words - each of the AND condtitions evaluate to true. If I execute them one by one it is OK. But when I try to execute it like what I posted above - no results are returned. I am sure am not doing right the multiple AND conditions part. The ct_product_attribute table:
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | int(11) | YES | MUL | NULL | |
| attribute_set_id | int(11) | YES | MUL | NULL | |
| attribute_id | int(11) | YES | MUL | NULL | |
| attribute_value_id | int(11) | YES | MUL | NULL | |
| value | varchar(255) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
Will post the other tables if needed. Just trying to not flood the post. Thank you!
EDIT
In ct_product I got products like ( just for example ):
id
1
2
3
In ct_product_attribute each product can have more than one attribute-attr.value pairs. Some of the pairs are same.( will show only the columns that I need )
id product_id attribute_id attribute_value_id
1 1 1 1
2 2 1 1
3 1 2 1
4 2 3 1
5 3 1 1
6 3 2 1
The values that I get from the request are:
attribute_id=1
attribute_value_id=1
attribute_id=2
attribute_value_id=1
And now I have to retrieve only the product with id=1. If I use OR it is retrieving both products id=1 and id=2. Not sure if it gets more clear now.
mysql
asked Nov 21 at 7:26
Toma Tomov
556216
|
show 4 more comments
up vote
0
down vote
favorite
My query looks like:
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id=10
AND cpa.attribute_value_id=36
AND cpa.attribute_id=2
AND cpa.attribute_value_id=5
AND cpa.attribute_id=7
AND cpa.attribute_value_id=31
AND cpa.attribute_id=9
AND cpa.attribute_value_id=28
AND cpa.attribute_id=8
AND cpa.attribute_value_id=25
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
In simple words - each of the AND condtitions evaluate to true. If I execute them one by one it is OK. But when I try to execute it like what I posted above - no results are returned. I am sure am not doing right the multiple AND conditions part. The ct_product_attribute table:
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | int(11) | YES | MUL | NULL | |
| attribute_set_id | int(11) | YES | MUL | NULL | |
| attribute_id | int(11) | YES | MUL | NULL | |
| attribute_value_id | int(11) | YES | MUL | NULL | |
| value | varchar(255) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
Will post the other tables if needed. Just trying to not flood the post. Thank you!
EDIT
In ct_product I got products like ( just for example ):
id
1
2
3
In ct_product_attribute each product can have more than one attribute-attr.value pairs. Some of the pairs are same.( will show only the columns that I need )
id product_id attribute_id attribute_value_id
1 1 1 1
2 2 1 1
3 1 2 1
4 2 3 1
5 3 1 1
6 3 2 1
The values that I get from the request are:
attribute_id=1
attribute_value_id=1
attribute_id=2
attribute_value_id=1
And now I have to retrieve only the product with id=1. If I use OR it is retrieving both products id=1 and id=2. Not sure if it gets more clear now.
mysql
asked Nov 21 at 7:26
Toma Tomov
556216
4
What your query is trying to do is to have attribute_id 2 at the same time as it has 7 and 9 and 8 and well - no row will ever match that logic. I'm not exactly sure what you're trying to retrieve but this looks like you'll need to utilize OR based logic or possible an IN subquery.
– Allan S. Hansen
Nov 21 at 7:29
1
Isn't it obvious that a column within a single row cannot have multiple values? What do you want to achieve with this query?
– Nico Haase
Nov 21 at 7:29
1
You're runningcpa.attribute_id=10on one line, and then two lines later runningAND cpa.attribute_id=2. This is a shortened version of what you're essentially trying to do:cpa.attribute_id=10 AND cpa.attribute_id=2This will always return false, because a column can't be two values at once.
– Davіd
Nov 21 at 7:29
1
Are you getting any results from this query ? You are basically asking a single field to have multiple values simultaneously, which is not possible
– Madhur Bhaiya
Nov 21 at 7:29
1
I think you need to use ORs amongst your ANDs eg WHERE (cpa.attribute_id=10 AND cpa.attribute_value_id=36) OR (cpa.attribute_id=2 AND cpa.attribute_value_id=5)
– Jonathan Willcock
Nov 21 at 7:31
|
show 4 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
My query looks like:
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id=10
AND cpa.attribute_value_id=36
AND cpa.attribute_id=2
AND cpa.attribute_value_id=5
AND cpa.attribute_id=7
AND cpa.attribute_value_id=31
AND cpa.attribute_id=9
AND cpa.attribute_value_id=28
AND cpa.attribute_id=8
AND cpa.attribute_value_id=25
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
In simple words - each of the AND condtitions evaluate to true. If I execute them one by one it is OK. But when I try to execute it like what I posted above - no results are returned. I am sure am not doing right the multiple AND conditions part. The ct_product_attribute table:
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | int(11) | YES | MUL | NULL | |
| attribute_set_id | int(11) | YES | MUL | NULL | |
| attribute_id | int(11) | YES | MUL | NULL | |
| attribute_value_id | int(11) | YES | MUL | NULL | |
| value | varchar(255) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
Will post the other tables if needed. Just trying to not flood the post. Thank you!
EDIT
In ct_product I got products like ( just for example ):
id
1
2
3
In ct_product_attribute each product can have more than one attribute-attr.value pairs. Some of the pairs are same.( will show only the columns that I need )
id product_id attribute_id attribute_value_id
1 1 1 1
2 2 1 1
3 1 2 1
4 2 3 1
5 3 1 1
6 3 2 1
The values that I get from the request are:
attribute_id=1
attribute_value_id=1
attribute_id=2
attribute_value_id=1
And now I have to retrieve only the product with id=1. If I use OR it is retrieving both products id=1 and id=2. Not sure if it gets more clear now.
mysql
asked Nov 21 at 7:26
Toma Tomov
556216
My query looks like:
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id=10
AND cpa.attribute_value_id=36
AND cpa.attribute_id=2
AND cpa.attribute_value_id=5
AND cpa.attribute_id=7
AND cpa.attribute_value_id=31
AND cpa.attribute_id=9
AND cpa.attribute_value_id=28
AND cpa.attribute_id=8
AND cpa.attribute_value_id=25
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
In simple words - each of the AND condtitions evaluate to true. If I execute them one by one it is OK. But when I try to execute it like what I posted above - no results are returned. I am sure am not doing right the multiple AND conditions part. The ct_product_attribute table:
+--------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_id | int(11) | YES | MUL | NULL | |
| attribute_set_id | int(11) | YES | MUL | NULL | |
| attribute_id | int(11) | YES | MUL | NULL | |
| attribute_value_id | int(11) | YES | MUL | NULL | |
| value | varchar(255) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+----------------+
Will post the other tables if needed. Just trying to not flood the post. Thank you!
EDIT
In ct_product I got products like ( just for example ):
id
1
2
3
In ct_product_attribute each product can have more than one attribute-attr.value pairs. Some of the pairs are same.( will show only the columns that I need )
id product_id attribute_id attribute_value_id
1 1 1 1
2 2 1 1
3 1 2 1
4 2 3 1
5 3 1 1
6 3 2 1
The values that I get from the request are:
attribute_id=1
attribute_value_id=1
attribute_id=2
attribute_value_id=1
And now I have to retrieve only the product with id=1. If I use OR it is retrieving both products id=1 and id=2. Not sure if it gets more clear now.
mysql
mysql
asked Nov 21 at 7:26
Toma Tomov
556216
asked Nov 21 at 7:26
Toma Tomov
556216
edited Nov 21 at 7:50
asked Nov 21 at 7:26
Toma Tomov
556216
asked Nov 21 at 7:26
Toma Tomov
556216
asked Nov 21 at 7:26
Toma Tomov
556216
556216
4
What your query is trying to do is to have attribute_id 2 at the same time as it has 7 and 9 and 8 and well - no row will ever match that logic. I'm not exactly sure what you're trying to retrieve but this looks like you'll need to utilize OR based logic or possible an IN subquery.
– Allan S. Hansen
Nov 21 at 7:29
1
Isn't it obvious that a column within a single row cannot have multiple values? What do you want to achieve with this query?
– Nico Haase
Nov 21 at 7:29
1
You're runningcpa.attribute_id=10on one line, and then two lines later runningAND cpa.attribute_id=2. This is a shortened version of what you're essentially trying to do:cpa.attribute_id=10 AND cpa.attribute_id=2This will always return false, because a column can't be two values at once.
– Davіd
Nov 21 at 7:29
1
Are you getting any results from this query ? You are basically asking a single field to have multiple values simultaneously, which is not possible
– Madhur Bhaiya
Nov 21 at 7:29
1
I think you need to use ORs amongst your ANDs eg WHERE (cpa.attribute_id=10 AND cpa.attribute_value_id=36) OR (cpa.attribute_id=2 AND cpa.attribute_value_id=5)
– Jonathan Willcock
Nov 21 at 7:31
|
show 4 more comments
4
What your query is trying to do is to have attribute_id 2 at the same time as it has 7 and 9 and 8 and well - no row will ever match that logic. I'm not exactly sure what you're trying to retrieve but this looks like you'll need to utilize OR based logic or possible an IN subquery.
– Allan S. Hansen
Nov 21 at 7:29
1
Isn't it obvious that a column within a single row cannot have multiple values? What do you want to achieve with this query?
– Nico Haase
Nov 21 at 7:29
1
You're runningcpa.attribute_id=10on one line, and then two lines later runningAND cpa.attribute_id=2. This is a shortened version of what you're essentially trying to do:cpa.attribute_id=10 AND cpa.attribute_id=2This will always return false, because a column can't be two values at once.
– Davіd
Nov 21 at 7:29
1
Are you getting any results from this query ? You are basically asking a single field to have multiple values simultaneously, which is not possible
– Madhur Bhaiya
Nov 21 at 7:29
1
I think you need to use ORs amongst your ANDs eg WHERE (cpa.attribute_id=10 AND cpa.attribute_value_id=36) OR (cpa.attribute_id=2 AND cpa.attribute_value_id=5)
– Jonathan Willcock
Nov 21 at 7:31
4
4
What your query is trying to do is to have attribute_id 2 at the same time as it has 7 and 9 and 8 and well - no row will ever match that logic. I'm not exactly sure what you're trying to retrieve but this looks like you'll need to utilize OR based logic or possible an IN subquery.
– Allan S. Hansen
Nov 21 at 7:29
What your query is trying to do is to have attribute_id 2 at the same time as it has 7 and 9 and 8 and well - no row will ever match that logic. I'm not exactly sure what you're trying to retrieve but this looks like you'll need to utilize OR based logic or possible an IN subquery.
– Allan S. Hansen
Nov 21 at 7:29
1
1
Isn't it obvious that a column within a single row cannot have multiple values? What do you want to achieve with this query?
– Nico Haase
Nov 21 at 7:29
Isn't it obvious that a column within a single row cannot have multiple values? What do you want to achieve with this query?
– Nico Haase
Nov 21 at 7:29
1
1
You're running
cpa.attribute_id=10 on one line, and then two lines later running AND cpa.attribute_id=2. This is a shortened version of what you're essentially trying to do: cpa.attribute_id=10 AND cpa.attribute_id=2 This will always return false, because a column can't be two values at once.– Davіd
Nov 21 at 7:29
You're running
cpa.attribute_id=10 on one line, and then two lines later running AND cpa.attribute_id=2. This is a shortened version of what you're essentially trying to do: cpa.attribute_id=10 AND cpa.attribute_id=2 This will always return false, because a column can't be two values at once.– Davіd
Nov 21 at 7:29
1
1
Are you getting any results from this query ? You are basically asking a single field to have multiple values simultaneously, which is not possible
– Madhur Bhaiya
Nov 21 at 7:29
Are you getting any results from this query ? You are basically asking a single field to have multiple values simultaneously, which is not possible
– Madhur Bhaiya
Nov 21 at 7:29
1
1
I think you need to use ORs amongst your ANDs eg WHERE (cpa.attribute_id=10 AND cpa.attribute_value_id=36) OR (cpa.attribute_id=2 AND cpa.attribute_value_id=5)
– Jonathan Willcock
Nov 21 at 7:31
I think you need to use ORs amongst your ANDs eg WHERE (cpa.attribute_id=10 AND cpa.attribute_value_id=36) OR (cpa.attribute_id=2 AND cpa.attribute_value_id=5)
– Jonathan Willcock
Nov 21 at 7:31
|
show 4 more comments
3 Answers
3
active
oldest
votes
up vote
3
down vote
I'm pretty sure those are supposed to be ORs because you can't have all those IDs at the same time. With that in mind, you should be able to use IN.
WHERE cpa.attribute_id IN (10,2,7,9,8)
AND cpa.attribute_value_id IN (36,5,31,28,25)
answered Nov 21 at 7:33
Snake14
28917
add a comment |
up vote
1
down vote
I really don't know what you are trying to accomplish but you should/could use WHERE IN, as everyone pointed in the comments you are looking for a field with multiple values...
But, as for the AND question, you could/should use IN, as in;
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id IN (10, 2, 7, 9, 8)
AND cpa.attribute_value_id IN (36, 5, 31, 28, 25)
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:33
Erubiel
1,790318
add a comment |
up vote
1
down vote
You can try using (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25))
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25)) and `ct_product`.`id`=1
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:36
fa06
9,1351917
I have updated the question with what I am trying to achieve. Hope it gets little more clear now.
– Toma Tomov
Nov 21 at 7:53
@TomaTomov, updated my answer - you can check - you need to specifyct_product.id=1 in where clause
– fa06
Nov 21 at 8:01
That is the problem. I don't know the ID. I need to retrieve it. Guess I am in some total mess. Maybe you are right with OR and IN conditions. Seems my logic is pretty wrong :/
– Toma Tomov
Nov 21 at 8:03
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
I'm pretty sure those are supposed to be ORs because you can't have all those IDs at the same time. With that in mind, you should be able to use IN.
WHERE cpa.attribute_id IN (10,2,7,9,8)
AND cpa.attribute_value_id IN (36,5,31,28,25)
answered Nov 21 at 7:33
Snake14
28917
add a comment |
up vote
3
down vote
I'm pretty sure those are supposed to be ORs because you can't have all those IDs at the same time. With that in mind, you should be able to use IN.
WHERE cpa.attribute_id IN (10,2,7,9,8)
AND cpa.attribute_value_id IN (36,5,31,28,25)
answered Nov 21 at 7:33
Snake14
28917
add a comment |
up vote
3
down vote
up vote
3
down vote
I'm pretty sure those are supposed to be ORs because you can't have all those IDs at the same time. With that in mind, you should be able to use IN.
WHERE cpa.attribute_id IN (10,2,7,9,8)
AND cpa.attribute_value_id IN (36,5,31,28,25)
answered Nov 21 at 7:33
Snake14
28917
I'm pretty sure those are supposed to be ORs because you can't have all those IDs at the same time. With that in mind, you should be able to use IN.
WHERE cpa.attribute_id IN (10,2,7,9,8)
AND cpa.attribute_value_id IN (36,5,31,28,25)
answered Nov 21 at 7:33
Snake14
28917
answered Nov 21 at 7:33
Snake14
28917
answered Nov 21 at 7:33
Snake14
28917
answered Nov 21 at 7:33
Snake14
28917
28917
add a comment |
add a comment |
up vote
1
down vote
I really don't know what you are trying to accomplish but you should/could use WHERE IN, as everyone pointed in the comments you are looking for a field with multiple values...
But, as for the AND question, you could/should use IN, as in;
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id IN (10, 2, 7, 9, 8)
AND cpa.attribute_value_id IN (36, 5, 31, 28, 25)
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:33
Erubiel
1,790318
add a comment |
up vote
1
down vote
I really don't know what you are trying to accomplish but you should/could use WHERE IN, as everyone pointed in the comments you are looking for a field with multiple values...
But, as for the AND question, you could/should use IN, as in;
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id IN (10, 2, 7, 9, 8)
AND cpa.attribute_value_id IN (36, 5, 31, 28, 25)
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:33
Erubiel
1,790318
add a comment |
up vote
1
down vote
up vote
1
down vote
I really don't know what you are trying to accomplish but you should/could use WHERE IN, as everyone pointed in the comments you are looking for a field with multiple values...
But, as for the AND question, you could/should use IN, as in;
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id IN (10, 2, 7, 9, 8)
AND cpa.attribute_value_id IN (36, 5, 31, 28, 25)
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:33
Erubiel
1,790318
I really don't know what you are trying to accomplish but you should/could use WHERE IN, as everyone pointed in the comments you are looking for a field with multiple values...
But, as for the AND question, you could/should use IN, as in;
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE cpa.attribute_id IN (10, 2, 7, 9, 8)
AND cpa.attribute_value_id IN (36, 5, 31, 28, 25)
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:33
Erubiel
1,790318
answered Nov 21 at 7:33
Erubiel
1,790318
answered Nov 21 at 7:33
Erubiel
1,790318
answered Nov 21 at 7:33
Erubiel
1,790318
1,790318
add a comment |
add a comment |
up vote
1
down vote
You can try using (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25))
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25)) and `ct_product`.`id`=1
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:36
fa06
9,1351917
I have updated the question with what I am trying to achieve. Hope it gets little more clear now.
– Toma Tomov
Nov 21 at 7:53
@TomaTomov, updated my answer - you can check - you need to specifyct_product.id=1 in where clause
– fa06
Nov 21 at 8:01
That is the problem. I don't know the ID. I need to retrieve it. Guess I am in some total mess. Maybe you are right with OR and IN conditions. Seems my logic is pretty wrong :/
– Toma Tomov
Nov 21 at 8:03
add a comment |
up vote
1
down vote
You can try using (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25))
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25)) and `ct_product`.`id`=1
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:36
fa06
9,1351917
I have updated the question with what I am trying to achieve. Hope it gets little more clear now.
– Toma Tomov
Nov 21 at 7:53
@TomaTomov, updated my answer - you can check - you need to specifyct_product.id=1 in where clause
– fa06
Nov 21 at 8:01
That is the problem. I don't know the ID. I need to retrieve it. Guess I am in some total mess. Maybe you are right with OR and IN conditions. Seems my logic is pretty wrong :/
– Toma Tomov
Nov 21 at 8:03
add a comment |
up vote
1
down vote
up vote
1
down vote
You can try using (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25))
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25)) and `ct_product`.`id`=1
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:36
fa06
9,1351917
You can try using (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25))
SELECT SUM(ct_product_store_quantity.quantity) as quantity, `ct_product`.*
FROM `ct_product`
LEFT JOIN `ct_productLang` ON `ct_product`.`id` = `ct_productLang`.`product_id`
LEFT JOIN `ct_product_store_quantity` ON `ct_product`.`id` = `ct_product_store_quantity`.`product_id`
LEFT JOIN `ct_product_attribute` as cpa ON ct_product.id=cpa.product_id
WHERE (cpa.attribute_id,cpa.attribute_value_id) in ((10,36),(2,5),(7,31),(9,28),(8,25)) and `ct_product`.`id`=1
GROUP BY `ct_product`.`id`
HAVING quantity > 0
ORDER BY `id` DESC
answered Nov 21 at 7:36
fa06
9,1351917
edited Nov 21 at 8:00
answered Nov 21 at 7:36
fa06
9,1351917
answered Nov 21 at 7:36
fa06
9,1351917
answered Nov 21 at 7:36
fa06
9,1351917
9,1351917
I have updated the question with what I am trying to achieve. Hope it gets little more clear now.
– Toma Tomov
Nov 21 at 7:53
@TomaTomov, updated my answer - you can check - you need to specifyct_product.id=1 in where clause
– fa06
Nov 21 at 8:01
That is the problem. I don't know the ID. I need to retrieve it. Guess I am in some total mess. Maybe you are right with OR and IN conditions. Seems my logic is pretty wrong :/
– Toma Tomov
Nov 21 at 8:03
add a comment |
I have updated the question with what I am trying to achieve. Hope it gets little more clear now.
– Toma Tomov
Nov 21 at 7:53
@TomaTomov, updated my answer - you can check - you need to specifyct_product.id=1 in where clause
– fa06
Nov 21 at 8:01
That is the problem. I don't know the ID. I need to retrieve it. Guess I am in some total mess. Maybe you are right with OR and IN conditions. Seems my logic is pretty wrong :/
– Toma Tomov
Nov 21 at 8:03
I have updated the question with what I am trying to achieve. Hope it gets little more clear now.
– Toma Tomov
Nov 21 at 7:53
I have updated the question with what I am trying to achieve. Hope it gets little more clear now.
– Toma Tomov
Nov 21 at 7:53
@TomaTomov, updated my answer - you can check - you need to specify
ct_product.id=1 in where clause– fa06
Nov 21 at 8:01
@TomaTomov, updated my answer - you can check - you need to specify
ct_product.id=1 in where clause– fa06
Nov 21 at 8:01
That is the problem. I don't know the ID. I need to retrieve it. Guess I am in some total mess. Maybe you are right with OR and IN conditions. Seems my logic is pretty wrong :/
– Toma Tomov
Nov 21 at 8:03
That is the problem. I don't know the ID. I need to retrieve it. Guess I am in some total mess. Maybe you are right with OR and IN conditions. Seems my logic is pretty wrong :/
– Toma Tomov
Nov 21 at 8:03
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53407109%2fhow-to-organize-my-query-with-so-many-ands%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
What your query is trying to do is to have attribute_id 2 at the same time as it has 7 and 9 and 8 and well - no row will ever match that logic. I'm not exactly sure what you're trying to retrieve but this looks like you'll need to utilize OR based logic or possible an IN subquery.
– Allan S. Hansen
Nov 21 at 7:29
1
Isn't it obvious that a column within a single row cannot have multiple values? What do you want to achieve with this query?
– Nico Haase
Nov 21 at 7:29
1
You're running
cpa.attribute_id=10on one line, and then two lines later runningAND cpa.attribute_id=2. This is a shortened version of what you're essentially trying to do:cpa.attribute_id=10 AND cpa.attribute_id=2This will always return false, because a column can't be two values at once.– Davіd
Nov 21 at 7:29
1
Are you getting any results from this query ? You are basically asking a single field to have multiple values simultaneously, which is not possible
– Madhur Bhaiya
Nov 21 at 7:29
1
I think you need to use ORs amongst your ANDs eg WHERE (cpa.attribute_id=10 AND cpa.attribute_value_id=36) OR (cpa.attribute_id=2 AND cpa.attribute_value_id=5)
– Jonathan Willcock
Nov 21 at 7:31