jvrothjr Posted April 17, 2007 Share Posted April 17, 2007 "SELECT distinctrow tblblockbatches.Block_Batch_ID, tblblockbatches.Primary_Batch_Number, tblblockbatches.Secondary_Batch_Number, tblitemmaster.Block_Description, tblitemmaster.Part_Number FROM (tblblockbatches LEFT JOIN tblbmr_test_and_inspect ON tblblockbatches.Block_Batch_ID = tblbmr_test_and_inspect.Block_Batch_ID) INNER JOIN tblitemmaster ON tblblockbatches.Item_ID = tblitemmaster.Item_ID WHERE (((tblbmr_test_and_inspect.Block_Batch_ID) Is Null) and ((tblblockbatches.Obsolete)='0'))" That is my query string I am having issues with. It does return the data I wish but also returns Dups. Some times there are two Batches per ID so if a batch was split in the operation. Then there would be two records but only wish to display one in the select box. I have tried Distinct and Group by wish no luck. Been working on this for a few hours with no luck and help with be great. Quote Link to comment Share on other sites More sharing options...
btherl Posted April 17, 2007 Share Posted April 17, 2007 Can you show your group by attempt? It basically depends on what condition you want your rows to be distinct by. If you want only one row for each Block_Batch_ID, and you don't care which row it is, then use "GROUP BY Block_Batch_ID" and you will get what you're looking for. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 20, 2007 Share Posted April 20, 2007 Well, you're returning the ID from the first non-joined table, so every record will not be a duplicate. Quote Link to comment Share on other sites More sharing options...
jvrothjr Posted April 20, 2007 Author Share Posted April 20, 2007 fixed the issue Quote Link to comment Share on other sites More sharing options...
fenway Posted April 20, 2007 Share Posted April 20, 2007 fixed the issue How? Quote Link to comment Share on other sites More sharing options...
jvrothjr Posted April 21, 2007 Author Share Posted April 21, 2007 removed distinctrow added group by Primary_Batch_Number Quote Link to comment 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.