SQL: select a new column that true if row (in another table) exist
-1
I have tables that have columns look like this:
Customer: cid, blah
Product: pid, blah
Order: cid, pid
I want the result table that have these columns:
cid, pid, (this column is 'true' when have (cid,pid) of that row in Order otherwise 'false')
for example:
customer
1 blah
2 blah
3 blah
product
1 blah
2 blah
3 blah
4 blah
order
1 2
1 3
2 1
2 4
3 3
I want to find the sql query that have this result:
1 1 false
1 2 true
1 3 true
1 4 false
2 1 true
2 2 false
2 3 false
2 4 true
3 1 false
3 2 false
3 3 true
3 4 false
I'm trying to figure out that query but it is limited to my knowledge. Anyone know how to write that query?
// sorry, forget to mention that I want the blah from customer and product in the result as well
cid, blah, pid, blah, true/false
mysql sql
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
add a comment |
-1
I have tables that have columns look like this:
Customer: cid, blah
Product: pid, blah
Order: cid, pid
I want the result table that have these columns:
cid, pid, (this column is 'true' when have (cid,pid) of that row in Order otherwise 'false')
for example:
customer
1 blah
2 blah
3 blah
product
1 blah
2 blah
3 blah
4 blah
order
1 2
1 3
2 1
2 4
3 3
I want to find the sql query that have this result:
1 1 false
1 2 true
1 3 true
1 4 false
2 1 true
2 2 false
2 3 false
2 4 true
3 1 false
3 2 false
3 3 true
3 4 false
I'm trying to figure out that query but it is limited to my knowledge. Anyone know how to write that query?
// sorry, forget to mention that I want the blah from customer and product in the result as well
cid, blah, pid, blah, true/false
mysql sql
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
Please read & act on How to Ask, hits googling 'stackexchange homework' & the downvote arrow mouseover text.
– philipxy
Nov 24 '18 at 10:13
add a comment |
-1
-1
-1
I have tables that have columns look like this:
Customer: cid, blah
Product: pid, blah
Order: cid, pid
I want the result table that have these columns:
cid, pid, (this column is 'true' when have (cid,pid) of that row in Order otherwise 'false')
for example:
customer
1 blah
2 blah
3 blah
product
1 blah
2 blah
3 blah
4 blah
order
1 2
1 3
2 1
2 4
3 3
I want to find the sql query that have this result:
1 1 false
1 2 true
1 3 true
1 4 false
2 1 true
2 2 false
2 3 false
2 4 true
3 1 false
3 2 false
3 3 true
3 4 false
I'm trying to figure out that query but it is limited to my knowledge. Anyone know how to write that query?
// sorry, forget to mention that I want the blah from customer and product in the result as well
cid, blah, pid, blah, true/false
mysql sql
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
I have tables that have columns look like this:
Customer: cid, blah
Product: pid, blah
Order: cid, pid
I want the result table that have these columns:
cid, pid, (this column is 'true' when have (cid,pid) of that row in Order otherwise 'false')
for example:
customer
1 blah
2 blah
3 blah
product
1 blah
2 blah
3 blah
4 blah
order
1 2
1 3
2 1
2 4
3 3
I want to find the sql query that have this result:
1 1 false
1 2 true
1 3 true
1 4 false
2 1 true
2 2 false
2 3 false
2 4 true
3 1 false
3 2 false
3 3 true
3 4 false
I'm trying to figure out that query but it is limited to my knowledge. Anyone know how to write that query?
// sorry, forget to mention that I want the blah from customer and product in the result as well
cid, blah, pid, blah, true/false
mysql sql
mysql sql
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
edited Nov 24 '18 at 9:49
taepatipol
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
asked Nov 24 '18 at 9:08
taepatipoltaepatipol
35
35
Please read & act on How to Ask, hits googling 'stackexchange homework' & the downvote arrow mouseover text.
– philipxy
Nov 24 '18 at 10:13
add a comment |
Please read & act on How to Ask, hits googling 'stackexchange homework' & the downvote arrow mouseover text.
– philipxy
Nov 24 '18 at 10:13
Please read & act on How to Ask, hits googling 'stackexchange homework' & the downvote arrow mouseover text.
– philipxy
Nov 24 '18 at 10:13
Please read & act on How to Ask, hits googling 'stackexchange homework' & the downvote arrow mouseover text.
– philipxy
Nov 24 '18 at 10:13
add a comment |
3 Answers
3
active
oldest
votes
1
You may cross and left joins and case..when conditional statement as :
select c.cid, p.pid,
( case when o.cid*o.pid > 0 then 'true' else 'false' end )
as flag
from product p
cross join customer c
left join order_ o on o.cid = c.cid and o.pid = p.pid
order by c.cid, p.pid;
P.S. order is a keyword, so I replaced that with order_
Rextester Demo
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
+1 as this is the best approach. However, I don't think thatcoalesce(o.cid,0)*coalesce(o.pid,0)is needed. Checking on any one field is enough, as your left join is based on two conditions (two columns).
– Madhur Bhaiya
Nov 24 '18 at 16:27
@MadhurBhaiya exactly, good catch thanks.
– Barbaros Özhan
Nov 24 '18 at 16:31
add a comment |
0
SELECT C.CID,P.PID,
CASE WHEN C.CID,P.PID IN
(SELECT CID,PID from
ORDER)
THEN "TRUE" END CASE
CASE WHEN C.CID,P.PID NOT IN
(SELECT CID,PID from
ORDER)
THEN "FALSE" END CASE
FROM CUSTOMER C,PRODUCT P ;
You need a case statement for that.
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
add a comment |
0
this is the simplest way :
(
SELECT O.cid, O.pid, 'true'
FROM order O
Where (O.cid, O.pid) in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
UNION
(
SELECT O.cid, O.pid, 'false'
FROM order O
Where (O.cid, O.pid) not in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
I think the code under the UNION is not work. There aren't any row in O that not in C cross join P. I think it should becustomer C CROSS JOIN product Pinstead oforder O.
– taepatipol
Nov 24 '18 at 10:54
did you execute it?
– FatemehNB
Nov 24 '18 at 13:28
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
});
}
});
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%2f53456720%2fsql-select-a-new-column-that-true-if-row-in-another-table-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You may cross and left joins and case..when conditional statement as :
select c.cid, p.pid,
( case when o.cid*o.pid > 0 then 'true' else 'false' end )
as flag
from product p
cross join customer c
left join order_ o on o.cid = c.cid and o.pid = p.pid
order by c.cid, p.pid;
P.S. order is a keyword, so I replaced that with order_
Rextester Demo
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
+1 as this is the best approach. However, I don't think thatcoalesce(o.cid,0)*coalesce(o.pid,0)is needed. Checking on any one field is enough, as your left join is based on two conditions (two columns).
– Madhur Bhaiya
Nov 24 '18 at 16:27
@MadhurBhaiya exactly, good catch thanks.
– Barbaros Özhan
Nov 24 '18 at 16:31
add a comment |
1
You may cross and left joins and case..when conditional statement as :
select c.cid, p.pid,
( case when o.cid*o.pid > 0 then 'true' else 'false' end )
as flag
from product p
cross join customer c
left join order_ o on o.cid = c.cid and o.pid = p.pid
order by c.cid, p.pid;
P.S. order is a keyword, so I replaced that with order_
Rextester Demo
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
+1 as this is the best approach. However, I don't think thatcoalesce(o.cid,0)*coalesce(o.pid,0)is needed. Checking on any one field is enough, as your left join is based on two conditions (two columns).
– Madhur Bhaiya
Nov 24 '18 at 16:27
@MadhurBhaiya exactly, good catch thanks.
– Barbaros Özhan
Nov 24 '18 at 16:31
add a comment |
1
1
1
You may cross and left joins and case..when conditional statement as :
select c.cid, p.pid,
( case when o.cid*o.pid > 0 then 'true' else 'false' end )
as flag
from product p
cross join customer c
left join order_ o on o.cid = c.cid and o.pid = p.pid
order by c.cid, p.pid;
P.S. order is a keyword, so I replaced that with order_
Rextester Demo
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
You may cross and left joins and case..when conditional statement as :
select c.cid, p.pid,
( case when o.cid*o.pid > 0 then 'true' else 'false' end )
as flag
from product p
cross join customer c
left join order_ o on o.cid = c.cid and o.pid = p.pid
order by c.cid, p.pid;
P.S. order is a keyword, so I replaced that with order_
Rextester Demo
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
edited Nov 24 '18 at 16:31
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
answered Nov 24 '18 at 9:23
Barbaros ÖzhanBarbaros Özhan
13.4k71633
13.4k71633
+1 as this is the best approach. However, I don't think thatcoalesce(o.cid,0)*coalesce(o.pid,0)is needed. Checking on any one field is enough, as your left join is based on two conditions (two columns).
– Madhur Bhaiya
Nov 24 '18 at 16:27
@MadhurBhaiya exactly, good catch thanks.
– Barbaros Özhan
Nov 24 '18 at 16:31
add a comment |
+1 as this is the best approach. However, I don't think thatcoalesce(o.cid,0)*coalesce(o.pid,0)is needed. Checking on any one field is enough, as your left join is based on two conditions (two columns).
– Madhur Bhaiya
Nov 24 '18 at 16:27
@MadhurBhaiya exactly, good catch thanks.
– Barbaros Özhan
Nov 24 '18 at 16:31
+1 as this is the best approach. However, I don't think that
coalesce(o.cid,0)*coalesce(o.pid,0) is needed. Checking on any one field is enough, as your left join is based on two conditions (two columns).– Madhur Bhaiya
Nov 24 '18 at 16:27
+1 as this is the best approach. However, I don't think that
coalesce(o.cid,0)*coalesce(o.pid,0) is needed. Checking on any one field is enough, as your left join is based on two conditions (two columns).– Madhur Bhaiya
Nov 24 '18 at 16:27
@MadhurBhaiya exactly, good catch thanks.
– Barbaros Özhan
Nov 24 '18 at 16:31
@MadhurBhaiya exactly, good catch thanks.
– Barbaros Özhan
Nov 24 '18 at 16:31
add a comment |
0
SELECT C.CID,P.PID,
CASE WHEN C.CID,P.PID IN
(SELECT CID,PID from
ORDER)
THEN "TRUE" END CASE
CASE WHEN C.CID,P.PID NOT IN
(SELECT CID,PID from
ORDER)
THEN "FALSE" END CASE
FROM CUSTOMER C,PRODUCT P ;
You need a case statement for that.
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
add a comment |
0
SELECT C.CID,P.PID,
CASE WHEN C.CID,P.PID IN
(SELECT CID,PID from
ORDER)
THEN "TRUE" END CASE
CASE WHEN C.CID,P.PID NOT IN
(SELECT CID,PID from
ORDER)
THEN "FALSE" END CASE
FROM CUSTOMER C,PRODUCT P ;
You need a case statement for that.
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
add a comment |
0
0
0
SELECT C.CID,P.PID,
CASE WHEN C.CID,P.PID IN
(SELECT CID,PID from
ORDER)
THEN "TRUE" END CASE
CASE WHEN C.CID,P.PID NOT IN
(SELECT CID,PID from
ORDER)
THEN "FALSE" END CASE
FROM CUSTOMER C,PRODUCT P ;
You need a case statement for that.
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
SELECT C.CID,P.PID,
CASE WHEN C.CID,P.PID IN
(SELECT CID,PID from
ORDER)
THEN "TRUE" END CASE
CASE WHEN C.CID,P.PID NOT IN
(SELECT CID,PID from
ORDER)
THEN "FALSE" END CASE
FROM CUSTOMER C,PRODUCT P ;
You need a case statement for that.
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
answered Nov 24 '18 at 9:23
Himanshu AhujaHimanshu Ahuja
6792217
6792217
add a comment |
add a comment |
0
this is the simplest way :
(
SELECT O.cid, O.pid, 'true'
FROM order O
Where (O.cid, O.pid) in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
UNION
(
SELECT O.cid, O.pid, 'false'
FROM order O
Where (O.cid, O.pid) not in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
I think the code under the UNION is not work. There aren't any row in O that not in C cross join P. I think it should becustomer C CROSS JOIN product Pinstead oforder O.
– taepatipol
Nov 24 '18 at 10:54
did you execute it?
– FatemehNB
Nov 24 '18 at 13:28
add a comment |
0
this is the simplest way :
(
SELECT O.cid, O.pid, 'true'
FROM order O
Where (O.cid, O.pid) in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
UNION
(
SELECT O.cid, O.pid, 'false'
FROM order O
Where (O.cid, O.pid) not in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
I think the code under the UNION is not work. There aren't any row in O that not in C cross join P. I think it should becustomer C CROSS JOIN product Pinstead oforder O.
– taepatipol
Nov 24 '18 at 10:54
did you execute it?
– FatemehNB
Nov 24 '18 at 13:28
add a comment |
0
0
0
this is the simplest way :
(
SELECT O.cid, O.pid, 'true'
FROM order O
Where (O.cid, O.pid) in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
UNION
(
SELECT O.cid, O.pid, 'false'
FROM order O
Where (O.cid, O.pid) not in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
this is the simplest way :
(
SELECT O.cid, O.pid, 'true'
FROM order O
Where (O.cid, O.pid) in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
UNION
(
SELECT O.cid, O.pid, 'false'
FROM order O
Where (O.cid, O.pid) not in
(SELECT C.cid, p.pid,
FROM customer C CROSS JOIN product P)
)
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
edited Nov 24 '18 at 9:29
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
answered Nov 24 '18 at 9:24
FatemehNBFatemehNB
25126
25126
I think the code under the UNION is not work. There aren't any row in O that not in C cross join P. I think it should becustomer C CROSS JOIN product Pinstead oforder O.
– taepatipol
Nov 24 '18 at 10:54
did you execute it?
– FatemehNB
Nov 24 '18 at 13:28
add a comment |
I think the code under the UNION is not work. There aren't any row in O that not in C cross join P. I think it should becustomer C CROSS JOIN product Pinstead oforder O.
– taepatipol
Nov 24 '18 at 10:54
did you execute it?
– FatemehNB
Nov 24 '18 at 13:28
I think the code under the UNION is not work. There aren't any row in O that not in C cross join P. I think it should be
customer C CROSS JOIN product P instead of order O.– taepatipol
Nov 24 '18 at 10:54
I think the code under the UNION is not work. There aren't any row in O that not in C cross join P. I think it should be
customer C CROSS JOIN product P instead of order O.– taepatipol
Nov 24 '18 at 10:54
did you execute it?
– FatemehNB
Nov 24 '18 at 13:28
did you execute it?
– FatemehNB
Nov 24 '18 at 13:28
add a comment |
draft saved
draft discarded
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.
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%2f53456720%2fsql-select-a-new-column-that-true-if-row-in-another-table-exist%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
Please read & act on How to Ask, hits googling 'stackexchange homework' & the downvote arrow mouseover text.
– philipxy
Nov 24 '18 at 10:13