MySQL Nested Query

I have two tabels...The first one is "files" and one row is albumname (this is an id) related to the other table of "groups".  I am thouslands of album listings in "files" what I have been doing is running a query on files, and grouping them by "albumname". this way I can get a list of the albumnames that are currently being used...

The next step, I need to join them with the "groups" table using "ID" to get the "GroupName" which are both in the groups table.....

The ultimate goal is to get a list like this

GroupName
Albumname 1
Albumname 2
Albumname 3

GroupName1
Albumname 1
Albumname 2
Albumname 3

GroupName2
Albumname 1
Albumname 2
Albumname 3
bradley525Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kim WalkerWeb Programmer/TechnicianCommented:
Your ultimate goal is to get three different tables back? One table for "GroupName" a second table for "GroupName1" and a third table for "GroupName2"? The only way I know of to do that is to execute three different queries.

It is possible to get the result back as:

GroupName,Albumname 1
GroupName,Albumname 2
GroupName,Albumname 3
GroupName1,Albumname 1
GroupName1,Albumname 2
GroupName1,Albumname 3
GroupName2,Albumname 1
GroupName2,Albumname 2
GroupName2,Albumname 3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bradley525Author Commented:
My goal is so that I can run 1 query and display the parent and then all the children below it..
0
Kim WalkerWeb Programmer/TechnicianCommented:
the parent and then all the children below it...
is a description of a table. Multiple parents with all the children below them is a description of multiple tables. I'm not aware of any way to run 1 query that returns multiple tables of results.

Are there always three children? It might be possible to do sub lists if you know in advance how many sublists to run.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

bradley525Author Commented:
There can be more than 3..There may be 10 there maybe 0...It is very dynamic
0
Ray PaseurCommented:
Please post the CREATE TABLE statements and a bit of sample data so we can set up a test, thanks.
0
Kim WalkerWeb Programmer/TechnicianCommented:
As I said in my first comment, it is possible to get the result back as:
GroupName,Albumname 1
GroupName,Albumname 2
GroupName,Albumname 3
GroupName1,Albumname 1
GroupName1,Albumname 2
GroupName1,Albumname 3
GroupName2,Albumname 1
GroupName2,Albumname 2
GroupName2,Albumname 3

Open in new window

It would be easy enough using PHP to display these results the way you've described. Are you converting the data to HTML for display in a browser? If so, how?
0
bradley525Author Commented:
I ran three queries. The was the simplest way to achieve my goal..Thanks
0
Ray PaseurCommented:
Sure would have been helpful to see the CREATE TABLE statement.  Anytime last month would have been fine.  Anyone with an understanding of SQL should be able to do this with one query.  If you ran 3 queries you're on firm ground right up until the time that the data changes and you need to change the programming to run 4 queries, etc.  You might want to think about posting a new question.  When you do, post the CREATE TABLE statement and show us the 3 queries you're using now.  We will be able to show you how to avoid the latent problems.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.