Mixing
mysql select primary record and multiple secondary records as sorted data set
I have two tables: locations and events
Each location can have multiple events. I am successfully doing a single query to grab all the location and matching events, however, the events are being returned as a merged data-set with all of the location data. I need to somehow have each, and all, events, listed as sub-rows so I can properly process them later.
This query:
"select locations.*,events.* FROM locations,events WHERE locations.lid = events.lid AND locations.lid=1001"
Is returning merged data like:
data [array 1]
0: lid: "1001" // location info
name: "Johns Bar"
address1: "123 Main St"
...
...
eventID: "1000" // event info
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
Is it possible to grab the specific locations.lid and all matching events.lid and have the events records listed as a subset of location data.
Something that would return like:
data [array 1]
0: lid: "1001"
name: "Johns Bar"
address1: "123 Main St"
...
...
Events: [array 5]
0: lid: "1001"
eventID: "1000"
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
1: lid: "1001"
eventID: "1010"
eName: "Christmas Party"
eDate: "2018-12-17"
...
...
2: [lid: "1001",...]
3: [lid: "1001",...]
4: [lid: "1001",...]
mysql select subquery left-join
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
add a comment |
I have two tables: locations and events
Each location can have multiple events. I am successfully doing a single query to grab all the location and matching events, however, the events are being returned as a merged data-set with all of the location data. I need to somehow have each, and all, events, listed as sub-rows so I can properly process them later.
This query:
"select locations.*,events.* FROM locations,events WHERE locations.lid = events.lid AND locations.lid=1001"
Is returning merged data like:
data [array 1]
0: lid: "1001" // location info
name: "Johns Bar"
address1: "123 Main St"
...
...
eventID: "1000" // event info
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
Is it possible to grab the specific locations.lid and all matching events.lid and have the events records listed as a subset of location data.
Something that would return like:
data [array 1]
0: lid: "1001"
name: "Johns Bar"
address1: "123 Main St"
...
...
Events: [array 5]
0: lid: "1001"
eventID: "1000"
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
1: lid: "1001"
eventID: "1010"
eName: "Christmas Party"
eDate: "2018-12-17"
...
...
2: [lid: "1001",...]
3: [lid: "1001",...]
4: [lid: "1001",...]
mysql select subquery left-join
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 20 '18 at 16:11
SQL results data in tabular manner. You will need to array operations in your application code to change into required format
– Madhur Bhaiya
Nov 20 '18 at 16:12
@MadhurBhaiya - so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records? If not, then this will force me to do two queries...or is there another method?
– rolinger
Nov 20 '18 at 16:30
There is not. You could do a group concat in which case you'll end up with an enormous string. SQL is not designed to code around your master detail requirements.
– T Gray
Nov 20 '18 at 16:41
add a comment |
I have two tables: locations and events
Each location can have multiple events. I am successfully doing a single query to grab all the location and matching events, however, the events are being returned as a merged data-set with all of the location data. I need to somehow have each, and all, events, listed as sub-rows so I can properly process them later.
This query:
"select locations.*,events.* FROM locations,events WHERE locations.lid = events.lid AND locations.lid=1001"
Is returning merged data like:
data [array 1]
0: lid: "1001" // location info
name: "Johns Bar"
address1: "123 Main St"
...
...
eventID: "1000" // event info
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
Is it possible to grab the specific locations.lid and all matching events.lid and have the events records listed as a subset of location data.
Something that would return like:
data [array 1]
0: lid: "1001"
name: "Johns Bar"
address1: "123 Main St"
...
...
Events: [array 5]
0: lid: "1001"
eventID: "1000"
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
1: lid: "1001"
eventID: "1010"
eName: "Christmas Party"
eDate: "2018-12-17"
...
...
2: [lid: "1001",...]
3: [lid: "1001",...]
4: [lid: "1001",...]
mysql select subquery left-join
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
I have two tables: locations and events
Each location can have multiple events. I am successfully doing a single query to grab all the location and matching events, however, the events are being returned as a merged data-set with all of the location data. I need to somehow have each, and all, events, listed as sub-rows so I can properly process them later.
This query:
"select locations.*,events.* FROM locations,events WHERE locations.lid = events.lid AND locations.lid=1001"
Is returning merged data like:
data [array 1]
0: lid: "1001" // location info
name: "Johns Bar"
address1: "123 Main St"
...
...
eventID: "1000" // event info
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
Is it possible to grab the specific locations.lid and all matching events.lid and have the events records listed as a subset of location data.
Something that would return like:
data [array 1]
0: lid: "1001"
name: "Johns Bar"
address1: "123 Main St"
...
...
Events: [array 5]
0: lid: "1001"
eventID: "1000"
eName: "Halloween Bash"
eDate: "2018-10-31"
...
...
1: lid: "1001"
eventID: "1010"
eName: "Christmas Party"
eDate: "2018-12-17"
...
...
2: [lid: "1001",...]
3: [lid: "1001",...]
4: [lid: "1001",...]
mysql select subquery left-join
mysql select subquery left-join
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
asked Nov 20 '18 at 16:09
rolingerrolinger
9601424
9601424
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 20 '18 at 16:11
SQL results data in tabular manner. You will need to array operations in your application code to change into required format
– Madhur Bhaiya
Nov 20 '18 at 16:12
@MadhurBhaiya - so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records? If not, then this will force me to do two queries...or is there another method?
– rolinger
Nov 20 '18 at 16:30
There is not. You could do a group concat in which case you'll end up with an enormous string. SQL is not designed to code around your master detail requirements.
– T Gray
Nov 20 '18 at 16:41
add a comment |
Please don't use Old comma based Implicit joins and use Modern ExplicitJoinbased syntax
– Madhur Bhaiya
Nov 20 '18 at 16:11
SQL results data in tabular manner. You will need to array operations in your application code to change into required format
– Madhur Bhaiya
Nov 20 '18 at 16:12
@MadhurBhaiya - so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records? If not, then this will force me to do two queries...or is there another method?
– rolinger
Nov 20 '18 at 16:30
There is not. You could do a group concat in which case you'll end up with an enormous string. SQL is not designed to code around your master detail requirements.
– T Gray
Nov 20 '18 at 16:41
Please don't use Old comma based Implicit joins and use Modern Explicit
Join based syntax– Madhur Bhaiya
Nov 20 '18 at 16:11
Please don't use Old comma based Implicit joins and use Modern Explicit
Join based syntax– Madhur Bhaiya
Nov 20 '18 at 16:11
SQL results data in tabular manner. You will need to array operations in your application code to change into required format
– Madhur Bhaiya
Nov 20 '18 at 16:12
SQL results data in tabular manner. You will need to array operations in your application code to change into required format
– Madhur Bhaiya
Nov 20 '18 at 16:12
@MadhurBhaiya - so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records? If not, then this will force me to do two queries...or is there another method?
– rolinger
Nov 20 '18 at 16:30
@MadhurBhaiya - so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records? If not, then this will force me to do two queries...or is there another method?
– rolinger
Nov 20 '18 at 16:30
There is not. You could do a group concat in which case you'll end up with an enormous string. SQL is not designed to code around your master detail requirements.
– T Gray
Nov 20 '18 at 16:41
There is not. You could do a group concat in which case you'll end up with an enormous string. SQL is not designed to code around your master detail requirements.
– T Gray
Nov 20 '18 at 16:41
add a comment |
1 Answer
1
active
oldest
votes
so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records?
Yes, this is how a JOIN works.
If not, then this will force me to do two queries...or is there another method?
No, you don't have to run multiple queries.
You can fetch your result set as SQL returns it, with pairings of matching location and event rows. Make sure you sort by location in your SQL query.
Then as you fetch the rows of result, keep track of the "current" location in a variable in your client app. If you fetch a row and its location is the same as the location you had seen before, then ignore it.
Pseudocode:
sql = SELECT ... FROM location JOIN event ... ORDER BY location
execute sql
location = nil
while row = fetch():
if row[location] != location:
print location
end if
location = row[location] # for next time
print event
end while
This is a common pattern for processing SQL result sets.
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
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%2f53397056%2fmysql-select-primary-record-and-multiple-secondary-records-as-sorted-data-set%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
so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records?
Yes, this is how a JOIN works.
If not, then this will force me to do two queries...or is there another method?
No, you don't have to run multiple queries.
You can fetch your result set as SQL returns it, with pairings of matching location and event rows. Make sure you sort by location in your SQL query.
Then as you fetch the rows of result, keep track of the "current" location in a variable in your client app. If you fetch a row and its location is the same as the location you had seen before, then ignore it.
Pseudocode:
sql = SELECT ... FROM location JOIN event ... ORDER BY location
execute sql
location = nil
while row = fetch():
if row[location] != location:
print location
end if
location = row[location] # for next time
print event
end while
This is a common pattern for processing SQL result sets.
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
add a comment |
so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records?
Yes, this is how a JOIN works.
If not, then this will force me to do two queries...or is there another method?
No, you don't have to run multiple queries.
You can fetch your result set as SQL returns it, with pairings of matching location and event rows. Make sure you sort by location in your SQL query.
Then as you fetch the rows of result, keep track of the "current" location in a variable in your client app. If you fetch a row and its location is the same as the location you had seen before, then ignore it.
Pseudocode:
sql = SELECT ... FROM location JOIN event ... ORDER BY location
execute sql
location = nil
while row = fetch():
if row[location] != location:
print location
end if
location = row[location] # for next time
print event
end while
This is a common pattern for processing SQL result sets.
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
add a comment |
so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records?
Yes, this is how a JOIN works.
If not, then this will force me to do two queries...or is there another method?
No, you don't have to run multiple queries.
You can fetch your result set as SQL returns it, with pairings of matching location and event rows. Make sure you sort by location in your SQL query.
Then as you fetch the rows of result, keep track of the "current" location in a variable in your client app. If you fetch a row and its location is the same as the location you had seen before, then ignore it.
Pseudocode:
sql = SELECT ... FROM location JOIN event ... ORDER BY location
execute sql
location = nil
while row = fetch():
if row[location] != location:
print location
end if
location = row[location] # for next time
print event
end while
This is a common pattern for processing SQL result sets.
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records?
Yes, this is how a JOIN works.
If not, then this will force me to do two queries...or is there another method?
No, you don't have to run multiple queries.
You can fetch your result set as SQL returns it, with pairings of matching location and event rows. Make sure you sort by location in your SQL query.
Then as you fetch the rows of result, keep track of the "current" location in a variable in your client app. If you fetch a row and its location is the same as the location you had seen before, then ignore it.
Pseudocode:
sql = SELECT ... FROM location JOIN event ... ORDER BY location
execute sql
location = nil
while row = fetch():
if row[location] != location:
print location
end if
location = row[location] # for next time
print event
end while
This is a common pattern for processing SQL result sets.
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
answered Nov 20 '18 at 16:45
Bill KarwinBill Karwin
375k63514667
375k63514667
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%2f53397056%2fmysql-select-primary-record-and-multiple-secondary-records-as-sorted-data-set%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
Please don't use Old comma based Implicit joins and use Modern Explicit
Joinbased syntax– Madhur Bhaiya
Nov 20 '18 at 16:11
SQL results data in tabular manner. You will need to array operations in your application code to change into required format
– Madhur Bhaiya
Nov 20 '18 at 16:12
@MadhurBhaiya - so all matching events will always come back as part of a single returned row? Is there no way to get the secondary records to list as sub-rows/records? If not, then this will force me to do two queries...or is there another method?
– rolinger
Nov 20 '18 at 16:30
There is not. You could do a group concat in which case you'll end up with an enormous string. SQL is not designed to code around your master detail requirements.
– T Gray
Nov 20 '18 at 16:41