[SOLVED] Query both sides of Many to Many relationship

[bubbles2020]

I am currently using MySQL server version 5.1.

 

My database is set up to keep track of the rosters of different organizations.

 

My table setup is as follows:

 

Groups table with primary key called GroupID, these are the names of the groups, field is varchar.

 

Individuals table with primary key called IndividualID, this is autonumber. In addition I have fields for LName, FName, Email.

 

Junction table with primary key of JunctionID, this is an autonumber, and foreign keys of GroupID and IndividualID.

 

I am utilizing two one-to-many relationships to achieve a many to many because individuals can be in more than one group.

 

I want to be able to query by GroupID and return a list of the members of who are in that group. I have achieved this with the following:

 

SELECT Groups.GroupID, Individuals.LName, Individuals.FName, Individuals.Email
FROM Individuals, Groups, Junction
WHERE Individuals.IndividualID = Junction.IndividualID
AND Groups.GroupID = Junction.GroupID
AND Groups.GroupID = "Name of Group";

 

However, from here I want to be able to tell which groups any given member is in. This is where I have run into a problem. Right now I can get this information by;

 

SELECT Groups.GroupID, Individuals.LName, Individuals.FName, Individuals.Email
FROM Individuals, Groups, Junction
WHERE Individuals.IndividualID = Junction.IndividualID
AND Groups.GroupID = Junction.GroupID
AND Individuals.IndividualID = "autonumber here";

 

However, I don't always know the autonumber of an individual and last names are not always unique. So this only works on a case by case basis. I want to have my query display, in addition to the members of a group, the groups of each member.

 

If this is in any way unclear please let me know. Thanks in advance for your help.

[kickstart]

Hi

 

Not quite sure what you want.

 

Do you want a list of Groups, and then for each group a list of members and for each of those member a list of the groups they are in. Something like

 

Needlework - Fred (Needlework, Metalwork), Burt (Needlework, Cooking)

Metalwork - Fred (Needlework, Metalwork)

Cooking - Burt (Needlework, Cooking)

 

And are you trying to get this in one SQL statement?

 

I take it at the moment you use the first select, and for each row you call the second select? As a short fix, I can see no reason why you cannot get the value of Individuals.IndividualID in the first select and use that as a key on the second select.

 

All the best

 

Keith

 

[bubbles2020]

Needlework - Fred (Needlework, Metalwork), Burt (Needlework, Cooking)

Metalwork - Fred (Needlework, Metalwork)

Cooking - Burt (Needlework, Cooking)

 

That is exactly what I'm looking for, except I only want to display one group at a time.

 

Metalwork - Fred (Needlework, Metalwork), Andy (Woodwork, Metalwork) would be one query. I would have a separate query for Cooking, Needlework, etc. My problem is displaying the groups of the members within the queried group.

 

I take it at the moment you use the first select, and for each row you call the second select?

 

I was not doing that though it seems that that could work for me. How would I go about this? If you could point me in the right direction I would greatly appreciate it.

 

Thanks!

 

 

[kickstart]

Hi

 

Not tried this, but hope it will give you the basic idea:-

 

SELECT a.GroupID, c.IndividualID, c.LName, c.FName, c.Email, GROUP_CONCAT(z.GroupID)
FROM Groups a 
INNER JOIN Junction b ON b.GroupID = a.GroupID
INNER JOIN Individuals c ON c.IndividualID = b.IndividualID
LEFT OUTER JOIN Junction z ON c.IndividualID = z.IndividualID
LEFT OUTER JOIN Groups y ON z.GroupID = y.GroupID
AND Groups.GroupID = "Name of Group"
GROUP BY a.GroupID, c.IndividualID, c.LName, c.FName, c.Email

 

Basically this should return one row per individual that is in the original group and a field that contains all the groups they are a member of.

 

The line LEFT OUTER JOIN Groups y ON z.GroupID = y.GroupID could probably be left out but I put it there as I am not certain you do not want to get other info about the groups rather than just the ID.

 

All the best

 

Keith

[bubbles2020]

Hello, thank you Keith for the prompt responses, unfortunately I did not get a chance to look over your suggestion as I had packed up for the weekend and do not have mysql at home.

 

SELECT a.GroupID, c.IndividualID, c.LName, c.FName, c.Email, GROUP_CONCAT(z.GroupID)
FROM Groups a
INNER JOIN Junction b ON b.GroupID = a.GroupID
INNER JOIN Individuals c ON c.IndividualID = b.IndividualID
LEFT OUTER JOIN Junction z ON c.IndividualID = z.IndividualID
AND a.GroupID = "Cooking"
GROUP BY a.GroupID, c.IndividualID, c.LName, c.FName, c.Email

 

Gives me what I want, but also gives me a list of the members of each group that any given individual is in from Cooking for instance:

 

Cooking (Tom (Sewing, Woodcutting), Bill(Sewing, Metalwork, Basketry))

Sewing (James, Tom, Bill)

Woodcutting (Tom)

Metalwork (Bill, Chris, Matt)

Basketry (Bill, James, Matt)

 

Was that expected? Can I filter those extra results out? Edit: the extra results being the charters of the other groups.

 

Thanks again for your help.

[bubbles2020]

I have realized that the previous code actually returns a group list of all groups I have, and lists the different groups for each member of the one specified group. In this case cooking.

[kickstart]

Hi

 

I would expect something like:-

 

Cooking - Tom - Cooking, Sewing, Woodcutting

Cooking - Bill - Cooking, Sewing, Metalwork, Basketry

Sewing - James - Sewing, Basketry

Sewing - Tom - Sewing

Sewing - Bill - Cooking, Sewing, Metalwork, Basketry

Woodcutting - Tom - Cooking, Sewing, Woodcutting

Metalwork - Bill - Cooking, Sewing, Metalwork, Basketry

Metalwork - Chris - Metalwork

Metalwork - Matt - Metalwork, Basketry

Basketry - Bill - Cooking, Sewing, Metalwork, Basketry

Basketry - James - Sewing, Basketry

Basketry - Matt - Metalwork, Basketry

 

Ie, each group, with each member, with a column for all the groups that member is a member of.

 

Is this what you want?

 

All the best

 

Keith

[bubbles2020]

I want:

 

Cooking - Tom - Cooking, Sewing, Woodcutting

Cooking - Bill - Cooking, Sewing, Metalwork, Basketry

 

to be the result of a query for Cooking.

 

SELECT a.GroupID, c.IndividualID, c.LName, c.FName, c.Email, GROUP_CONCAT(z.GroupID)
FROM Groups a
INNER JOIN Junction b ON b.GroupID = a.GroupID
INNER JOIN Individuals c ON c.IndividualID = b.IndividualID
LEFT OUTER JOIN Junction z ON c.IndividualID = z.IndividualID
LEFT OUTER JOIN Groups y ON z.GroupID = y.GroupID
AND Groups.GroupID = "Name of Group"
GROUP BY a.GroupID, c.IndividualID, c.LName, c.FName, c.Email

 

It seems like that should work; however, when I run that code I receive:

Error 1054 (42S22): Unknown column 'Groups.GroupID' in 'ON clause'

 

I tried to run:

 

SELECT a.GroupID, c.IndividualID, c.LName, c.FName, c.Email, GROUP_CONCAT(z.GroupID)
FROM Groups a
INNER JOIN Junction b ON b.GroupID = a.GroupID
INNER JOIN Individuals c ON c.IndividualID = b.IndividualID
LEFT OUTER JOIN Junction z ON c.IndividualID = z.IndividualID
LEFT OUTER JOIN Groups y ON z.GroupID = y.GroupID
AND a.GroupID = "Name of Group"
AND y.GroupID = "Name of Group"
GROUP BY a.GroupID, c.IndividualID, c.LName, c.FName, c.Email

 

But that returns results like:

 

Cooking - Tom - Cooking, Sewing, Woodcutting

Cooking - Bill - Cooking, Sewing, Metalwork, Basketry

Sewing - James - Sewing, Basketry

Sewing - Tom - Sewing

Sewing - Bill - Cooking, Sewing, Metalwork, Basketry

Woodcutting - Tom - Cooking, Sewing, Woodcutting

Metalwork - Bill - Cooking, Sewing, Metalwork, Basketry

Metalwork - Chris - Metalwork

Metalwork - Matt - Metalwork, Basketry

Basketry - Bill - Cooking, Sewing, Metalwork, Basketry

Basketry - James - Sewing, Basketry

Basketry - Matt - Metalwork, Basketry

 

I guess I am having trouble visualizing the second join, the left outer join. I feel as though I have come a long way towards my goal though, with your help. Thank you so much for your continued patience.

[kickstart]

Hi

 

You were close I think. With this:-

 

SELECT a.GroupID, c.IndividualID, c.LName, c.FName, c.Email, GROUP_CONCAT(z.GroupID)

FROM Groups a

INNER JOIN Junction b ON b.GroupID = a.GroupID

INNER JOIN Individuals c ON c.IndividualID = b.IndividualID

LEFT OUTER JOIN Junction z ON c.IndividualID = z.IndividualID

LEFT OUTER JOIN Groups y ON z.GroupID = y.GroupID

AND a.GroupID = "Name of Group"

AND y.GroupID = "Name of Group"

GROUP BY a.GroupID, c.IndividualID, c.LName, c.FName, c.Email

 

I am not sure what MySQL will try and do. I suspect it has ignored the reference to a.GroupID as it is part of the join of y and z, while y.GroupID is probably not relevant . Being a left outer join if there isn't a matching record it will just have null.

 

Try this:-

 

SELECT a.GroupID, c.IndividualID, c.LName, c.FName, c.Email, GROUP_CONCAT(z.GroupID)
FROM Groups a
INNER JOIN Junction b ON b.GroupID = a.GroupID
INNER JOIN Individuals c ON c.IndividualID = b.IndividualID
LEFT OUTER JOIN Junction z ON c.IndividualID = z.IndividualID
LEFT OUTER JOIN Groups y ON z.GroupID = y.GroupID
WHERE a.GroupID = "Name of Group"
GROUP BY a.GroupID, c.IndividualID, c.LName, c.FName, c.Email

 

All the best

 

Keith

[bubbles2020]

That is perfect!! I could have sworn I tried that.. Guess not. Anyway, thank you so much for all of your help!