Select rows with multi-conditional requirements in mysqlCan I concatenate multiple MySQL rows into one field?Which MySQL data type to use for storing boolean valuesHow to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?How to get a list of MySQL user accountsMySQL select 10 random rows from 600K rows fastSQL select only rows with max value on a columnHow to reset AUTO_INCREMENT in MySQL?How to import an SQL file using the command line in MySQL?
Why is Minecraft giving an OpenGL error?
Is it possible to do 50 km distance without any previous training?
How do I draw and define two right triangles next to each other?
Is it legal for company to use my work email to pretend I still work there?
Watching something be written to a file live with tail
Accidentally leaked the solution to an assignment, what to do now? (I'm the prof)
Does detail obscure or enhance action?
Modeling an IP Address
Are the number of citations and number of published articles the most important criteria for a tenure promotion?
What does the "remote control" for a QF-4 look like?
Why do I get two different answers for this counting problem?
Codimension of non-flat locus
Add text to same line using sed
If human space travel is limited by the G force vulnerability, is there a way to counter G forces?
How to format long polynomial?
How do I gain back my faith in my PhD degree?
How to determine what difficulty is right for the game?
Replacing matching entries in one column of a file by another column from a different file
Is it unprofessional to ask if a job posting on GlassDoor is real?
How can I make my BBEG immortal short of making them a Lich or Vampire?
When a company launches a new product do they "come out" with a new product or do they "come up" with a new product?
Convert two switches to a dual stack, and add outlet - possible here?
Why doesn't H₄O²⁺ exist?
LWC SFDX source push error TypeError: LWC1009: decl.moveTo is not a function
Select rows with multi-conditional requirements in mysql
Can I concatenate multiple MySQL rows into one field?Which MySQL data type to use for storing boolean valuesHow to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?How to get a list of MySQL user accountsMySQL select 10 random rows from 600K rows fastSQL select only rows with max value on a columnHow to reset AUTO_INCREMENT in MySQL?How to import an SQL file using the command line in MySQL?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have five tables,their relationships were listed in pictures. I would like to write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2013, and July 31, 2013. If a customer purchased more than one such product, display the customer’s information only once in the output. Sort the output by state, last name, and then first name.
I am OK with query with only one conditions, but for this multiple(Maybe indented?) conditions, I am totally stuck. I can analyze the structure like this:
IN LGBRAND TABLE: Brand_ID = 23 ~ Brand_Name = "Foresters Best"
inv_date from lginvoice where inv_date between "2013-7-15" and "2013-7-31"
prod_category from lgproduct = "Top Coat"
Thank you!
mysql
asked Mar 9 at 1:29
SteveSteve
246
add a comment |
I have five tables,their relationships were listed in pictures. I would like to write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2013, and July 31, 2013. If a customer purchased more than one such product, display the customer’s information only once in the output. Sort the output by state, last name, and then first name.
I am OK with query with only one conditions, but for this multiple(Maybe indented?) conditions, I am totally stuck. I can analyze the structure like this:
IN LGBRAND TABLE: Brand_ID = 23 ~ Brand_Name = "Foresters Best"
inv_date from lginvoice where inv_date between "2013-7-15" and "2013-7-31"
prod_category from lgproduct = "Top Coat"
Thank you!
mysql
asked Mar 9 at 1:29
SteveSteve
246
add a comment |
I have five tables,their relationships were listed in pictures. I would like to write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2013, and July 31, 2013. If a customer purchased more than one such product, display the customer’s information only once in the output. Sort the output by state, last name, and then first name.
I am OK with query with only one conditions, but for this multiple(Maybe indented?) conditions, I am totally stuck. I can analyze the structure like this:
IN LGBRAND TABLE: Brand_ID = 23 ~ Brand_Name = "Foresters Best"
inv_date from lginvoice where inv_date between "2013-7-15" and "2013-7-31"
prod_category from lgproduct = "Top Coat"
Thank you!
mysql
asked Mar 9 at 1:29
SteveSteve
246
I have five tables,their relationships were listed in pictures. I would like to write a query to display the first name, last name, street, city, state, and zip code of any customer who purchased a Foresters Best brand top coat between July 15, 2013, and July 31, 2013. If a customer purchased more than one such product, display the customer’s information only once in the output. Sort the output by state, last name, and then first name.
I am OK with query with only one conditions, but for this multiple(Maybe indented?) conditions, I am totally stuck. I can analyze the structure like this:
IN LGBRAND TABLE: Brand_ID = 23 ~ Brand_Name = "Foresters Best"
inv_date from lginvoice where inv_date between "2013-7-15" and "2013-7-31"
prod_category from lgproduct = "Top Coat"
Thank you!
mysql
mysql
asked Mar 9 at 1:29
SteveSteve
246
asked Mar 9 at 1:29
SteveSteve
246
asked Mar 9 at 1:29
SteveSteve
246
asked Mar 9 at 1:29
SteveSteve
246
asked Mar 9 at 1:29
SteveSteve
246
246
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
It's hard to be certain without sample data, but something like this should work:
SELECT DISTINCT c.*
FROM LGCUSTOMER c
JOIN LGINVOICE i ON i.Cust_Code = c.Cust_Code
JOIN LGLINE l ON l.Inv_Num = i.Inv_Num
JOIN LGPRODUCT p ON p.Prod_SKU = l.Prod_SKU
JOIN LGBRAND b ON b.Brand_ID = p.Brand_ID
WHERE b.Brand_Name = 'Foresters Best' AND
p.Prod_Category = 'Top Coat' AND
i.Inv_Date BETWEEN '2013-07-15' AND '2013-07-31'
ORDER BY c.Cust_State, c.Cust_Lname, c.Cust_Fname
answered Mar 9 at 1:38
NickNick
38.5k132443
add a comment |
select distinct(Cust_Fname),distinct(Cust_Lname),distinct(Cust_Street),distinct(Cust_City),distinct(Cust_State),distinct(Cust_ZIP) from lgcustomer as cust
join (select inv_num,cust_code from lginvoice where CAST(inv_date AS DATE) between '2013-07-15' and '2013-07-31') inv on inv.cust_code = cust.cust_code
join (select inv_num, prod_sku from lgline) ll on ll.inv_num = inv.inv_num
join (select prod_sku,prod_descipt, brand_id from lgproduct where prod_descipt like "%Top Coat%") lgp on lgp.prod_sku = ll.prod_sku
join (select brand_id, brand_name from lgbrand where brand_name like "%Foresters Best%") lb on lb.brand_id = lgp.brand_id
order by 5,2,1 desc
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
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%2f55073123%2fselect-rows-with-multi-conditional-requirements-in-mysql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
It's hard to be certain without sample data, but something like this should work:
SELECT DISTINCT c.*
FROM LGCUSTOMER c
JOIN LGINVOICE i ON i.Cust_Code = c.Cust_Code
JOIN LGLINE l ON l.Inv_Num = i.Inv_Num
JOIN LGPRODUCT p ON p.Prod_SKU = l.Prod_SKU
JOIN LGBRAND b ON b.Brand_ID = p.Brand_ID
WHERE b.Brand_Name = 'Foresters Best' AND
p.Prod_Category = 'Top Coat' AND
i.Inv_Date BETWEEN '2013-07-15' AND '2013-07-31'
ORDER BY c.Cust_State, c.Cust_Lname, c.Cust_Fname
answered Mar 9 at 1:38
NickNick
38.5k132443
add a comment |
It's hard to be certain without sample data, but something like this should work:
SELECT DISTINCT c.*
FROM LGCUSTOMER c
JOIN LGINVOICE i ON i.Cust_Code = c.Cust_Code
JOIN LGLINE l ON l.Inv_Num = i.Inv_Num
JOIN LGPRODUCT p ON p.Prod_SKU = l.Prod_SKU
JOIN LGBRAND b ON b.Brand_ID = p.Brand_ID
WHERE b.Brand_Name = 'Foresters Best' AND
p.Prod_Category = 'Top Coat' AND
i.Inv_Date BETWEEN '2013-07-15' AND '2013-07-31'
ORDER BY c.Cust_State, c.Cust_Lname, c.Cust_Fname
answered Mar 9 at 1:38
NickNick
38.5k132443
add a comment |
It's hard to be certain without sample data, but something like this should work:
SELECT DISTINCT c.*
FROM LGCUSTOMER c
JOIN LGINVOICE i ON i.Cust_Code = c.Cust_Code
JOIN LGLINE l ON l.Inv_Num = i.Inv_Num
JOIN LGPRODUCT p ON p.Prod_SKU = l.Prod_SKU
JOIN LGBRAND b ON b.Brand_ID = p.Brand_ID
WHERE b.Brand_Name = 'Foresters Best' AND
p.Prod_Category = 'Top Coat' AND
i.Inv_Date BETWEEN '2013-07-15' AND '2013-07-31'
ORDER BY c.Cust_State, c.Cust_Lname, c.Cust_Fname
answered Mar 9 at 1:38
NickNick
38.5k132443
It's hard to be certain without sample data, but something like this should work:
SELECT DISTINCT c.*
FROM LGCUSTOMER c
JOIN LGINVOICE i ON i.Cust_Code = c.Cust_Code
JOIN LGLINE l ON l.Inv_Num = i.Inv_Num
JOIN LGPRODUCT p ON p.Prod_SKU = l.Prod_SKU
JOIN LGBRAND b ON b.Brand_ID = p.Brand_ID
WHERE b.Brand_Name = 'Foresters Best' AND
p.Prod_Category = 'Top Coat' AND
i.Inv_Date BETWEEN '2013-07-15' AND '2013-07-31'
ORDER BY c.Cust_State, c.Cust_Lname, c.Cust_Fname
answered Mar 9 at 1:38
NickNick
38.5k132443
answered Mar 9 at 1:38
NickNick
38.5k132443
answered Mar 9 at 1:38
NickNick
38.5k132443
answered Mar 9 at 1:38
NickNick
38.5k132443
38.5k132443
add a comment |
add a comment |
select distinct(Cust_Fname),distinct(Cust_Lname),distinct(Cust_Street),distinct(Cust_City),distinct(Cust_State),distinct(Cust_ZIP) from lgcustomer as cust
join (select inv_num,cust_code from lginvoice where CAST(inv_date AS DATE) between '2013-07-15' and '2013-07-31') inv on inv.cust_code = cust.cust_code
join (select inv_num, prod_sku from lgline) ll on ll.inv_num = inv.inv_num
join (select prod_sku,prod_descipt, brand_id from lgproduct where prod_descipt like "%Top Coat%") lgp on lgp.prod_sku = ll.prod_sku
join (select brand_id, brand_name from lgbrand where brand_name like "%Foresters Best%") lb on lb.brand_id = lgp.brand_id
order by 5,2,1 desc
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
add a comment |
select distinct(Cust_Fname),distinct(Cust_Lname),distinct(Cust_Street),distinct(Cust_City),distinct(Cust_State),distinct(Cust_ZIP) from lgcustomer as cust
join (select inv_num,cust_code from lginvoice where CAST(inv_date AS DATE) between '2013-07-15' and '2013-07-31') inv on inv.cust_code = cust.cust_code
join (select inv_num, prod_sku from lgline) ll on ll.inv_num = inv.inv_num
join (select prod_sku,prod_descipt, brand_id from lgproduct where prod_descipt like "%Top Coat%") lgp on lgp.prod_sku = ll.prod_sku
join (select brand_id, brand_name from lgbrand where brand_name like "%Foresters Best%") lb on lb.brand_id = lgp.brand_id
order by 5,2,1 desc
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
add a comment |
select distinct(Cust_Fname),distinct(Cust_Lname),distinct(Cust_Street),distinct(Cust_City),distinct(Cust_State),distinct(Cust_ZIP) from lgcustomer as cust
join (select inv_num,cust_code from lginvoice where CAST(inv_date AS DATE) between '2013-07-15' and '2013-07-31') inv on inv.cust_code = cust.cust_code
join (select inv_num, prod_sku from lgline) ll on ll.inv_num = inv.inv_num
join (select prod_sku,prod_descipt, brand_id from lgproduct where prod_descipt like "%Top Coat%") lgp on lgp.prod_sku = ll.prod_sku
join (select brand_id, brand_name from lgbrand where brand_name like "%Foresters Best%") lb on lb.brand_id = lgp.brand_id
order by 5,2,1 desc
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
select distinct(Cust_Fname),distinct(Cust_Lname),distinct(Cust_Street),distinct(Cust_City),distinct(Cust_State),distinct(Cust_ZIP) from lgcustomer as cust
join (select inv_num,cust_code from lginvoice where CAST(inv_date AS DATE) between '2013-07-15' and '2013-07-31') inv on inv.cust_code = cust.cust_code
join (select inv_num, prod_sku from lgline) ll on ll.inv_num = inv.inv_num
join (select prod_sku,prod_descipt, brand_id from lgproduct where prod_descipt like "%Top Coat%") lgp on lgp.prod_sku = ll.prod_sku
join (select brand_id, brand_name from lgbrand where brand_name like "%Foresters Best%") lb on lb.brand_id = lgp.brand_id
order by 5,2,1 desc
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
answered Mar 9 at 1:59
Sathvik ReddySathvik Reddy
265
265
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%2f55073123%2fselect-rows-with-multi-conditional-requirements-in-mysql%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
