Nils:
Not exactly... :)
I know for sure that there are 4 matching records - when I do:
(Select count(*) from orders where member_id='2') union (select count(*)
from old_orders where member_id='2')
I get:
+----------+
| count(*) |
+----------+
| 1 |
| 3 |
+----------+
Which is fine - if I want to iterate through the results in my php script to
add these up.
I know - it is not a great deal of overhead to do that - BUT if there is a
way to get that in 1 row, that is the ideal solution.
I have beat my brains out over this, perhaps unnecessarily if it cannot be
done...... But until I hear from someone that is more of a guru than I say
'Mike it cannot be done that way' then I will continue to hold up hope... :)
On 6/25/03 8:29 PM, "Nils Valentin" <[EMAIL PROTECTED]> wrote:
> 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
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
