Select rows from related table which matches different conditions
I have a table called places which looks like this:
and another table called place_addons_saved which looks like this:
How can I write a query that relates place_addons_saved.place and places.id and searches rows that matches ONLY places which have all the conditions?
For example when I'll search for rows that have place_addon = 163, place_option = 329 and place_addon = 162, place_option = 324 will return place with id of a 80, but when I'll search for rows that have place_addon = 162, place_option = 326 and place_addon = 163, place_option = 330 the query must return nothing, because place 80 doesn't fit the 2nd condition
sql rows
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
add a comment |
I have a table called places which looks like this:
and another table called place_addons_saved which looks like this:
How can I write a query that relates place_addons_saved.place and places.id and searches rows that matches ONLY places which have all the conditions?
For example when I'll search for rows that have place_addon = 163, place_option = 329 and place_addon = 162, place_option = 324 will return place with id of a 80, but when I'll search for rows that have place_addon = 162, place_option = 326 and place_addon = 163, place_option = 330 the query must return nothing, because place 80 doesn't fit the 2nd condition
sql rows
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
add a comment |
I have a table called places which looks like this:
and another table called place_addons_saved which looks like this:
How can I write a query that relates place_addons_saved.place and places.id and searches rows that matches ONLY places which have all the conditions?
For example when I'll search for rows that have place_addon = 163, place_option = 329 and place_addon = 162, place_option = 324 will return place with id of a 80, but when I'll search for rows that have place_addon = 162, place_option = 326 and place_addon = 163, place_option = 330 the query must return nothing, because place 80 doesn't fit the 2nd condition
sql rows
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
I have a table called places which looks like this:
and another table called place_addons_saved which looks like this:
How can I write a query that relates place_addons_saved.place and places.id and searches rows that matches ONLY places which have all the conditions?
For example when I'll search for rows that have place_addon = 163, place_option = 329 and place_addon = 162, place_option = 324 will return place with id of a 80, but when I'll search for rows that have place_addon = 162, place_option = 326 and place_addon = 163, place_option = 330 the query must return nothing, because place 80 doesn't fit the 2nd condition
sql rows
sql rows
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
edited Nov 24 '18 at 16:46
marc_s
579k12911181264
579k12911181264
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
asked Nov 24 '18 at 12:32
miloszowimiloszowi
113
113
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If I understand correctly, you can use group by and having:
select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
To get all the information about the places:
select p.*
from places p
where p.id in (select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
);
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
yes yes yes, that's what I was searching for but not exactly, I also wanna make relation such asplace_addons_saved.place = places.idand I wanna get data only fromplacestable
– miloszowi
Nov 24 '18 at 12:39
You can usein,existsor ` join` to bring in columns fromplaces.
– Gordon Linoff
Nov 24 '18 at 13:08
Where should I put it?
– miloszowi
Nov 24 '18 at 13:19
second query gives me 1 row output instead of two :( (for example 1st query gives back 2 ids, but only 1 is showed in 2nd query)
– miloszowi
Nov 24 '18 at 13:35
add a comment |
This could work:
With GetResult (place,chk1,chk2) As
(
SELECT
pas.place,
sum(Case When (pas.place_addon = 163) AND (pas.place_option = 329) then 1 Else 0 End) As chk1,
sum(Case When (pas.place_addon = 162) AND (pas.place_option = 324) then 1 Else 0 End) As chk2
FROM
places AS p RIGHT OUTER JOIN
place_addons_saved AS pas ON p.id = pas.place
group by pas.place
)
Select * From GetResult Where chk1 > 0 and chk2 > 0
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
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%2f53458182%2fselect-rows-from-related-table-which-matches-different-conditions%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
If I understand correctly, you can use group by and having:
select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
To get all the information about the places:
select p.*
from places p
where p.id in (select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
);
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
yes yes yes, that's what I was searching for but not exactly, I also wanna make relation such asplace_addons_saved.place = places.idand I wanna get data only fromplacestable
– miloszowi
Nov 24 '18 at 12:39
You can usein,existsor ` join` to bring in columns fromplaces.
– Gordon Linoff
Nov 24 '18 at 13:08
Where should I put it?
– miloszowi
Nov 24 '18 at 13:19
second query gives me 1 row output instead of two :( (for example 1st query gives back 2 ids, but only 1 is showed in 2nd query)
– miloszowi
Nov 24 '18 at 13:35
add a comment |
If I understand correctly, you can use group by and having:
select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
To get all the information about the places:
select p.*
from places p
where p.id in (select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
);
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
yes yes yes, that's what I was searching for but not exactly, I also wanna make relation such asplace_addons_saved.place = places.idand I wanna get data only fromplacestable
– miloszowi
Nov 24 '18 at 12:39
You can usein,existsor ` join` to bring in columns fromplaces.
– Gordon Linoff
Nov 24 '18 at 13:08
Where should I put it?
– miloszowi
Nov 24 '18 at 13:19
second query gives me 1 row output instead of two :( (for example 1st query gives back 2 ids, but only 1 is showed in 2nd query)
– miloszowi
Nov 24 '18 at 13:35
add a comment |
If I understand correctly, you can use group by and having:
select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
To get all the information about the places:
select p.*
from places p
where p.id in (select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
);
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
If I understand correctly, you can use group by and having:
select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
To get all the information about the places:
select p.*
from places p
where p.id in (select place
from place_addons_saved
where (place_addon = 163 and place_option = 329) or
(place_addon = 162 and place_option = 324)
group by place
having count(*) = 2; -- "2" is the number of comparisons
);
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
edited Nov 24 '18 at 13:27
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
answered Nov 24 '18 at 12:34
Gordon LinoffGordon Linoff
780k35310412
780k35310412
yes yes yes, that's what I was searching for but not exactly, I also wanna make relation such asplace_addons_saved.place = places.idand I wanna get data only fromplacestable
– miloszowi
Nov 24 '18 at 12:39
You can usein,existsor ` join` to bring in columns fromplaces.
– Gordon Linoff
Nov 24 '18 at 13:08
Where should I put it?
– miloszowi
Nov 24 '18 at 13:19
second query gives me 1 row output instead of two :( (for example 1st query gives back 2 ids, but only 1 is showed in 2nd query)
– miloszowi
Nov 24 '18 at 13:35
add a comment |
yes yes yes, that's what I was searching for but not exactly, I also wanna make relation such asplace_addons_saved.place = places.idand I wanna get data only fromplacestable
– miloszowi
Nov 24 '18 at 12:39
You can usein,existsor ` join` to bring in columns fromplaces.
– Gordon Linoff
Nov 24 '18 at 13:08
Where should I put it?
– miloszowi
Nov 24 '18 at 13:19
second query gives me 1 row output instead of two :( (for example 1st query gives back 2 ids, but only 1 is showed in 2nd query)
– miloszowi
Nov 24 '18 at 13:35
yes yes yes, that's what I was searching for but not exactly, I also wanna make relation such as
place_addons_saved.place = places.id and I wanna get data only from places table– miloszowi
Nov 24 '18 at 12:39
yes yes yes, that's what I was searching for but not exactly, I also wanna make relation such as
place_addons_saved.place = places.id and I wanna get data only from places table– miloszowi
Nov 24 '18 at 12:39
You can use
in, exists or ` join` to bring in columns from places.– Gordon Linoff
Nov 24 '18 at 13:08
You can use
in, exists or ` join` to bring in columns from places.– Gordon Linoff
Nov 24 '18 at 13:08
Where should I put it?
– miloszowi
Nov 24 '18 at 13:19
Where should I put it?
– miloszowi
Nov 24 '18 at 13:19
second query gives me 1 row output instead of two :( (for example 1st query gives back 2 ids, but only 1 is showed in 2nd query)
– miloszowi
Nov 24 '18 at 13:35
second query gives me 1 row output instead of two :( (for example 1st query gives back 2 ids, but only 1 is showed in 2nd query)
– miloszowi
Nov 24 '18 at 13:35
add a comment |
This could work:
With GetResult (place,chk1,chk2) As
(
SELECT
pas.place,
sum(Case When (pas.place_addon = 163) AND (pas.place_option = 329) then 1 Else 0 End) As chk1,
sum(Case When (pas.place_addon = 162) AND (pas.place_option = 324) then 1 Else 0 End) As chk2
FROM
places AS p RIGHT OUTER JOIN
place_addons_saved AS pas ON p.id = pas.place
group by pas.place
)
Select * From GetResult Where chk1 > 0 and chk2 > 0
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
add a comment |
This could work:
With GetResult (place,chk1,chk2) As
(
SELECT
pas.place,
sum(Case When (pas.place_addon = 163) AND (pas.place_option = 329) then 1 Else 0 End) As chk1,
sum(Case When (pas.place_addon = 162) AND (pas.place_option = 324) then 1 Else 0 End) As chk2
FROM
places AS p RIGHT OUTER JOIN
place_addons_saved AS pas ON p.id = pas.place
group by pas.place
)
Select * From GetResult Where chk1 > 0 and chk2 > 0
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
add a comment |
This could work:
With GetResult (place,chk1,chk2) As
(
SELECT
pas.place,
sum(Case When (pas.place_addon = 163) AND (pas.place_option = 329) then 1 Else 0 End) As chk1,
sum(Case When (pas.place_addon = 162) AND (pas.place_option = 324) then 1 Else 0 End) As chk2
FROM
places AS p RIGHT OUTER JOIN
place_addons_saved AS pas ON p.id = pas.place
group by pas.place
)
Select * From GetResult Where chk1 > 0 and chk2 > 0
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
This could work:
With GetResult (place,chk1,chk2) As
(
SELECT
pas.place,
sum(Case When (pas.place_addon = 163) AND (pas.place_option = 329) then 1 Else 0 End) As chk1,
sum(Case When (pas.place_addon = 162) AND (pas.place_option = 324) then 1 Else 0 End) As chk2
FROM
places AS p RIGHT OUTER JOIN
place_addons_saved AS pas ON p.id = pas.place
group by pas.place
)
Select * From GetResult Where chk1 > 0 and chk2 > 0
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
answered Nov 24 '18 at 15:51
level3looperlevel3looper
69126
69126
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%2f53458182%2fselect-rows-from-related-table-which-matches-different-conditions%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
