How to get distinct result in mysql?
1
I want to find all files which are common based on v_id
I have three tables
detail
----------------------------
|d_id |v_id |d_name
|--------|--------|---------
|1 |1 |detail_A
|2 |1 |detail_B
|3 |2 |detail_C
file
--------------------
|f_id |fname
|---------|---------
|1 | file_W
|2 | file_X
|3 | file_Y
|4 | file_Z
mapping
---------------------------------
|m_id | v_id |d_id |f_id
|-------|---------|--------|-----
|1 | 1 |1 |1
|2 | 1 |1 |2
|3 | 1 |2 |1
|4 | 1 |2 |3
|5 | 2 |3 |2
|6 | 2 |3 |4
the query I tried is below
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN ('1','2')
GROUP BY m.f_id
HAVING COUNT(m.f_id) >=2
I want result as file_X as my objective is to find common file based on v_id
but using above query I am getting file_X,file_W
expected output
-----------
|f_id |fname
------|---
|2 |file_X
mysql distinct
asked Nov 20 '18 at 10:31
NishitNishit
83
add a comment |
1
I want to find all files which are common based on v_id
I have three tables
detail
----------------------------
|d_id |v_id |d_name
|--------|--------|---------
|1 |1 |detail_A
|2 |1 |detail_B
|3 |2 |detail_C
file
--------------------
|f_id |fname
|---------|---------
|1 | file_W
|2 | file_X
|3 | file_Y
|4 | file_Z
mapping
---------------------------------
|m_id | v_id |d_id |f_id
|-------|---------|--------|-----
|1 | 1 |1 |1
|2 | 1 |1 |2
|3 | 1 |2 |1
|4 | 1 |2 |3
|5 | 2 |3 |2
|6 | 2 |3 |4
the query I tried is below
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN ('1','2')
GROUP BY m.f_id
HAVING COUNT(m.f_id) >=2
I want result as file_X as my objective is to find common file based on v_id
but using above query I am getting file_X,file_W
expected output
-----------
|f_id |fname
------|---
|2 |file_X
mysql distinct
asked Nov 20 '18 at 10:31
NishitNishit
83
1
Add your expected output (as per the given sample data) in tabular format to the question
– Madhur Bhaiya
Nov 20 '18 at 10:32
Your data appears to not be normalized.
– Tim Biegeleisen
Nov 20 '18 at 10:34
@TimBiegeleisen. Can you explain how it should be?
– Nishit
Nov 20 '18 at 10:47
1
Two of your tables maintain relationships betweend_idandv_id. Ideally, only a single mapping table should be keeping track of this.
– Tim Biegeleisen
Nov 20 '18 at 10:48
there are more data and content in detail table liked_nameand other columns that I have not displayed here...that will be duplicate in mapping table...that's the reason I have kept it separate.
– Nishit
Nov 20 '18 at 11:00
add a comment |
1
1
1
I want to find all files which are common based on v_id
I have three tables
detail
----------------------------
|d_id |v_id |d_name
|--------|--------|---------
|1 |1 |detail_A
|2 |1 |detail_B
|3 |2 |detail_C
file
--------------------
|f_id |fname
|---------|---------
|1 | file_W
|2 | file_X
|3 | file_Y
|4 | file_Z
mapping
---------------------------------
|m_id | v_id |d_id |f_id
|-------|---------|--------|-----
|1 | 1 |1 |1
|2 | 1 |1 |2
|3 | 1 |2 |1
|4 | 1 |2 |3
|5 | 2 |3 |2
|6 | 2 |3 |4
the query I tried is below
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN ('1','2')
GROUP BY m.f_id
HAVING COUNT(m.f_id) >=2
I want result as file_X as my objective is to find common file based on v_id
but using above query I am getting file_X,file_W
expected output
-----------
|f_id |fname
------|---
|2 |file_X
mysql distinct
asked Nov 20 '18 at 10:31
NishitNishit
83
I want to find all files which are common based on v_id
I have three tables
detail
----------------------------
|d_id |v_id |d_name
|--------|--------|---------
|1 |1 |detail_A
|2 |1 |detail_B
|3 |2 |detail_C
file
--------------------
|f_id |fname
|---------|---------
|1 | file_W
|2 | file_X
|3 | file_Y
|4 | file_Z
mapping
---------------------------------
|m_id | v_id |d_id |f_id
|-------|---------|--------|-----
|1 | 1 |1 |1
|2 | 1 |1 |2
|3 | 1 |2 |1
|4 | 1 |2 |3
|5 | 2 |3 |2
|6 | 2 |3 |4
the query I tried is below
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN ('1','2')
GROUP BY m.f_id
HAVING COUNT(m.f_id) >=2
I want result as file_X as my objective is to find common file based on v_id
but using above query I am getting file_X,file_W
expected output
-----------
|f_id |fname
------|---
|2 |file_X
mysql distinct
mysql distinct
asked Nov 20 '18 at 10:31
NishitNishit
83
asked Nov 20 '18 at 10:31
NishitNishit
83
edited Nov 20 '18 at 10:54
Nishit
asked Nov 20 '18 at 10:31
NishitNishit
83
asked Nov 20 '18 at 10:31
NishitNishit
83
asked Nov 20 '18 at 10:31
NishitNishit
83
83
1
Add your expected output (as per the given sample data) in tabular format to the question
– Madhur Bhaiya
Nov 20 '18 at 10:32
Your data appears to not be normalized.
– Tim Biegeleisen
Nov 20 '18 at 10:34
@TimBiegeleisen. Can you explain how it should be?
– Nishit
Nov 20 '18 at 10:47
1
Two of your tables maintain relationships betweend_idandv_id. Ideally, only a single mapping table should be keeping track of this.
– Tim Biegeleisen
Nov 20 '18 at 10:48
there are more data and content in detail table liked_nameand other columns that I have not displayed here...that will be duplicate in mapping table...that's the reason I have kept it separate.
– Nishit
Nov 20 '18 at 11:00
add a comment |
1
Add your expected output (as per the given sample data) in tabular format to the question
– Madhur Bhaiya
Nov 20 '18 at 10:32
Your data appears to not be normalized.
– Tim Biegeleisen
Nov 20 '18 at 10:34
@TimBiegeleisen. Can you explain how it should be?
– Nishit
Nov 20 '18 at 10:47
1
Two of your tables maintain relationships betweend_idandv_id. Ideally, only a single mapping table should be keeping track of this.
– Tim Biegeleisen
Nov 20 '18 at 10:48
there are more data and content in detail table liked_nameand other columns that I have not displayed here...that will be duplicate in mapping table...that's the reason I have kept it separate.
– Nishit
Nov 20 '18 at 11:00
1
1
Add your expected output (as per the given sample data) in tabular format to the question
– Madhur Bhaiya
Nov 20 '18 at 10:32
Add your expected output (as per the given sample data) in tabular format to the question
– Madhur Bhaiya
Nov 20 '18 at 10:32
Your data appears to not be normalized.
– Tim Biegeleisen
Nov 20 '18 at 10:34
Your data appears to not be normalized.
– Tim Biegeleisen
Nov 20 '18 at 10:34
@TimBiegeleisen. Can you explain how it should be?
– Nishit
Nov 20 '18 at 10:47
@TimBiegeleisen. Can you explain how it should be?
– Nishit
Nov 20 '18 at 10:47
1
1
Two of your tables maintain relationships between
d_id and v_id. Ideally, only a single mapping table should be keeping track of this.– Tim Biegeleisen
Nov 20 '18 at 10:48
Two of your tables maintain relationships between
d_id and v_id. Ideally, only a single mapping table should be keeping track of this.– Tim Biegeleisen
Nov 20 '18 at 10:48
there are more data and content in detail table like
d_name and other columns that I have not displayed here...that will be duplicate in mapping table...that's the reason I have kept it separate.– Nishit
Nov 20 '18 at 11:00
there are more data and content in detail table like
d_name and other columns that I have not displayed here...that will be duplicate in mapping table...that's the reason I have kept it separate.– Nishit
Nov 20 '18 at 11:00
add a comment |
1 Answer
1
active
oldest
votes
1
You can simply count the unique value of v_id. So if there are two v_id values; in order to find the files which are available to both the v_id values, you can count their unique occurrences, and it should be 2.
Also, if v_id is integer, you can use IN (1,2) instead of IN ('1', '2'). Try:
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN (1,2)
GROUP BY m.f_id
HAVING COUNT(DISTINCT m.v_id) = 2
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thanks a lot Madhur... It is working as expected...
– Nishit
Nov 20 '18 at 11:53
@NishitKotak is v_id integer or string in your database ?
– Madhur Bhaiya
Nov 20 '18 at 11:53
It is integer in my database.
– Nishit
Nov 20 '18 at 11:56
@NishitKotak check the updated answer, Dont use('1', '2').
– Madhur Bhaiya
Nov 20 '18 at 11:57
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%2f53391034%2fhow-to-get-distinct-result-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can simply count the unique value of v_id. So if there are two v_id values; in order to find the files which are available to both the v_id values, you can count their unique occurrences, and it should be 2.
Also, if v_id is integer, you can use IN (1,2) instead of IN ('1', '2'). Try:
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN (1,2)
GROUP BY m.f_id
HAVING COUNT(DISTINCT m.v_id) = 2
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thanks a lot Madhur... It is working as expected...
– Nishit
Nov 20 '18 at 11:53
@NishitKotak is v_id integer or string in your database ?
– Madhur Bhaiya
Nov 20 '18 at 11:53
It is integer in my database.
– Nishit
Nov 20 '18 at 11:56
@NishitKotak check the updated answer, Dont use('1', '2').
– Madhur Bhaiya
Nov 20 '18 at 11:57
add a comment |
1
You can simply count the unique value of v_id. So if there are two v_id values; in order to find the files which are available to both the v_id values, you can count their unique occurrences, and it should be 2.
Also, if v_id is integer, you can use IN (1,2) instead of IN ('1', '2'). Try:
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN (1,2)
GROUP BY m.f_id
HAVING COUNT(DISTINCT m.v_id) = 2
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
Thanks a lot Madhur... It is working as expected...
– Nishit
Nov 20 '18 at 11:53
@NishitKotak is v_id integer or string in your database ?
– Madhur Bhaiya
Nov 20 '18 at 11:53
It is integer in my database.
– Nishit
Nov 20 '18 at 11:56
@NishitKotak check the updated answer, Dont use('1', '2').
– Madhur Bhaiya
Nov 20 '18 at 11:57
add a comment |
1
1
1
You can simply count the unique value of v_id. So if there are two v_id values; in order to find the files which are available to both the v_id values, you can count their unique occurrences, and it should be 2.
Also, if v_id is integer, you can use IN (1,2) instead of IN ('1', '2'). Try:
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN (1,2)
GROUP BY m.f_id
HAVING COUNT(DISTINCT m.v_id) = 2
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
You can simply count the unique value of v_id. So if there are two v_id values; in order to find the files which are available to both the v_id values, you can count their unique occurrences, and it should be 2.
Also, if v_id is integer, you can use IN (1,2) instead of IN ('1', '2'). Try:
SELECT f.f_id, f.fname
FROM mapping AS m
INNER JOIN detail AS d ON d.d_id=m.d_id
INNER JOIN file AS f ON m.f_id=f.f_id
WHERE m.v_id IN (1,2)
GROUP BY m.f_id
HAVING COUNT(DISTINCT m.v_id) = 2
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
edited Nov 20 '18 at 11:54
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 20 '18 at 11:35
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
Thanks a lot Madhur... It is working as expected...
– Nishit
Nov 20 '18 at 11:53
@NishitKotak is v_id integer or string in your database ?
– Madhur Bhaiya
Nov 20 '18 at 11:53
It is integer in my database.
– Nishit
Nov 20 '18 at 11:56
@NishitKotak check the updated answer, Dont use('1', '2').
– Madhur Bhaiya
Nov 20 '18 at 11:57
add a comment |
Thanks a lot Madhur... It is working as expected...
– Nishit
Nov 20 '18 at 11:53
@NishitKotak is v_id integer or string in your database ?
– Madhur Bhaiya
Nov 20 '18 at 11:53
It is integer in my database.
– Nishit
Nov 20 '18 at 11:56
@NishitKotak check the updated answer, Dont use('1', '2').
– Madhur Bhaiya
Nov 20 '18 at 11:57
Thanks a lot Madhur... It is working as expected...
– Nishit
Nov 20 '18 at 11:53
Thanks a lot Madhur... It is working as expected...
– Nishit
Nov 20 '18 at 11:53
@NishitKotak is v_id integer or string in your database ?
– Madhur Bhaiya
Nov 20 '18 at 11:53
@NishitKotak is v_id integer or string in your database ?
– Madhur Bhaiya
Nov 20 '18 at 11:53
It is integer in my database.
– Nishit
Nov 20 '18 at 11:56
It is integer in my database.
– Nishit
Nov 20 '18 at 11:56
@NishitKotak check the updated answer, Dont use
('1', '2').– Madhur Bhaiya
Nov 20 '18 at 11:57
@NishitKotak check the updated answer, Dont use
('1', '2').– Madhur Bhaiya
Nov 20 '18 at 11:57
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%2f53391034%2fhow-to-get-distinct-result-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
1
Add your expected output (as per the given sample data) in tabular format to the question
– Madhur Bhaiya
Nov 20 '18 at 10:32
Your data appears to not be normalized.
– Tim Biegeleisen
Nov 20 '18 at 10:34
@TimBiegeleisen. Can you explain how it should be?
– Nishit
Nov 20 '18 at 10:47
1
Two of your tables maintain relationships between
d_idandv_id. Ideally, only a single mapping table should be keeping track of this.– Tim Biegeleisen
Nov 20 '18 at 10:48
there are more data and content in detail table like
d_nameand other columns that I have not displayed here...that will be duplicate in mapping table...that's the reason I have kept it separate.– Nishit
Nov 20 '18 at 11:00