Select query with several many-many left joins yeilding unwanted duplicate data

[randhuck]

I'm making a complex set of queries trying to get what I want... here's an example schema

[quote][b]Firms[/b]
[i]Firms contain customers[/i]
[table]
[tr][td][u]id[/u][/td][td][u]name[/u][/td][td][u]description[/u][/td][/tr]
[tr][td]1[/td][td]ACME[/td][td]Fine provider of dynamite, springs, magnets, and glue.[/td][/tr]
[tr][td]2[/td][td]Eat at Joe's[/td][td]The most cliched restaurant in town[/td][/tr]
[/table][/quote]

[quote][b]Employees[/b]
[i]Employees contain specialties and positions[/i]
id
firm_id
name
description
[table]
[tr][td][u]id[/u][/td][td][u]firm_id[/u][/td][td][u]name[/u][/td][td][u]description[/u][/td][/tr]
[tr][td]1[/td][td]1[/td][td]Matt[/td][td]NULL[/td][/tr]
[tr][td]2[/td][td]1[/td][td]Molly[/td][td]Insert Description Here[/td][/tr]
[tr][td]3[/td][td]1[/td][td]The King[/td][td]I love specialties and positions[/td][/tr]
[tr][td]4[/td][td]2[/td][td]Joe[/td][td]Joe's kewl[/td][/tr]
[/table][/quote]

[quote][b]EmployeeSpecialties[/b]
[i]m-n[/i]
[table]
[tr][td][u]employee_id[/u][/td][td][u]specialty_id[/u][/td][/tr]
[tr][td]1[/td][td]1[/td][/tr]
[tr][td]1[/td][td]2[/td][/tr]
[tr][td]2[/td][td]2[/td][/tr]
[tr][td]2[/td][td]3[/td][/tr]
[tr][td]3[/td][td]1[/td][/tr]
[tr][td]3[/td][td]2[/td][/tr]
[tr][td]3[/td][td]3[/td][/tr]
[tr][td]3[/td][td]4[/td][/tr]
[/table][/quote]

[quote][b]EmployeePositions[/b]
[i]m-n[/i]
[table]
[tr][td][u]employee_id[/u][/td][td][u]position_id[/u][/td][/tr]
[tr][td]1[/td][td]1[/td][/tr]
[tr][td]2[/td][td]1[/td][/tr]
[tr][td]2[/td][td]2[/td][/tr]
[tr][td]3[/td][td]1[/td][/tr]
[tr][td]3[/td][td]2[/td][/tr]
[tr][td]3[/td][td]3[/td][/tr]
[tr][td]3[/td][td]4[/td][/tr]
[tr][td]4[/td][td]4[/td][/tr]
[/table][/quote]

[quote][b]Specialties[/b]
[i]Property of Employees[/i]
[table]
[tr][td][u]id[/u][/td][td][u]name[/u][/td][td][u]description[/u][/td][/tr]
[tr][td]1[/td][td]Customer Service[/td][td]Kicks out customers with slippers instead of steel toed shoes[/td][/tr]
[tr][td]2[/td][td]Electronics[/td][td]Televisions, Radios[/td][/tr]
[tr][td]3[/td][td]Media[/td][td]DVD players, CD players, iPods[/td][/tr]
[tr][td]4[/td][td]Making Money[/td][td]NULL[/td][/tr]
[/table][/quote]

[quote][b]Positions[/b]
[i]Property of Employees[/i]
[table]
[tr][td][u]id[/u][/td][td][u]name[/u][/td][td][u]description[/u][/td][/tr]
[tr][td]1[/td][td]Sales Rep[/td][td]Answers customers' questions and kicks them out after they pay[/td][/tr]
[tr][td]2[/td][td]Supervisor[/td][td]Makes sure the sales reps kick the customers out gracefully[/td][/tr]
[tr][td]3[/td][td]Manager[/td][td]Counts money[/td][/tr]
[tr][td]4[/td][td]Owner[/td][td]Makes money[/td][/tr]
[/table][/quote]

So, what I've got here are employees that have two many-many relationships, and I'd like to have query that gives me a result like this:

[i]17 rows[/i]
[quote][table]
[tr][td][u]Firm[/u][/td][td][u]Employee[/u][/td][td][u]Specialty[/u][/td][td][u]Position[/u][/td][/tr]
[tr][td]ACME[/td][td]Matt[/td][td]Customer Service[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]Matt[/td][td]Electronics[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]Matt[/td][td]NULL[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]Electronics[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]Media[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]NULL[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]NULL[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Customer Service[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Electronics[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Media[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Making Money[/td][td]NULL[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]NULL[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]NULL[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]NULL[/td][td]Manager[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]NULL[/td][td]Owner[/td][/tr]
[tr][td]Eat at Joe's[/td][td]Joe[/td][td]NULL[/td][td]Owner[/td][/tr]
[/table][/quote]

I tried this query:

[quote][tt]
SELECT
`Firms`.`name` AS `Firm`,
`Employees`.`name` AS `Employee`,
`Specialties`.`name` AS `Specialty`,
`Positions`.`name` AS `Position`

FROM `Firms`
LEFT JOIN `Employees` ON (`Employees`.`firm_id` = `Firms`.`id`)
LEFT JOIN `EmployeeSpecialites` ON (`Employees`.`id` = `EmployeeSpecialties`.`employee_id`)
LEFT JOIN `Specialties` ON (`Specialties`.`id` = `EmployeeSpecialties`.`specialty_id`)
LEFT JOIN `EmployeePositions` ON (`Employees`.`id` = `EmployeePositions`.`employee_id`)
LEFT JOIN `Positions` ON (`Positions`.`id` = `EmployeePositions`.`position_id`)
[/tt][/quote]

And instead what I got was something like this:

[quote][i]24 rows[/i]
[table]
[tr][td][u]Firm[/u][/td][td][u]Employee[/u][/td][td][u]Specialty[/u][/td][td][u]Position[/u][/td][/tr]
[tr][td]ACME[/td][td]Matt[/td][td]Customer Service[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]Matt[/td][td]Electronics[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]Electronics[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]Electronics[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]Media[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]Molly[/td][td]Media[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Customer Service[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Customer Service[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Customer Service[/td][td]Manager[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Customer Service[/td][td]Owner[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Electronics[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Electronics[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Electronics[/td][td]Manager[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Electronics[/td][td]Owner[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Media[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Media[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Media[/td][td]Manager[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Media[/td][td]Owner[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Making Money[/td][td]Sales Rep[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Making Money[/td][td]Supervisor[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Making Money[/td][td]Manager[/td][/tr]
[tr][td]ACME[/td][td]The King[/td][td]Making Money[/td][td]Owner[/td][/tr]
[tr][td]Eat at Joe's[/td][td]Joe[/td][td]NULL[/td][td]Owner[/td][/tr]
[/table][/quote]

Considering my actual schema has several more many-many relationships and that those relationships ARE many-many instead of 'few-few' in this example, this kind of query would never work for me.

Is there query with temp tables, joining, grouping, or 'whereing' I could use to cut down on all of these rows? The system will also be deployed on several servers, all of which I'll have little choice of what version of MySQL they will be using, and if possible, I'd like to keep it compatible with version 3.23.

I'd appreciate any help.

[btherl]

It looks to me like you want two seperate queries.. you can always UNION the results together afterwards.

In your expected output, you never have both Specialty and Position set in the same row.  But the purpose of join is to merge data into the same row.  If you want seperate rows, you can do two queries followed by a union, leaving null values as placeholders for the rows which aren't involved in that particular query.

[code=php:0]SELECT
`Firms`.`name` AS `Firm`,
`Employees`.`name` AS `Employee`,
NULL AS `Specialty`,
`Positions`.`name` AS `Position`

...

SELECT
`Firms`.`name` AS `Firm`,
`Employees`.`name` AS `Employee`,
`Specialties`.`name` AS `Specialty`,
NULL AS `Position`[code=php:0]

[randhuck]

Ah, sounds simple enough...

Unfortunately MySQL 3.23 doesn't have unions, so I had to just make a temp table, and insert the select queries into it, which had the same result.

Thanks for you help. :)

[artacus]

MySQL 3.23? Time for you to find a new host.

[randhuck]

Well, as I have said earlier, I'm developing something that will run on servers of unknown configuration, and would like to be as compatible as reasonably possible. It's not my host, but the host of others.