[SOLVED] Distinctrow issues when jioning three tables

[jvrothjr]

"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.

[btherl]

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.

[fenway]

Well, you're returning the ID from the first non-joined table, so every record will not be a duplicate.

[jvrothjr]

fixed the issue

 

[fenway]

fixed the issue

 

How?

[jvrothjr]

removed distinctrow

 

added group by Primary_Batch_Number