RE: help with query: select customers that ARO NOT in orders table

2006-10-23 Thread Jerry Schwartz 
Is a sub-select more efficient than an outer join?

SELECT cust_id FROM customers LEFT JOIN orders on customers.cust_id =
  orders.cust_id WHERE orders.cust_id IS NULL;

Or am I missing something (as usual)?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


 -Original Message-
 From: Waldemar Jankowski [mailto:[EMAIL PROTECTED]
 Sent: Friday, October 20, 2006 1:53 PM
 To: [EMAIL PROTECTED]
 Cc: mysql@lists.mysql.com
 Subject: Re: help with query: select customers that ARO NOT
 in orders table

 On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:

  hi to all,
  I have table customers (PK cust_id)
  I have table orders (PK order_id, FK cust_id)
 
  I need query that will selecct all customers from
 'customers' they don't
  have any order, there is not their cust_id in 'orders'.
 
  couls somebody help me?
 
  thanks.
 
  -afan

 I think the most explicit way is with a sub select:

 select cust_id from customers where
 cust_id not in
   (select cust_id from orders);

 -w

  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

help with query: select customers that ARO NOT in orders table

2006-10-20 Thread afan 
hi to all,
I have table customers (PK cust_id)
I have table orders (PK order_id, FK cust_id)

I need query that will selecct all customers from 'customers' they don't
have any order, there is not their cust_id in 'orders'.

couls somebody help me?

thanks.

-afan

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with query: select customers that ARO NOT in orders table

2006-10-20 Thread Waldemar Jankowski 

On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:


hi to all,
I have table customers (PK cust_id)
I have table orders (PK order_id, FK cust_id)

I need query that will selecct all customers from 'customers' they don't
have any order, there is not their cust_id in 'orders'.

couls somebody help me?

thanks.

-afan


I think the most explicit way is with a sub select:

select cust_id from customers where
cust_id not in
(select cust_id from orders);

-w


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with query: select customers that ARO NOT in orders table

2006-10-20 Thread afan 
Ok. Just found I gave wrong info. To make my life easier, the person who
created db named cust_id in 'orders' table as SoldTo

[EMAIL PROTECTED]

in this case,
select cust_id from customers
where cust_id not in
   (select Soldto from orders);

will not work

:(






 On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:

 hi to all,
 I have table customers (PK cust_id)
 I have table orders (PK order_id, FK cust_id)

 I need query that will selecct all customers from 'customers' they don't
 have any order, there is not their cust_id in 'orders'.

 couls somebody help me?

 thanks.

 -afan

 I think the most explicit way is with a sub select:

 select cust_id from customers where
 cust_id not in
   (select cust_id from orders);

 -w

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: help with query: select customers that ARO NOT in orders table

2006-10-20 Thread Waldemar Jankowski 

On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:


Ok. Just found I gave wrong info. To make my life easier, the person who
created db named cust_id in 'orders' table as SoldTo

[EMAIL PROTECTED]

in this case,
select cust_id from customers
where cust_id not in
  (select Soldto from orders);

will not work

:(



Maybe I'm not understanding you but as long as Soldto is cust_id just with 
a different column name it will still work.


The subquery will result in a list of values and then the NOT IN clause 
will check if your cust_id is not in that list.


For more info check out:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html





On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:


hi to all,
I have table customers (PK cust_id)
I have table orders (PK order_id, FK cust_id)

I need query that will selecct all customers from 'customers' they don't
have any order, there is not their cust_id in 'orders'.

couls somebody help me?

thanks.

-afan


I think the most explicit way is with a sub select:

select cust_id from customers where
cust_id not in
(select cust_id from orders);

-w


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]








--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]