fairly hefty query with joins need to limit some data

[c_shelswell]

I've got a mysql query which seems to be going the job sort of. I need to join 3 tables but in this instance there isn't a matching key on the tables. I just need to select all the records on 3 tables if they're between a certain date but if there's two records in one table and only one in another i don't want it to repeat the single record.

Here's my query:
[code]
$query = "select purchases.item_price, purchases.qty, purchases.date, refunds.refund_title, refunds.product_refund,
  refunds.ramount, costs_charged.title, costs_charged.camount from purchases join costs_charged
  join refunds where purchases.date between '$dateDiff' and '$thisDate' and
  refunds.date between '$dateDiff' and '$thisDate' and costs_charged.date between '$dateDiff' and '$thisDate'";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$details[] = $row;
}

return $details;
[/code]

and this is what it's returning:
[code]Array ( [0] => Array ( [item_price] => 25.99 [qty] => 1 [date] => 2007-01-14 [refund_title] => Chris's refund title [product_refund] => yes [ramount] => 3.00 [title] => Lots of extra work completed [camount] => 100.00 ) [1] => Array ( [item_price] => 25.99 [qty] => 1 [date] => 2007-01-14 [refund_title] => misc [product_refund] => no [ramount] => 5.00 [title] => Lots of extra work completed [camount] => 100.00 ) )
[/code]

as you can see the first bit is fine but then it repeats 2 records from the first part of the array in the second part where it hasn't found a result for them.

Is there anyway to restrict this from happening so i just get all the unique records?

[btherl]

If you have duplicate rows in a table that you don't want repeated, you can use either DISTINCT or GROUP BY to eliminate them.  For example.

[code=php:0]SELECT DISTINCT purchases.item_price, purchases.qty, ....[/code]

[code=php:0]SELECT purchases.item_price, ...  GROUP BY purchases.item_price, purchases.qty, ...[/code]


For the group by approach, you will need to list all your columns again.  I would try distinct first.

[c_shelswell]

yeah i tried a distinct at the start of the query but it still yielded the same results

[fenway]

You'd want GROUP BY, not DISTINCT -- but maybe you're missing you conditions, where are the ON clauses?

[c_shelswell]

I wasn't really sure how to put the ON clauses in there as an examples i could find seemed to link it with another key on a different table whereas i don't have a key that's the same on each table i just need to get all the records within a certain date.

[fenway]

Yeah, but that's probably why you're getting multiple records... how are these tabled linked?

[c_shelswell]

well that's the thing i don't think they are.

Here's a schema of a few of the tables
[code]

CREATE TABLE gift_refund (
  date date default NULL,
  amount float(4,2) default NULL,
  title varchar(50) default NULL
) TYPE=MyISAM;


CREATE TABLE product_refund (
  date date default NULL,
  amount float(4,2) default NULL,
  title varchar(50) default NULL
) TYPE=MyISAM;


CREATE TABLE purchases (
  download_id int(10) unsigned NOT NULL auto_increment,
  media_id int(10) unsigned NOT NULL default '0',
  username varchar(30) NOT NULL default '',
  item_price float(6,2) default NULL,
  qty int(10) unsigned NOT NULL default '0',
  date date NOT NULL default '0000-00-00',
  order_status varchar(10) default NULL,
  disc_code varchar(15) default NULL,
  PRIMARY KEY  (download_id)
) TYPE=MyISAM;
[/code]

I'm not sure how i would link them as a refund might not be against one of the purchases. I just need to get all the records between a certain date range so could i link them like that? Like i said though when querying a range i need to only get distinct records.

Thanks very much for your help fenway - very much appriciated.

[fenway]

Oh, now I understand... these tables aren't really "linked", you're just finding overlapping date ranges?

[c_shelswell]

yeah totally just need to get anything from the tables that's in a certain range.

[fenway]

The question is now you define "unique" records.

[c_shelswell]

I tried putting a DISTINCT in there but that didn't work. I've currently just got a different query for each table it's working so perphaps that's the best way?

Cheers

[fenway]

Probably... other the JOIN without conditions with give you M*N rows.