warrenk Posted February 15, 2007 Share Posted February 15, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/38576-sql-select-question/ Share on other sites More sharing options...
btherl Posted February 15, 2007 Share Posted February 15, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/38576-sql-select-question/#findComment-185221 Share on other sites More sharing options...
worldworld Posted February 15, 2007 Share Posted February 15, 2007 I thinkn you must use GROUP BY along with DISTINCT .. Quote Link to comment https://forums.phpfreaks.com/topic/38576-sql-select-question/#findComment-185341 Share on other sites More sharing options...
fenway Posted February 15, 2007 Share Posted February 15, 2007 Yes, it's very evil to request non-group'ed by columns in the select column list. Quote Link to comment https://forums.phpfreaks.com/topic/38576-sql-select-question/#findComment-185571 Share on other sites More sharing options...
btherl Posted February 16, 2007 Share Posted February 16, 2007 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.. Quote Link to comment https://forums.phpfreaks.com/topic/38576-sql-select-question/#findComment-186053 Share on other sites More sharing options...
fenway Posted February 16, 2007 Share Posted February 16, 2007 Well, he could join in this derived table. Quote Link to comment https://forums.phpfreaks.com/topic/38576-sql-select-question/#findComment-186210 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.