Exclude duplicate entries from left table
I've got two tables, tbl_headings and tbl_sub_headings. tbl_sub_headings contains multiple matches to tbl_headings.I want to query the two tables and show only the heading names from the left table, tbl_headings and all the matching records from the right table, tbl_sub_headings.So if tbl_headings contained four records and each one had two sub headings, then tbl_sub_headings would have eight records.When I query with a left join, I get back eight rows with duplicate entries in the left table, tbl_headings.I would like to set the query up so that I get just the four rows on the left (maybe as an alias) and all the matching on the right.
I've tried a left inner join on the two tables using the shared column 'header_id' and grouped on column 'header_id' this still gives me the matching header name for every sub heading entry.
$stmt = $conn->prepare("SELECT
tbl_headings.heading_id AS header_id,
tbl_headings.heading_name AS header_name,
tbl_sub_headings.sub_heading_name,
tbl_sub_headings.key_stage_id
FROM tbl_headings
INNER JOIN tbl_sub_headings
ON tbl_headings.heading_id = tbl_sub_headings.heading_id
WHERE tbl_sub_headings.key_stage_id = 1
GROUP BY tbl_headings.heading_id,
");
What I want to be able to do is get only the distinct entries from the left table and all the matching entries from the right table.
mysql left-join grouping
asked Jan 20 at 10:14
DavidDavid
1827
add a comment |
I've got two tables, tbl_headings and tbl_sub_headings. tbl_sub_headings contains multiple matches to tbl_headings.I want to query the two tables and show only the heading names from the left table, tbl_headings and all the matching records from the right table, tbl_sub_headings.So if tbl_headings contained four records and each one had two sub headings, then tbl_sub_headings would have eight records.When I query with a left join, I get back eight rows with duplicate entries in the left table, tbl_headings.I would like to set the query up so that I get just the four rows on the left (maybe as an alias) and all the matching on the right.
I've tried a left inner join on the two tables using the shared column 'header_id' and grouped on column 'header_id' this still gives me the matching header name for every sub heading entry.
$stmt = $conn->prepare("SELECT
tbl_headings.heading_id AS header_id,
tbl_headings.heading_name AS header_name,
tbl_sub_headings.sub_heading_name,
tbl_sub_headings.key_stage_id
FROM tbl_headings
INNER JOIN tbl_sub_headings
ON tbl_headings.heading_id = tbl_sub_headings.heading_id
WHERE tbl_sub_headings.key_stage_id = 1
GROUP BY tbl_headings.heading_id,
");
What I want to be able to do is get only the distinct entries from the left table and all the matching entries from the right table.
mysql left-join grouping
asked Jan 20 at 10:14
DavidDavid
1827
It's not really clear what you're asking. Could you provide some sample data and expected output?
– Nick
Jan 20 at 11:43
add a comment |
I've got two tables, tbl_headings and tbl_sub_headings. tbl_sub_headings contains multiple matches to tbl_headings.I want to query the two tables and show only the heading names from the left table, tbl_headings and all the matching records from the right table, tbl_sub_headings.So if tbl_headings contained four records and each one had two sub headings, then tbl_sub_headings would have eight records.When I query with a left join, I get back eight rows with duplicate entries in the left table, tbl_headings.I would like to set the query up so that I get just the four rows on the left (maybe as an alias) and all the matching on the right.
I've tried a left inner join on the two tables using the shared column 'header_id' and grouped on column 'header_id' this still gives me the matching header name for every sub heading entry.
$stmt = $conn->prepare("SELECT
tbl_headings.heading_id AS header_id,
tbl_headings.heading_name AS header_name,
tbl_sub_headings.sub_heading_name,
tbl_sub_headings.key_stage_id
FROM tbl_headings
INNER JOIN tbl_sub_headings
ON tbl_headings.heading_id = tbl_sub_headings.heading_id
WHERE tbl_sub_headings.key_stage_id = 1
GROUP BY tbl_headings.heading_id,
");
What I want to be able to do is get only the distinct entries from the left table and all the matching entries from the right table.
mysql left-join grouping
asked Jan 20 at 10:14
DavidDavid
1827
I've got two tables, tbl_headings and tbl_sub_headings. tbl_sub_headings contains multiple matches to tbl_headings.I want to query the two tables and show only the heading names from the left table, tbl_headings and all the matching records from the right table, tbl_sub_headings.So if tbl_headings contained four records and each one had two sub headings, then tbl_sub_headings would have eight records.When I query with a left join, I get back eight rows with duplicate entries in the left table, tbl_headings.I would like to set the query up so that I get just the four rows on the left (maybe as an alias) and all the matching on the right.
I've tried a left inner join on the two tables using the shared column 'header_id' and grouped on column 'header_id' this still gives me the matching header name for every sub heading entry.
$stmt = $conn->prepare("SELECT
tbl_headings.heading_id AS header_id,
tbl_headings.heading_name AS header_name,
tbl_sub_headings.sub_heading_name,
tbl_sub_headings.key_stage_id
FROM tbl_headings
INNER JOIN tbl_sub_headings
ON tbl_headings.heading_id = tbl_sub_headings.heading_id
WHERE tbl_sub_headings.key_stage_id = 1
GROUP BY tbl_headings.heading_id,
");
What I want to be able to do is get only the distinct entries from the left table and all the matching entries from the right table.
mysql left-join grouping
mysql left-join grouping
asked Jan 20 at 10:14
DavidDavid
1827
asked Jan 20 at 10:14
DavidDavid
1827
asked Jan 20 at 10:14
DavidDavid
1827
asked Jan 20 at 10:14
DavidDavid
1827
asked Jan 20 at 10:14
DavidDavid
1827
1827
It's not really clear what you're asking. Could you provide some sample data and expected output?
– Nick
Jan 20 at 11:43
add a comment |
It's not really clear what you're asking. Could you provide some sample data and expected output?
– Nick
Jan 20 at 11:43
It's not really clear what you're asking. Could you provide some sample data and expected output?
– Nick
Jan 20 at 11:43
It's not really clear what you're asking. Could you provide some sample data and expected output?
– Nick
Jan 20 at 11:43
add a comment |
0
active
oldest
votes
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%2f54275418%2fexclude-duplicate-entries-from-left-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f54275418%2fexclude-duplicate-entries-from-left-table%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
It's not really clear what you're asking. Could you provide some sample data and expected output?
– Nick
Jan 20 at 11:43