How to use where clause within my big join Mysql query to restrict results?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have successfully written this join query below to get all the data I need from each individual table in the database within one query. Unfortunately, it gives me ALL the data from these individual tables. I only need data that corresponds to e.Design_ID, if e.Design_ID = 1 I have tried a few "where e.Design_ID = 1" within this code block, but have not been successful in restricting the query. I'm stuck. I'm thinking I may need to restructure my entire query. My logic may be off...
Any help is much appreciated.
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
";
mysql
add a comment |
I have successfully written this join query below to get all the data I need from each individual table in the database within one query. Unfortunately, it gives me ALL the data from these individual tables. I only need data that corresponds to e.Design_ID, if e.Design_ID = 1 I have tried a few "where e.Design_ID = 1" within this code block, but have not been successful in restricting the query. I'm stuck. I'm thinking I may need to restructure my entire query. My logic may be off...
Any help is much appreciated.
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
";
mysql
What about the query do you wish to restrict? Your joins look fine but since you are not limiting the data with a where clause, you will get all the data
– Cup of Java
Nov 25 '18 at 1:29
I only want to get data that corresponds to e.Design_ID if e.Design_ID=1
– Wyatt Jackson
Nov 25 '18 at 2:00
add a comment |
I have successfully written this join query below to get all the data I need from each individual table in the database within one query. Unfortunately, it gives me ALL the data from these individual tables. I only need data that corresponds to e.Design_ID, if e.Design_ID = 1 I have tried a few "where e.Design_ID = 1" within this code block, but have not been successful in restricting the query. I'm stuck. I'm thinking I may need to restructure my entire query. My logic may be off...
Any help is much appreciated.
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
";
mysql
I have successfully written this join query below to get all the data I need from each individual table in the database within one query. Unfortunately, it gives me ALL the data from these individual tables. I only need data that corresponds to e.Design_ID, if e.Design_ID = 1 I have tried a few "where e.Design_ID = 1" within this code block, but have not been successful in restricting the query. I'm stuck. I'm thinking I may need to restructure my entire query. My logic may be off...
Any help is much appreciated.
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
";
mysql
mysql
asked Nov 25 '18 at 1:25
Wyatt JacksonWyatt Jackson
787
787
What about the query do you wish to restrict? Your joins look fine but since you are not limiting the data with a where clause, you will get all the data
– Cup of Java
Nov 25 '18 at 1:29
I only want to get data that corresponds to e.Design_ID if e.Design_ID=1
– Wyatt Jackson
Nov 25 '18 at 2:00
add a comment |
What about the query do you wish to restrict? Your joins look fine but since you are not limiting the data with a where clause, you will get all the data
– Cup of Java
Nov 25 '18 at 1:29
I only want to get data that corresponds to e.Design_ID if e.Design_ID=1
– Wyatt Jackson
Nov 25 '18 at 2:00
What about the query do you wish to restrict? Your joins look fine but since you are not limiting the data with a where clause, you will get all the data
– Cup of Java
Nov 25 '18 at 1:29
What about the query do you wish to restrict? Your joins look fine but since you are not limiting the data with a where clause, you will get all the data
– Cup of Java
Nov 25 '18 at 1:29
I only want to get data that corresponds to e.Design_ID if e.Design_ID=1
– Wyatt Jackson
Nov 25 '18 at 2:00
I only want to get data that corresponds to e.Design_ID if e.Design_ID=1
– Wyatt Jackson
Nov 25 '18 at 2:00
add a comment |
1 Answer
1
active
oldest
votes
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
WHERE e.Design_ID = 1
";
It should work if you put it at the end. Also check correct spelling.
Hi. Thank you for your help. Unfortunately, it does not. I still get all the other data corresponding to all of the rows of data within "Designs". The query is not restricted to just the 1 row with e.Design_ID = 1.
– Wyatt Jackson
Nov 25 '18 at 1:59
Try to put e.Design_ID = 1 into () -> where (e.Design_ID = 1)
– Timothy Lukas H.
Nov 25 '18 at 2:08
Unfortunately, the same result...
– Wyatt Jackson
Nov 25 '18 at 2:15
Did you write it in CAPS?
– Timothy Lukas H.
Nov 25 '18 at 2:18
1
Ok. This is the correct answer. I had a completely unrelated bug outside of this code that was causing me to see incorrect results. Thank you for your help. This bug was driving me wonky.
– Wyatt Jackson
Nov 25 '18 at 8:19
|
show 1 more 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%2f53463912%2fhow-to-use-where-clause-within-my-big-join-mysql-query-to-restrict-results%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
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
WHERE e.Design_ID = 1
";
It should work if you put it at the end. Also check correct spelling.
Hi. Thank you for your help. Unfortunately, it does not. I still get all the other data corresponding to all of the rows of data within "Designs". The query is not restricted to just the 1 row with e.Design_ID = 1.
– Wyatt Jackson
Nov 25 '18 at 1:59
Try to put e.Design_ID = 1 into () -> where (e.Design_ID = 1)
– Timothy Lukas H.
Nov 25 '18 at 2:08
Unfortunately, the same result...
– Wyatt Jackson
Nov 25 '18 at 2:15
Did you write it in CAPS?
– Timothy Lukas H.
Nov 25 '18 at 2:18
1
Ok. This is the correct answer. I had a completely unrelated bug outside of this code that was causing me to see incorrect results. Thank you for your help. This bug was driving me wonky.
– Wyatt Jackson
Nov 25 '18 at 8:19
|
show 1 more comment
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
WHERE e.Design_ID = 1
";
It should work if you put it at the end. Also check correct spelling.
Hi. Thank you for your help. Unfortunately, it does not. I still get all the other data corresponding to all of the rows of data within "Designs". The query is not restricted to just the 1 row with e.Design_ID = 1.
– Wyatt Jackson
Nov 25 '18 at 1:59
Try to put e.Design_ID = 1 into () -> where (e.Design_ID = 1)
– Timothy Lukas H.
Nov 25 '18 at 2:08
Unfortunately, the same result...
– Wyatt Jackson
Nov 25 '18 at 2:15
Did you write it in CAPS?
– Timothy Lukas H.
Nov 25 '18 at 2:18
1
Ok. This is the correct answer. I had a completely unrelated bug outside of this code that was causing me to see incorrect results. Thank you for your help. This bug was driving me wonky.
– Wyatt Jackson
Nov 25 '18 at 8:19
|
show 1 more comment
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
WHERE e.Design_ID = 1
";
It should work if you put it at the end. Also check correct spelling.
SELECT
a.SKU,
a.Apparel_ID,
a.Apparel_Color_Abbr,
a.Design_ID,
a.City_ID,
a.Category_ID,
a.SEO_Keyword,
a.OC_Product_ID,
b.Apparel_Color_Palette,
b.Apparel_Name,
b.Apparel_Name_Abbr,
b.Apparel_Type,
b.Google_Feed_Cat,
b.Gender,
b.Age_Group,
b.Retail_Price,
b.Apparel_Description,
c.Apparel_Color,
d.OC_Cat_City_ID,
d.OC_Cat_State_ID,
d.City,
d.State,
e.Design_Name,
e.Design_Description
FROM Complete_City_Products AS a
INNER JOIN Apparel AS b ON a.Apparel_ID = b.Apparel_ID
INNER JOIN Apparel_Colors AS c ON b.Apparel_Color_Palette = c.Apparel_Color_Palette AND a.Apparel_Color_Abbr = c.Apparel_Color_Abbr
INNER JOIN Cities AS d ON a.City_ID = d.City_ID
INNER JOIN Designs AS e ON a.Design_ID = e.Design_ID
WHERE e.Design_ID = 1
";
It should work if you put it at the end. Also check correct spelling.
edited Nov 25 '18 at 2:18
answered Nov 25 '18 at 1:28
Timothy Lukas H.Timothy Lukas H.
286313
286313
Hi. Thank you for your help. Unfortunately, it does not. I still get all the other data corresponding to all of the rows of data within "Designs". The query is not restricted to just the 1 row with e.Design_ID = 1.
– Wyatt Jackson
Nov 25 '18 at 1:59
Try to put e.Design_ID = 1 into () -> where (e.Design_ID = 1)
– Timothy Lukas H.
Nov 25 '18 at 2:08
Unfortunately, the same result...
– Wyatt Jackson
Nov 25 '18 at 2:15
Did you write it in CAPS?
– Timothy Lukas H.
Nov 25 '18 at 2:18
1
Ok. This is the correct answer. I had a completely unrelated bug outside of this code that was causing me to see incorrect results. Thank you for your help. This bug was driving me wonky.
– Wyatt Jackson
Nov 25 '18 at 8:19
|
show 1 more comment
Hi. Thank you for your help. Unfortunately, it does not. I still get all the other data corresponding to all of the rows of data within "Designs". The query is not restricted to just the 1 row with e.Design_ID = 1.
– Wyatt Jackson
Nov 25 '18 at 1:59
Try to put e.Design_ID = 1 into () -> where (e.Design_ID = 1)
– Timothy Lukas H.
Nov 25 '18 at 2:08
Unfortunately, the same result...
– Wyatt Jackson
Nov 25 '18 at 2:15
Did you write it in CAPS?
– Timothy Lukas H.
Nov 25 '18 at 2:18
1
Ok. This is the correct answer. I had a completely unrelated bug outside of this code that was causing me to see incorrect results. Thank you for your help. This bug was driving me wonky.
– Wyatt Jackson
Nov 25 '18 at 8:19
Hi. Thank you for your help. Unfortunately, it does not. I still get all the other data corresponding to all of the rows of data within "Designs". The query is not restricted to just the 1 row with e.Design_ID = 1.
– Wyatt Jackson
Nov 25 '18 at 1:59
Hi. Thank you for your help. Unfortunately, it does not. I still get all the other data corresponding to all of the rows of data within "Designs". The query is not restricted to just the 1 row with e.Design_ID = 1.
– Wyatt Jackson
Nov 25 '18 at 1:59
Try to put e.Design_ID = 1 into () -> where (e.Design_ID = 1)
– Timothy Lukas H.
Nov 25 '18 at 2:08
Try to put e.Design_ID = 1 into () -> where (e.Design_ID = 1)
– Timothy Lukas H.
Nov 25 '18 at 2:08
Unfortunately, the same result...
– Wyatt Jackson
Nov 25 '18 at 2:15
Unfortunately, the same result...
– Wyatt Jackson
Nov 25 '18 at 2:15
Did you write it in CAPS?
– Timothy Lukas H.
Nov 25 '18 at 2:18
Did you write it in CAPS?
– Timothy Lukas H.
Nov 25 '18 at 2:18
1
1
Ok. This is the correct answer. I had a completely unrelated bug outside of this code that was causing me to see incorrect results. Thank you for your help. This bug was driving me wonky.
– Wyatt Jackson
Nov 25 '18 at 8:19
Ok. This is the correct answer. I had a completely unrelated bug outside of this code that was causing me to see incorrect results. Thank you for your help. This bug was driving me wonky.
– Wyatt Jackson
Nov 25 '18 at 8:19
|
show 1 more 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%2f53463912%2fhow-to-use-where-clause-within-my-big-join-mysql-query-to-restrict-results%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
What about the query do you wish to restrict? Your joins look fine but since you are not limiting the data with a where clause, you will get all the data
– Cup of Java
Nov 25 '18 at 1:29
I only want to get data that corresponds to e.Design_ID if e.Design_ID=1
– Wyatt Jackson
Nov 25 '18 at 2:00