Hi Mike, Just a guess. Is perhaps one of the 4 records you mention a NULL record (meaning no entry) ?
Why I am asking is because in this case you may want to use INNER JOIN or even LEFT JOIN which should return also the NULL entry (incomplete datarecords). That would make it Select count(*) from orders INNER JOIN old_orders where orders.member_id='2' AND old_orders.member_id='2'; or Select count(*) from orders LEFT JOIN old_orders where orders.member_id='2' AND old_orders.member_id='2'; I hope thats what you are looking for. Let me know if I went down the wrong way ;-) Best regards Nils Valentin Tokyo/Japan 2003年 6月 26日 木曜日 05:05、Mike Morton さんは書きました: > I have searched the archives and the docs - and I cannot seem to find or > adapt an answer to this particular problem, hopefully someone out there > either knows how or can say simply that it cannot be done. > > I have two tables that have the exact same schema, orders and old_orders: > > order_id bigint(20) unsigned NOT NULL default 0, > order_reference varchar(30) default NULL, > orderplaced datetime default NULL, > member_id bigint(20) unsigned default NULL, > shplabel varchar(50) default NULL, > shpprice decimal(5,2) default NULL, > shpregion bigint(20) unsigned default NULL, > paymethod varchar(10) default NULL, > taxes text, > cod decimal(5,2) default NULL, > codlabel varchar(30) default NULL, > sh decimal(5,2) default NULL, > shlabel varchar(30) default NULL, > ordertotal decimal(10,2) default 0.00, > status varchar(30) default NULL, > trackingnum varchar(50) default NULL, > trackingcompany varchar(50) default NULL, > customernotes text, > adminnotes text, > PRIMARY KEY(order_id) > > I want to get a count of the 2 tables combined where the member id is a > specific number. I.e. Something like: > > Select count(*) from orders,old_orders where orders.member_id='2' or > old_orders.member_id='2'; > > The problem is the above returns 3 rows, the two tables combined in each of > the rows! I know that there is 4 records between the 2 tables that should > bring the result. > > I really do not want to do this in two queries, I would prefer to do it in > one.... > > Is it possible to do what I am asking? If so could someone point me in the > right direction? > > TIA > > > -- > Cheers > > Mike Morton > > **************************************************** > * > * Tel: 905-465-1263 > * Email: [EMAIL PROTECTED] > * > **************************************************** > > "Indeed, it would not be an exaggeration to describe the history of the > computer industry for the past decade as a massive effort to keep up with > Apple." > - Byte Magazine > > Given infinite time, 100 monkeys could type out the complete works of > Shakespeare. Win 98 source code? Eight monkeys, five minutes. > -- NullGrey -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
