Mixing
How can i make this mysql query ? If possible at all
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have this table
itemname property value
------------------------------------------
A size big
A color red
B size big
B color blue
C size small
C color blue
D size small
D color blue
E size small
E color blue
Im creating a list like this: SELECT property,value,COUNT(itemname),GROUP_CONCAT(itemname) FROM table GROUP BY property,value
property value count
---------------------------------
size big 2 (A,B)
size small 3 (C,D,E)
color red 1 (A)
color blue 4 (B,C,D,E)
I want to filter the items which are BIG && SMALL && BLUE, how can i achieve this result ? (I MUST be specific about the property when addressing a value for filtering)
`SELECT
property,value,COUNT(itemname),GROUP_CONCAT(itemname)
FROM
table
GROUP BY
property,value
HAVING
( property IN ('size') && value IN ('big','small') )
&&
( property IN ('color') && value IN ('blue') )`
But this has no result, because it tries to match the row with size and color at the same time ? My desired output in this case is to avoid the item A because it is red, like this:
property value count
---------------------------------
size big 1 (B) A not here, because it is red
size small 3 (C,D,E) no change because all are blue
color red 0 (A) no red is selected, so this row should be 0 or not listed at all
color blue 4 (B,C,D,E) all the 4 are big or small and blue
Please someone help me out in this, i lost 2days wondering on the solution.
I might use CASE combined with HAVING ? Or i should address the WHERE instead somehow ?
Note: This table is actually not real, but if this question can be solved i can use it in my real tables which are a lot more complicated.
mysqli
asked Jan 3 at 12:52
Ninet9Ninet9
3817
|
show 2 more comments
I have this table
itemname property value
------------------------------------------
A size big
A color red
B size big
B color blue
C size small
C color blue
D size small
D color blue
E size small
E color blue
Im creating a list like this: SELECT property,value,COUNT(itemname),GROUP_CONCAT(itemname) FROM table GROUP BY property,value
property value count
---------------------------------
size big 2 (A,B)
size small 3 (C,D,E)
color red 1 (A)
color blue 4 (B,C,D,E)
I want to filter the items which are BIG && SMALL && BLUE, how can i achieve this result ? (I MUST be specific about the property when addressing a value for filtering)
`SELECT
property,value,COUNT(itemname),GROUP_CONCAT(itemname)
FROM
table
GROUP BY
property,value
HAVING
( property IN ('size') && value IN ('big','small') )
&&
( property IN ('color') && value IN ('blue') )`
But this has no result, because it tries to match the row with size and color at the same time ? My desired output in this case is to avoid the item A because it is red, like this:
property value count
---------------------------------
size big 1 (B) A not here, because it is red
size small 3 (C,D,E) no change because all are blue
color red 0 (A) no red is selected, so this row should be 0 or not listed at all
color blue 4 (B,C,D,E) all the 4 are big or small and blue
Please someone help me out in this, i lost 2days wondering on the solution.
I might use CASE combined with HAVING ? Or i should address the WHERE instead somehow ?
Note: This table is actually not real, but if this question can be solved i can use it in my real tables which are a lot more complicated.
mysqli
asked Jan 3 at 12:52
Ninet9Ninet9
3817
itemnameshould not be duplicated and fieldpropertyshould be columns.
– Martin Zeitler
Jan 3 at 12:58
what do you mean ? property is a column already. By the way as i said my goal is not to reorganize the table, just to find a solution to this problem, so i can use it in my real tables.
– Ninet9
Jan 3 at 13:01
if the idea is not to normalize the data; you will have to suffer from the consequences. the way how the data is represented might suit noSQL, but not really SQL. the way of data-modelling is different there.
– Martin Zeitler
Jan 3 at 13:01
my real tables like this: ITEMS, PROPERTIES (size,color,and a hundred more), PROPERTYVALUES (black,big,10mm,medium,short,tall etc..) and PROPERTYCONNECTIONS where i connect the item id's to the properties and the values.
– Ninet9
Jan 3 at 13:03
makesizeandcolorcolumns; then it would be something alikeSELECT *, COUNT(itemname) AS amount FROM table WHERE color="blue" AND size IN("big", "small");(with optinal grouping) if one cannot run a query like this, one can assume the structure is sub-optimal. see studytonight.com/dbms/database-normalization.php
– Martin Zeitler
Jan 3 at 13:09
|
show 2 more comments
I have this table
itemname property value
------------------------------------------
A size big
A color red
B size big
B color blue
C size small
C color blue
D size small
D color blue
E size small
E color blue
Im creating a list like this: SELECT property,value,COUNT(itemname),GROUP_CONCAT(itemname) FROM table GROUP BY property,value
property value count
---------------------------------
size big 2 (A,B)
size small 3 (C,D,E)
color red 1 (A)
color blue 4 (B,C,D,E)
I want to filter the items which are BIG && SMALL && BLUE, how can i achieve this result ? (I MUST be specific about the property when addressing a value for filtering)
`SELECT
property,value,COUNT(itemname),GROUP_CONCAT(itemname)
FROM
table
GROUP BY
property,value
HAVING
( property IN ('size') && value IN ('big','small') )
&&
( property IN ('color') && value IN ('blue') )`
But this has no result, because it tries to match the row with size and color at the same time ? My desired output in this case is to avoid the item A because it is red, like this:
property value count
---------------------------------
size big 1 (B) A not here, because it is red
size small 3 (C,D,E) no change because all are blue
color red 0 (A) no red is selected, so this row should be 0 or not listed at all
color blue 4 (B,C,D,E) all the 4 are big or small and blue
Please someone help me out in this, i lost 2days wondering on the solution.
I might use CASE combined with HAVING ? Or i should address the WHERE instead somehow ?
Note: This table is actually not real, but if this question can be solved i can use it in my real tables which are a lot more complicated.
mysqli
asked Jan 3 at 12:52
Ninet9Ninet9
3817
I have this table
itemname property value
------------------------------------------
A size big
A color red
B size big
B color blue
C size small
C color blue
D size small
D color blue
E size small
E color blue
Im creating a list like this: SELECT property,value,COUNT(itemname),GROUP_CONCAT(itemname) FROM table GROUP BY property,value
property value count
---------------------------------
size big 2 (A,B)
size small 3 (C,D,E)
color red 1 (A)
color blue 4 (B,C,D,E)
I want to filter the items which are BIG && SMALL && BLUE, how can i achieve this result ? (I MUST be specific about the property when addressing a value for filtering)
`SELECT
property,value,COUNT(itemname),GROUP_CONCAT(itemname)
FROM
table
GROUP BY
property,value
HAVING
( property IN ('size') && value IN ('big','small') )
&&
( property IN ('color') && value IN ('blue') )`
But this has no result, because it tries to match the row with size and color at the same time ? My desired output in this case is to avoid the item A because it is red, like this:
property value count
---------------------------------
size big 1 (B) A not here, because it is red
size small 3 (C,D,E) no change because all are blue
color red 0 (A) no red is selected, so this row should be 0 or not listed at all
color blue 4 (B,C,D,E) all the 4 are big or small and blue
Please someone help me out in this, i lost 2days wondering on the solution.
I might use CASE combined with HAVING ? Or i should address the WHERE instead somehow ?
Note: This table is actually not real, but if this question can be solved i can use it in my real tables which are a lot more complicated.
mysqli
mysqli
asked Jan 3 at 12:52
Ninet9Ninet9
3817
asked Jan 3 at 12:52
Ninet9Ninet9
3817
edited Jan 3 at 12:59
Ninet9
asked Jan 3 at 12:52
Ninet9Ninet9
3817
asked Jan 3 at 12:52
Ninet9Ninet9
3817
asked Jan 3 at 12:52
Ninet9Ninet9
3817
3817
itemnameshould not be duplicated and fieldpropertyshould be columns.
– Martin Zeitler
Jan 3 at 12:58
what do you mean ? property is a column already. By the way as i said my goal is not to reorganize the table, just to find a solution to this problem, so i can use it in my real tables.
– Ninet9
Jan 3 at 13:01
if the idea is not to normalize the data; you will have to suffer from the consequences. the way how the data is represented might suit noSQL, but not really SQL. the way of data-modelling is different there.
– Martin Zeitler
Jan 3 at 13:01
my real tables like this: ITEMS, PROPERTIES (size,color,and a hundred more), PROPERTYVALUES (black,big,10mm,medium,short,tall etc..) and PROPERTYCONNECTIONS where i connect the item id's to the properties and the values.
– Ninet9
Jan 3 at 13:03
makesizeandcolorcolumns; then it would be something alikeSELECT *, COUNT(itemname) AS amount FROM table WHERE color="blue" AND size IN("big", "small");(with optinal grouping) if one cannot run a query like this, one can assume the structure is sub-optimal. see studytonight.com/dbms/database-normalization.php
– Martin Zeitler
Jan 3 at 13:09
|
show 2 more comments
itemnameshould not be duplicated and fieldpropertyshould be columns.
– Martin Zeitler
Jan 3 at 12:58
what do you mean ? property is a column already. By the way as i said my goal is not to reorganize the table, just to find a solution to this problem, so i can use it in my real tables.
– Ninet9
Jan 3 at 13:01
if the idea is not to normalize the data; you will have to suffer from the consequences. the way how the data is represented might suit noSQL, but not really SQL. the way of data-modelling is different there.
– Martin Zeitler
Jan 3 at 13:01
my real tables like this: ITEMS, PROPERTIES (size,color,and a hundred more), PROPERTYVALUES (black,big,10mm,medium,short,tall etc..) and PROPERTYCONNECTIONS where i connect the item id's to the properties and the values.
– Ninet9
Jan 3 at 13:03
makesizeandcolorcolumns; then it would be something alikeSELECT *, COUNT(itemname) AS amount FROM table WHERE color="blue" AND size IN("big", "small");(with optinal grouping) if one cannot run a query like this, one can assume the structure is sub-optimal. see studytonight.com/dbms/database-normalization.php
– Martin Zeitler
Jan 3 at 13:09
itemname should not be duplicated and field property should be columns.– Martin Zeitler
Jan 3 at 12:58
itemname should not be duplicated and field property should be columns.– Martin Zeitler
Jan 3 at 12:58
what do you mean ? property is a column already. By the way as i said my goal is not to reorganize the table, just to find a solution to this problem, so i can use it in my real tables.
– Ninet9
Jan 3 at 13:01
what do you mean ? property is a column already. By the way as i said my goal is not to reorganize the table, just to find a solution to this problem, so i can use it in my real tables.
– Ninet9
Jan 3 at 13:01
if the idea is not to normalize the data; you will have to suffer from the consequences. the way how the data is represented might suit noSQL, but not really SQL. the way of data-modelling is different there.
– Martin Zeitler
Jan 3 at 13:01
if the idea is not to normalize the data; you will have to suffer from the consequences. the way how the data is represented might suit noSQL, but not really SQL. the way of data-modelling is different there.
– Martin Zeitler
Jan 3 at 13:01
my real tables like this: ITEMS, PROPERTIES (size,color,and a hundred more), PROPERTYVALUES (black,big,10mm,medium,short,tall etc..) and PROPERTYCONNECTIONS where i connect the item id's to the properties and the values.
– Ninet9
Jan 3 at 13:03
my real tables like this: ITEMS, PROPERTIES (size,color,and a hundred more), PROPERTYVALUES (black,big,10mm,medium,short,tall etc..) and PROPERTYCONNECTIONS where i connect the item id's to the properties and the values.
– Ninet9
Jan 3 at 13:03
make
size and color columns; then it would be something alike SELECT *, COUNT(itemname) AS amount FROM table WHERE color="blue" AND size IN("big", "small"); (with optinal grouping) if one cannot run a query like this, one can assume the structure is sub-optimal. see studytonight.com/dbms/database-normalization.php– Martin Zeitler
Jan 3 at 13:09
make
size and color columns; then it would be something alike SELECT *, COUNT(itemname) AS amount FROM table WHERE color="blue" AND size IN("big", "small"); (with optinal grouping) if one cannot run a query like this, one can assume the structure is sub-optimal. see studytonight.com/dbms/database-normalization.php– Martin Zeitler
Jan 3 at 13:09
|
show 2 more comments
1 Answer
1
active
oldest
votes
I figured a solution on my own, of course this example table is nonsense, the point is the way it had been solved.
`
SELECT
property,
value,
COUNT(t.itemname) AS count
FROM (
SELECT
itemname
FROM
table
GROUP BY
itemname
HAVING
COUNT (CASE WHEN property='size' AND value IN ('big','small') THEN 1 END) >=1
&&
COUNT (CASE WHEN property='color' AND value IN ('blue') THEN 1 END) >=1
) t
INNER JOIN
table ON table.itemname=t.itemname
GROUP BY
property,value
`
answered Jan 4 at 10:49
Ninet9Ninet9
3817
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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%2f54022688%2fhow-can-i-make-this-mysql-query-if-possible-at-all%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I figured a solution on my own, of course this example table is nonsense, the point is the way it had been solved.
`
SELECT
property,
value,
COUNT(t.itemname) AS count
FROM (
SELECT
itemname
FROM
table
GROUP BY
itemname
HAVING
COUNT (CASE WHEN property='size' AND value IN ('big','small') THEN 1 END) >=1
&&
COUNT (CASE WHEN property='color' AND value IN ('blue') THEN 1 END) >=1
) t
INNER JOIN
table ON table.itemname=t.itemname
GROUP BY
property,value
`
answered Jan 4 at 10:49
Ninet9Ninet9
3817
add a comment |
I figured a solution on my own, of course this example table is nonsense, the point is the way it had been solved.
`
SELECT
property,
value,
COUNT(t.itemname) AS count
FROM (
SELECT
itemname
FROM
table
GROUP BY
itemname
HAVING
COUNT (CASE WHEN property='size' AND value IN ('big','small') THEN 1 END) >=1
&&
COUNT (CASE WHEN property='color' AND value IN ('blue') THEN 1 END) >=1
) t
INNER JOIN
table ON table.itemname=t.itemname
GROUP BY
property,value
`
answered Jan 4 at 10:49
Ninet9Ninet9
3817
add a comment |
I figured a solution on my own, of course this example table is nonsense, the point is the way it had been solved.
`
SELECT
property,
value,
COUNT(t.itemname) AS count
FROM (
SELECT
itemname
FROM
table
GROUP BY
itemname
HAVING
COUNT (CASE WHEN property='size' AND value IN ('big','small') THEN 1 END) >=1
&&
COUNT (CASE WHEN property='color' AND value IN ('blue') THEN 1 END) >=1
) t
INNER JOIN
table ON table.itemname=t.itemname
GROUP BY
property,value
`
answered Jan 4 at 10:49
Ninet9Ninet9
3817
I figured a solution on my own, of course this example table is nonsense, the point is the way it had been solved.
`
SELECT
property,
value,
COUNT(t.itemname) AS count
FROM (
SELECT
itemname
FROM
table
GROUP BY
itemname
HAVING
COUNT (CASE WHEN property='size' AND value IN ('big','small') THEN 1 END) >=1
&&
COUNT (CASE WHEN property='color' AND value IN ('blue') THEN 1 END) >=1
) t
INNER JOIN
table ON table.itemname=t.itemname
GROUP BY
property,value
`
answered Jan 4 at 10:49
Ninet9Ninet9
3817
answered Jan 4 at 10:49
Ninet9Ninet9
3817
answered Jan 4 at 10:49
Ninet9Ninet9
3817
answered Jan 4 at 10:49
Ninet9Ninet9
3817
3817
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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%2f54022688%2fhow-can-i-make-this-mysql-query-if-possible-at-all%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
itemnameshould not be duplicated and fieldpropertyshould be columns.– Martin Zeitler
Jan 3 at 12:58
what do you mean ? property is a column already. By the way as i said my goal is not to reorganize the table, just to find a solution to this problem, so i can use it in my real tables.
– Ninet9
Jan 3 at 13:01
if the idea is not to normalize the data; you will have to suffer from the consequences. the way how the data is represented might suit noSQL, but not really SQL. the way of data-modelling is different there.
– Martin Zeitler
Jan 3 at 13:01
my real tables like this: ITEMS, PROPERTIES (size,color,and a hundred more), PROPERTYVALUES (black,big,10mm,medium,short,tall etc..) and PROPERTYCONNECTIONS where i connect the item id's to the properties and the values.
– Ninet9
Jan 3 at 13:03
make
sizeandcolorcolumns; then it would be something alikeSELECT *, COUNT(itemname) AS amount FROM table WHERE color="blue" AND size IN("big", "small");(with optinal grouping) if one cannot run a query like this, one can assume the structure is sub-optimal. see studytonight.com/dbms/database-normalization.php– Martin Zeitler
Jan 3 at 13:09