Jump to content

fairly hefty query with joins need to limit some data


c_shelswell

Recommended Posts

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.