Using a Join but getting duplicate records because of it... Want one record

3 posts by 2 authors in: Forums > CMS Builder
Last Post: June 28, 2010   (RSS)

By internut - June 25, 2010

Hey all...

I'm a little stuck on how to go about this.

I have a "orders" table / section. and a "Updates" section which is used to add updates to an order (stages of the order process).

I'm trying to get a list of records from "orders" and joining the "updates" table to get the latest stage of the update.

Using code:

// load records
list($ordersRecords, $ordersMetaData) = getRecords(array(
'tableName' => 'orders',

'leftJoin' => array(
'updates' => 'ON orders.num = updates.job',
'accounts' => 'ON accounts.num = updates.updatedByUserNum',
'whip_stages' => 'ON updates.stage = whip_stages.num',
),

'debugSql' => true,


'loadUploads' => '0',
'loadCreatedBy' => '0',
'where' => 'updates.job = orders.num AND archive = 0',
'limit' => '',

));

I get a record returned from "orders" as many times as there are related records in "updates". How can I get 1 record returned and just the most recent?

DISTINCT?

Thanks much!

Re: [internut] Using a Join but getting duplicate records because of it... Want one record

By internut - June 25, 2010

Well a quick way around is I just cycle through the records and set a variable if its already went through a job number and if so not to show it again. Not the best way.

Now a new question is on record data from the join of "updates" its only pulling in:

[updates.job] => 533
[updates.stage] => 15

Shouldn't I be getting record created / updated variables returned?