SQL Select Question

[warrenk]

I have two tables in a one (master) to many (detail) relationship.  Is there anyway to select this as a one-to-one relationship?  I would use DISTINCT, but one of the fields I select in the detail file is not unique.  So even with the DISTINCT, it always selects multiple detail records for each master.

 

I just want one occurance of the detail record.

 

Thanks,

Warren

 

[btherl]

Do you mean the field in the detail file is not identical for all rows?

 

That opens the question of which of the detail rows you want returned.  Does it matter?  If not, you can use a mysql extension:

 

SELECT mastercol, detailcol FROM master JOIN detail USING (commoncol) GROUP BY mastercol

 

This will give you some random selection from the possible values of detailcol.  Note that in standard SQL this is invalid, as detailcol is not mentioned in the group by, and is not used with an aggregate function.

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

[worldworld]

I thinkn you must use GROUP BY along with DISTINCT .. 

[fenway]

Yes, it's very evil to request non-group'ed by columns in the select column list.

[btherl]

It's evil, but it's documented.  And how else is he going to get one detailcol for each mastercol when there are multiple ones present?  Maybe he wants group_concat(), which is less evil..

[fenway]

Well, he could join in this derived table.