c_shelswell Posted January 14, 2007 Share Posted January 14, 2007 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? Quote Link to comment Share on other sites More sharing options...
btherl Posted January 15, 2007 Share Posted January 15, 2007 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. Quote Link to comment Share on other sites More sharing options...
c_shelswell Posted January 15, 2007 Author Share Posted January 15, 2007 yeah i tried a distinct at the start of the query but it still yielded the same results Quote Link to comment Share on other sites More sharing options...
fenway Posted January 15, 2007 Share Posted January 15, 2007 You'd want GROUP BY, not DISTINCT -- but maybe you're missing you conditions, where are the ON clauses? Quote Link to comment Share on other sites More sharing options...
c_shelswell Posted January 16, 2007 Author Share Posted January 16, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 16, 2007 Share Posted January 16, 2007 Yeah, but that's probably why you're getting multiple records... how are these tabled linked? Quote Link to comment Share on other sites More sharing options...
c_shelswell Posted January 16, 2007 Author Share Posted January 16, 2007 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 18, 2007 Share Posted January 18, 2007 Oh, now I understand... these tables aren't really "linked", you're just finding overlapping date ranges? Quote Link to comment Share on other sites More sharing options...
c_shelswell Posted January 20, 2007 Author Share Posted January 20, 2007 yeah totally just need to get anything from the tables that's in a certain range. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 22, 2007 Share Posted January 22, 2007 The question is now you define "unique" records. Quote Link to comment Share on other sites More sharing options...
c_shelswell Posted January 24, 2007 Author Share Posted January 24, 2007 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted January 24, 2007 Share Posted January 24, 2007 Probably... other the JOIN without conditions with give you M*N rows. 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.