Mixing
SELECT JOIN WHERE a record does not exist
I have 2 tables
id name type
1 aa driver
2 bb cyclist
3 cc runner
parent_id key value
1 mobile 00299029
2 mobile 008772
2 active 1
3 mobile 09887
3 active 0
I need to get the record 1,aa,driver, the one that has not a record with value 'active' in the second table.
My last try was something like this, but I'n not sure to be even a bit close to what I need, my result is always 0 records
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND NOT EXISTS (
SELECT * FROM table2
WHERE key = 'active'
)
mysql sql join
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
add a comment |
I have 2 tables
id name type
1 aa driver
2 bb cyclist
3 cc runner
parent_id key value
1 mobile 00299029
2 mobile 008772
2 active 1
3 mobile 09887
3 active 0
I need to get the record 1,aa,driver, the one that has not a record with value 'active' in the second table.
My last try was something like this, but I'n not sure to be even a bit close to what I need, my result is always 0 records
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND NOT EXISTS (
SELECT * FROM table2
WHERE key = 'active'
)
mysql sql join
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
add a comment |
I have 2 tables
id name type
1 aa driver
2 bb cyclist
3 cc runner
parent_id key value
1 mobile 00299029
2 mobile 008772
2 active 1
3 mobile 09887
3 active 0
I need to get the record 1,aa,driver, the one that has not a record with value 'active' in the second table.
My last try was something like this, but I'n not sure to be even a bit close to what I need, my result is always 0 records
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND NOT EXISTS (
SELECT * FROM table2
WHERE key = 'active'
)
mysql sql join
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
I have 2 tables
id name type
1 aa driver
2 bb cyclist
3 cc runner
parent_id key value
1 mobile 00299029
2 mobile 008772
2 active 1
3 mobile 09887
3 active 0
I need to get the record 1,aa,driver, the one that has not a record with value 'active' in the second table.
My last try was something like this, but I'n not sure to be even a bit close to what I need, my result is always 0 records
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND NOT EXISTS (
SELECT * FROM table2
WHERE key = 'active'
)
mysql sql join
mysql sql join
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
edited Nov 21 '18 at 16:55
Rahul Neekhra
6001627
6001627
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
asked Nov 21 '18 at 16:53
ssstofffssstofff
501111
501111
add a comment |
add a comment |
7 Answers
7
active
oldest
votes
Just a left join would do
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.id = t2.parent_id
AND t2.key = 'active'
WHERE t2.key IS NULL
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
add a comment |
You can use NOT EXISTS as follows:
select t1.name from table1 t1
where not exists
(
select 1 from table2 t2 where t1.id = t2.parent_id AND t2.key = 'active'
)
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
add a comment |
Try this (I think, not entirely sure I understand the linkage):
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.id = t2.parent_id and t2.key = 'active' where t2.key is null
A left join returns all elements of the first table regardless of whether they have a corresponding record in the joined table. By then including t2.key is null in the where clause, you limit it to records that only exist in the first table.
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
add a comment |
This will work:
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND t2.key not like '%active%'
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
add a comment |
Select id,name,type from table1
where id Not in
(Select
parent_id from table2
group by parent_id
having key=
'active')
Better avoid join in this case I would say as subquery
would do good in this case.
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
add a comment |
I think Your query is not working in this case, please try
this
SELECT name FROM table1
JOIN table2 ON table1.id =
table2.parent_id where id not IN ( SELECT parent_id
FROM
table2 WHERE `key` = 'active' )
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
answered Nov 21 '18 at 17:12
user9639650user9639650
422
Why you used join and subquery together I dont understand ?
– Himanshu Ahuja
Nov 21 '18 at 18:16
I changed the query according to the requirement , without changing what the user was trying to do. may be he/she needs join for the further operation.
– user9639650
Nov 22 '18 at 4:43
add a comment |
SELECT t1.id,t1.name,t1.type,t2.key,t2.value
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.parent_id
where t2.key <>'active'
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
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%2f53416996%2fselect-join-where-a-record-does-not-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
7 Answers
7
active
oldest
votes
7 Answers
7
active
oldest
votes
active
oldest
votes
active
oldest
votes
Just a left join would do
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.id = t2.parent_id
AND t2.key = 'active'
WHERE t2.key IS NULL
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
add a comment |
Just a left join would do
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.id = t2.parent_id
AND t2.key = 'active'
WHERE t2.key IS NULL
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
add a comment |
Just a left join would do
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.id = t2.parent_id
AND t2.key = 'active'
WHERE t2.key IS NULL
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
Just a left join would do
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2
ON t1.id = t2.parent_id
AND t2.key = 'active'
WHERE t2.key IS NULL
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
answered Nov 21 '18 at 17:00
George JosephGeorge Joseph
1,59059
1,59059
add a comment |
add a comment |
You can use NOT EXISTS as follows:
select t1.name from table1 t1
where not exists
(
select 1 from table2 t2 where t1.id = t2.parent_id AND t2.key = 'active'
)
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
add a comment |
You can use NOT EXISTS as follows:
select t1.name from table1 t1
where not exists
(
select 1 from table2 t2 where t1.id = t2.parent_id AND t2.key = 'active'
)
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
add a comment |
You can use NOT EXISTS as follows:
select t1.name from table1 t1
where not exists
(
select 1 from table2 t2 where t1.id = t2.parent_id AND t2.key = 'active'
)
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
You can use NOT EXISTS as follows:
select t1.name from table1 t1
where not exists
(
select 1 from table2 t2 where t1.id = t2.parent_id AND t2.key = 'active'
)
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
answered Nov 21 '18 at 17:03
isaaceisaace
2,6781615
2,6781615
add a comment |
add a comment |
Try this (I think, not entirely sure I understand the linkage):
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.id = t2.parent_id and t2.key = 'active' where t2.key is null
A left join returns all elements of the first table regardless of whether they have a corresponding record in the joined table. By then including t2.key is null in the where clause, you limit it to records that only exist in the first table.
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
add a comment |
Try this (I think, not entirely sure I understand the linkage):
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.id = t2.parent_id and t2.key = 'active' where t2.key is null
A left join returns all elements of the first table regardless of whether they have a corresponding record in the joined table. By then including t2.key is null in the where clause, you limit it to records that only exist in the first table.
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
add a comment |
Try this (I think, not entirely sure I understand the linkage):
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.id = t2.parent_id and t2.key = 'active' where t2.key is null
A left join returns all elements of the first table regardless of whether they have a corresponding record in the joined table. By then including t2.key is null in the where clause, you limit it to records that only exist in the first table.
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
Try this (I think, not entirely sure I understand the linkage):
SELECT t1.name as name
FROM table1 as t1
LEFT JOIN table2 as t2 ON t1.id = t2.parent_id and t2.key = 'active' where t2.key is null
A left join returns all elements of the first table regardless of whether they have a corresponding record in the joined table. By then including t2.key is null in the where clause, you limit it to records that only exist in the first table.
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
answered Nov 21 '18 at 17:00
Chris ThompsonChris Thompson
28.9k96998
28.9k96998
add a comment |
add a comment |
This will work:
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND t2.key not like '%active%'
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
add a comment |
This will work:
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND t2.key not like '%active%'
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
add a comment |
This will work:
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND t2.key not like '%active%'
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
This will work:
SELECT t1.name as name
FROM table1 as t1
JOIN table2 as t2 ON t1.id = t2.parent_id
AND t2.key not like '%active%'
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
answered Nov 21 '18 at 17:00
VijeshVijesh
152110
152110
add a comment |
add a comment |
Select id,name,type from table1
where id Not in
(Select
parent_id from table2
group by parent_id
having key=
'active')
Better avoid join in this case I would say as subquery
would do good in this case.
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
add a comment |
Select id,name,type from table1
where id Not in
(Select
parent_id from table2
group by parent_id
having key=
'active')
Better avoid join in this case I would say as subquery
would do good in this case.
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
add a comment |
Select id,name,type from table1
where id Not in
(Select
parent_id from table2
group by parent_id
having key=
'active')
Better avoid join in this case I would say as subquery
would do good in this case.
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
Select id,name,type from table1
where id Not in
(Select
parent_id from table2
group by parent_id
having key=
'active')
Better avoid join in this case I would say as subquery
would do good in this case.
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
edited Nov 21 '18 at 18:05
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
answered Nov 21 '18 at 18:00
Himanshu AhujaHimanshu Ahuja
6942217
6942217
add a comment |
add a comment |
I think Your query is not working in this case, please try
this
SELECT name FROM table1
JOIN table2 ON table1.id =
table2.parent_id where id not IN ( SELECT parent_id
FROM
table2 WHERE `key` = 'active' )
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
answered Nov 21 '18 at 17:12
user9639650user9639650
422
Why you used join and subquery together I dont understand ?
– Himanshu Ahuja
Nov 21 '18 at 18:16
I changed the query according to the requirement , without changing what the user was trying to do. may be he/she needs join for the further operation.
– user9639650
Nov 22 '18 at 4:43
add a comment |
I think Your query is not working in this case, please try
this
SELECT name FROM table1
JOIN table2 ON table1.id =
table2.parent_id where id not IN ( SELECT parent_id
FROM
table2 WHERE `key` = 'active' )
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
answered Nov 21 '18 at 17:12
user9639650user9639650
422
Why you used join and subquery together I dont understand ?
– Himanshu Ahuja
Nov 21 '18 at 18:16
I changed the query according to the requirement , without changing what the user was trying to do. may be he/she needs join for the further operation.
– user9639650
Nov 22 '18 at 4:43
add a comment |
I think Your query is not working in this case, please try
this
SELECT name FROM table1
JOIN table2 ON table1.id =
table2.parent_id where id not IN ( SELECT parent_id
FROM
table2 WHERE `key` = 'active' )
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
answered Nov 21 '18 at 17:12
user9639650user9639650
422
I think Your query is not working in this case, please try
this
SELECT name FROM table1
JOIN table2 ON table1.id =
table2.parent_id where id not IN ( SELECT parent_id
FROM
table2 WHERE `key` = 'active' )
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
answered Nov 21 '18 at 17:12
user9639650user9639650
422
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
edited Nov 21 '18 at 19:57
Himanshu Ahuja
6942217
6942217
answered Nov 21 '18 at 17:12
user9639650user9639650
422
answered Nov 21 '18 at 17:12
user9639650user9639650
422
answered Nov 21 '18 at 17:12
user9639650user9639650
422
422
Why you used join and subquery together I dont understand ?
– Himanshu Ahuja
Nov 21 '18 at 18:16
I changed the query according to the requirement , without changing what the user was trying to do. may be he/she needs join for the further operation.
– user9639650
Nov 22 '18 at 4:43
add a comment |
Why you used join and subquery together I dont understand ?
– Himanshu Ahuja
Nov 21 '18 at 18:16
I changed the query according to the requirement , without changing what the user was trying to do. may be he/she needs join for the further operation.
– user9639650
Nov 22 '18 at 4:43
Why you used join and subquery together I dont understand ?
– Himanshu Ahuja
Nov 21 '18 at 18:16
Why you used join and subquery together I dont understand ?
– Himanshu Ahuja
Nov 21 '18 at 18:16
I changed the query according to the requirement , without changing what the user was trying to do. may be he/she needs join for the further operation.
– user9639650
Nov 22 '18 at 4:43
I changed the query according to the requirement , without changing what the user was trying to do. may be he/she needs join for the further operation.
– user9639650
Nov 22 '18 at 4:43
add a comment |
SELECT t1.id,t1.name,t1.type,t2.key,t2.value
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.parent_id
where t2.key <>'active'
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
add a comment |
SELECT t1.id,t1.name,t1.type,t2.key,t2.value
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.parent_id
where t2.key <>'active'
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
add a comment |
SELECT t1.id,t1.name,t1.type,t2.key,t2.value
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.parent_id
where t2.key <>'active'
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
SELECT t1.id,t1.name,t1.type,t2.key,t2.value
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.parent_id
where t2.key <>'active'
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
answered Nov 21 '18 at 17:14
Md Abdul MannanMd Abdul Mannan
11
11
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%2f53416996%2fselect-join-where-a-record-does-not-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
