JOIN Statement problem

[johnnyb]

Hi,

I have 2 tables, 'ballet' and 'ballet_creator' ballet creator is really a reference table for a 3-way relationship but for the moment I don't need to join beyond these two tables.

So I have this join:

"SELECT ballet.*, ballet_creator.* FROM ballet INNER JOIN ballet_creator ON ballet.BALLET_ID=ballet_creator.BALLET_ID WHERE ballet_creator.PROFESSION_ID=2";

and it's working fine. The problem is I have multiple records in 'ballet_creator' with the same BALLET_ID. This is true for some, but not all, BALLET_ID's. When I complete the join I want my result set to include only one row for the ballets that have two records in 'ballet_creator'. I hope I've been clear enough on what I want.

Also, if it helps, in the 'ballet_creator' table when there are multiple records with the same BALLET_ID there is a true/false field in which the one primary record marked true.

If someone knows how to do this that would be great. I've been pounding my head against a wall for several hours now and am running out of ideas.

John

[sad69]

If there aren't multiple rows in the ballet_creator table with the same ballet_id, is that single row also marked as the primary (to set to true)?

In that case, I think you can do the following:

"SELECT ballet.*, ballet_creator.* FROM ballet INNER JOIN ballet_creator ON ballet.BALLET_ID=ballet_creator.BALLET_ID WHERE ballet_creator.PROFESSION_ID=2 AND ballet_creator.PRIMARY=true";

or something like that...

Sadiq.

[johnnyb]

it's not also marked as primary, although I suppose it would make sense for me to do that - not hard at all, it's not that big of a table - and just set the update script to do so in the future.

[MsZola]

Did you try "SELECT DISTINCT"?

It might be worthwhile giving it a whirl. Worst-case scenario, it isn't what you need and you'll have only lost ten minutes.

[Kiwi]

Why complicate it?

Surely this would work:

SELECT
b.*,
c.*
FROM
ballet AS b,
ballet_creator AS c
WHERE
b.BALLET_ID= c.BALLET_ID AND
c.PROFESSION_ID = 2 AND
c.<flag> = TRUE
;
Just replace <flag> the name of your flag that tells you which of the creators is the primary for that ballet. If you must use JOIN (only necessary if the child table is very large), then:
SELECT
b.*,
c.*
FROM ballet AS b
INNER JOIN ballet_creator AS c
ON b.BALLET_ID= c.BALLET_ID
WHERE
c.PROFESSION_ID = 2 AND
c.<flag> = TRUE
;
The difference is when the sql engine excludes the records that breadk the WHERE clause: before or after building the result set. In mySql JOIN excludes them before building the results, so it is a little faster with very large tables.

[johnnyb]

I figured it out - thanks for the help.

JB