Get row from one table, that has its id in other table with condition(don't know how to name it)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm afraid, that I've asked my question in title wrong...
I have two tables in database, that are related to this question
Products
- product_id
- name
- etc.
Categories
- id
- product_id
- category_id
I need to create two queries.
One to get products id that have product_category = 1 AND product_category = 2 AND product_category = 3 [...].
And the second one is to get products id that have product_category = 1 OR product_category = 2 OR product_category = 3 [...]
I've tried everything that I've learned, but with no effects
What kind of query should I use?
EDIT:
Here's some sample rows and expected results
Products
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
------------
3 | Prod_5
------------
4 | Prod_6
Categories
ID | ProductID | CategoryID
----------------------------
1 | 1 | 101
----------------------------
2 | 1 | 102
----------------------------
3 | 1 | 103
----------------------------
4 | 2 | 102
----------------------------
5 | 2 | 103
----------------------------
6 | 2 | 108
----------------------------
7 | 3 | 121
----------------------------
8 | 3 | 111
I Want to select Name from Products table. This products should be in Categories table, and have specific CategoryID
Example1:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 103 AND `Categories`.`CategoryID` = 102)
This should Result in:
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
Example2:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 108 OR `Categories`.`CategoryID` = 121)
This should Result in:
ID | Name
------------
2 | Prod_4
------------
3 | Prod_5
mysql sql
edited Nov 23 '18 at 2:09
dwir182
1,447619
asked Nov 22 '18 at 21:45
user3013821user3013821
104
add a comment |
I'm afraid, that I've asked my question in title wrong...
I have two tables in database, that are related to this question
Products
- product_id
- name
- etc.
Categories
- id
- product_id
- category_id
I need to create two queries.
One to get products id that have product_category = 1 AND product_category = 2 AND product_category = 3 [...].
And the second one is to get products id that have product_category = 1 OR product_category = 2 OR product_category = 3 [...]
I've tried everything that I've learned, but with no effects
What kind of query should I use?
EDIT:
Here's some sample rows and expected results
Products
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
------------
3 | Prod_5
------------
4 | Prod_6
Categories
ID | ProductID | CategoryID
----------------------------
1 | 1 | 101
----------------------------
2 | 1 | 102
----------------------------
3 | 1 | 103
----------------------------
4 | 2 | 102
----------------------------
5 | 2 | 103
----------------------------
6 | 2 | 108
----------------------------
7 | 3 | 121
----------------------------
8 | 3 | 111
I Want to select Name from Products table. This products should be in Categories table, and have specific CategoryID
Example1:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 103 AND `Categories`.`CategoryID` = 102)
This should Result in:
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
Example2:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 108 OR `Categories`.`CategoryID` = 121)
This should Result in:
ID | Name
------------
2 | Prod_4
------------
3 | Prod_5
mysql sql
edited Nov 23 '18 at 2:09
dwir182
1,447619
asked Nov 22 '18 at 21:45
user3013821user3013821
104
2
@user3013821 are you sure product_id in Categories table? I think Product table has CategoryId.
– Zeki Gumus
Nov 22 '18 at 21:50
@a_horse_with_no_name I'm using MySQL
– user3013821
Nov 22 '18 at 22:26
@LukStorms I've updated my question with some samples
– user3013821
Nov 22 '18 at 22:26
add a comment |
I'm afraid, that I've asked my question in title wrong...
I have two tables in database, that are related to this question
Products
- product_id
- name
- etc.
Categories
- id
- product_id
- category_id
I need to create two queries.
One to get products id that have product_category = 1 AND product_category = 2 AND product_category = 3 [...].
And the second one is to get products id that have product_category = 1 OR product_category = 2 OR product_category = 3 [...]
I've tried everything that I've learned, but with no effects
What kind of query should I use?
EDIT:
Here's some sample rows and expected results
Products
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
------------
3 | Prod_5
------------
4 | Prod_6
Categories
ID | ProductID | CategoryID
----------------------------
1 | 1 | 101
----------------------------
2 | 1 | 102
----------------------------
3 | 1 | 103
----------------------------
4 | 2 | 102
----------------------------
5 | 2 | 103
----------------------------
6 | 2 | 108
----------------------------
7 | 3 | 121
----------------------------
8 | 3 | 111
I Want to select Name from Products table. This products should be in Categories table, and have specific CategoryID
Example1:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 103 AND `Categories`.`CategoryID` = 102)
This should Result in:
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
Example2:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 108 OR `Categories`.`CategoryID` = 121)
This should Result in:
ID | Name
------------
2 | Prod_4
------------
3 | Prod_5
mysql sql
edited Nov 23 '18 at 2:09
dwir182
1,447619
asked Nov 22 '18 at 21:45
user3013821user3013821
104
I'm afraid, that I've asked my question in title wrong...
I have two tables in database, that are related to this question
Products
- product_id
- name
- etc.
Categories
- id
- product_id
- category_id
I need to create two queries.
One to get products id that have product_category = 1 AND product_category = 2 AND product_category = 3 [...].
And the second one is to get products id that have product_category = 1 OR product_category = 2 OR product_category = 3 [...]
I've tried everything that I've learned, but with no effects
What kind of query should I use?
EDIT:
Here's some sample rows and expected results
Products
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
------------
3 | Prod_5
------------
4 | Prod_6
Categories
ID | ProductID | CategoryID
----------------------------
1 | 1 | 101
----------------------------
2 | 1 | 102
----------------------------
3 | 1 | 103
----------------------------
4 | 2 | 102
----------------------------
5 | 2 | 103
----------------------------
6 | 2 | 108
----------------------------
7 | 3 | 121
----------------------------
8 | 3 | 111
I Want to select Name from Products table. This products should be in Categories table, and have specific CategoryID
Example1:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 103 AND `Categories`.`CategoryID` = 102)
This should Result in:
ID | Name
------------
1 | Prod_3
------------
2 | Prod_4
Example2:
Select `ID`, `Name` From `Products`
WHERE IN `Categories`.`ProductID` = `Products`.`ID`
AND (`Categories`.`CategoryID` = 108 OR `Categories`.`CategoryID` = 121)
This should Result in:
ID | Name
------------
2 | Prod_4
------------
3 | Prod_5
mysql sql
mysql sql
edited Nov 23 '18 at 2:09
dwir182
1,447619
asked Nov 22 '18 at 21:45
user3013821user3013821
104
edited Nov 23 '18 at 2:09
dwir182
1,447619
asked Nov 22 '18 at 21:45
user3013821user3013821
104
edited Nov 23 '18 at 2:09
dwir182
1,447619
edited Nov 23 '18 at 2:09
dwir182
1,447619
edited Nov 23 '18 at 2:09
dwir182
1,447619
1,447619
asked Nov 22 '18 at 21:45
user3013821user3013821
104
asked Nov 22 '18 at 21:45
user3013821user3013821
104
asked Nov 22 '18 at 21:45
user3013821user3013821
104
104
2
@user3013821 are you sure product_id in Categories table? I think Product table has CategoryId.
– Zeki Gumus
Nov 22 '18 at 21:50
@a_horse_with_no_name I'm using MySQL
– user3013821
Nov 22 '18 at 22:26
@LukStorms I've updated my question with some samples
– user3013821
Nov 22 '18 at 22:26
add a comment |
2
@user3013821 are you sure product_id in Categories table? I think Product table has CategoryId.
– Zeki Gumus
Nov 22 '18 at 21:50
@a_horse_with_no_name I'm using MySQL
– user3013821
Nov 22 '18 at 22:26
@LukStorms I've updated my question with some samples
– user3013821
Nov 22 '18 at 22:26
2
2
@user3013821 are you sure product_id in Categories table? I think Product table has CategoryId.
– Zeki Gumus
Nov 22 '18 at 21:50
@user3013821 are you sure product_id in Categories table? I think Product table has CategoryId.
– Zeki Gumus
Nov 22 '18 at 21:50
@a_horse_with_no_name I'm using MySQL
– user3013821
Nov 22 '18 at 22:26
@a_horse_with_no_name I'm using MySQL
– user3013821
Nov 22 '18 at 22:26
@LukStorms I've updated my question with some samples
– user3013821
Nov 22 '18 at 22:26
@LukStorms I've updated my question with some samples
– user3013821
Nov 22 '18 at 22:26
add a comment |
3 Answers
3
active
oldest
votes
Sounds like you need something like that:
select distinct p.ID, p.Name
from Products P
inner join Categories C on c.ProductId = p.ID
where c.CategoryID in (108, 121)
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
add a comment |
You can use group by and having to get the products that have all the categories:
select p.ID, p.Name
from products p join
categories c
on c.productid = p.id
where c.categoryid in (102, 103)
group by p.id, p.name
having count(*) = 2; -- number of items in the `in` list
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
add a comment |
I ended up with:
SELECT a.product_id FROM `product_categories` a
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1655) a1 ON
a1.product_id = a.product_id
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1605) a2 ON
[...]
a2.product_id = a.product_id
WHERE a.category_id = 1575
answered Nov 24 '18 at 10:13
user3013821user3013821
104
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%2f53438307%2fget-row-from-one-table-that-has-its-id-in-other-table-with-conditiondont-know%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
Sounds like you need something like that:
select distinct p.ID, p.Name
from Products P
inner join Categories C on c.ProductId = p.ID
where c.CategoryID in (108, 121)
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
add a comment |
Sounds like you need something like that:
select distinct p.ID, p.Name
from Products P
inner join Categories C on c.ProductId = p.ID
where c.CategoryID in (108, 121)
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
add a comment |
Sounds like you need something like that:
select distinct p.ID, p.Name
from Products P
inner join Categories C on c.ProductId = p.ID
where c.CategoryID in (108, 121)
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
Sounds like you need something like that:
select distinct p.ID, p.Name
from Products P
inner join Categories C on c.ProductId = p.ID
where c.CategoryID in (108, 121)
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
answered Nov 23 '18 at 2:28
Mike TwcMike Twc
1,2461713
1,2461713
add a comment |
add a comment |
You can use group by and having to get the products that have all the categories:
select p.ID, p.Name
from products p join
categories c
on c.productid = p.id
where c.categoryid in (102, 103)
group by p.id, p.name
having count(*) = 2; -- number of items in the `in` list
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
add a comment |
You can use group by and having to get the products that have all the categories:
select p.ID, p.Name
from products p join
categories c
on c.productid = p.id
where c.categoryid in (102, 103)
group by p.id, p.name
having count(*) = 2; -- number of items in the `in` list
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
add a comment |
You can use group by and having to get the products that have all the categories:
select p.ID, p.Name
from products p join
categories c
on c.productid = p.id
where c.categoryid in (102, 103)
group by p.id, p.name
having count(*) = 2; -- number of items in the `in` list
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
You can use group by and having to get the products that have all the categories:
select p.ID, p.Name
from products p join
categories c
on c.productid = p.id
where c.categoryid in (102, 103)
group by p.id, p.name
having count(*) = 2; -- number of items in the `in` list
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
answered Nov 23 '18 at 2:54
Gordon LinoffGordon Linoff
799k37320426
799k37320426
add a comment |
add a comment |
I ended up with:
SELECT a.product_id FROM `product_categories` a
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1655) a1 ON
a1.product_id = a.product_id
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1605) a2 ON
[...]
a2.product_id = a.product_id
WHERE a.category_id = 1575
answered Nov 24 '18 at 10:13
user3013821user3013821
104
add a comment |
I ended up with:
SELECT a.product_id FROM `product_categories` a
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1655) a1 ON
a1.product_id = a.product_id
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1605) a2 ON
[...]
a2.product_id = a.product_id
WHERE a.category_id = 1575
answered Nov 24 '18 at 10:13
user3013821user3013821
104
add a comment |
I ended up with:
SELECT a.product_id FROM `product_categories` a
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1655) a1 ON
a1.product_id = a.product_id
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1605) a2 ON
[...]
a2.product_id = a.product_id
WHERE a.category_id = 1575
answered Nov 24 '18 at 10:13
user3013821user3013821
104
I ended up with:
SELECT a.product_id FROM `product_categories` a
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1655) a1 ON
a1.product_id = a.product_id
INNER JOIN (SELECT * FROM product_categories WHERE category_id = 1605) a2 ON
[...]
a2.product_id = a.product_id
WHERE a.category_id = 1575
answered Nov 24 '18 at 10:13
user3013821user3013821
104
answered Nov 24 '18 at 10:13
user3013821user3013821
104
answered Nov 24 '18 at 10:13
user3013821user3013821
104
answered Nov 24 '18 at 10:13
user3013821user3013821
104
104
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%2f53438307%2fget-row-from-one-table-that-has-its-id-in-other-table-with-conditiondont-know%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
2
@user3013821 are you sure product_id in Categories table? I think Product table has CategoryId.
– Zeki Gumus
Nov 22 '18 at 21:50
@a_horse_with_no_name I'm using MySQL
– user3013821
Nov 22 '18 at 22:26
@LukStorms I've updated my question with some samples
– user3013821
Nov 22 '18 at 22:26