subquery question

[trenttc]

I want all US customers (cust.id is unique) who have spent > $30 from 1/1/06 to 12/31/06. Invoice subtotals and dates are from invoice table (cust.id is 1 to many to invoice.id). I also want to exclude any cust records with flag=1 or flag=2 in flag table (cust.id is 1 to many to flag.id). How do I write the invoice select and fit it into the rest of this?

select * from cust c
where c.country='us'
and c.id not in
(select f.id from flags f
where (f.flag_id=1)
or (f.flag_id=2))

 

[SQLSister]

I would suggest using a join to invoice table not a subquery.

I also would stop using that "not in" construction and use a left join in its place as that is generally more efficient. Also, you will want to identify which fields you need to return and specify them as select * is a poor programming practice.

"NOTHING is more important in a database than integrity." ESquared

 

[trenttc]

I'm not sure how to write a single stmt that joins all 3 tables. Any help would be appreciated.

 

[SQLSister]

http://www.somecoders.com/2006/02/explanation-of-the-different-sql-joins/

that should get you started.

"NOTHING is more important in a database than integrity." ESquared

 

[trenttc]

Maybe I can do something like this. Where do I add the left join?

select c.name from cust c
inner join
(select order_tot,order_date from orders
where sum(item_tot)>'30' and
order_date between '1/1/2006' and '12/31/'2006') o
on c.id=o.id
where c.country='us'

 

[SQLSister]

right ofter the on line and before the where clause

"NOTHING is more important in a database than integrity." ESquared