"where" clause causing problems

[jd1515]

Hi,

I'm trying to run a select statement, but the where clause seems to be causing problems. If I seperate this into two different statements instead of using the "OR" statement, it runs ok, but I'd much rather do this with one single select statement. When I run it as is, it runs forever and eventually times out. Here's the statement:

--------------BEGIN STATEMENT-----------------

select customer.custid, customer.ownfname, customer.ownlname, customer.ownorgname

from customer, hostacct, domainorder, customerorder

where
(customer.custid = hostacct.custid and hostacct.domain LIKE 'ldsx.net')

or

(customer.custid = customerorder.custid and domainorder.ordid = customerorder.ordid and domainorder.domain LIKE 'ldsx.net')

ORDER BY customer.ownlname

---------------END STATEMENT----------------

Thanks for the help.

 

[r937]

(
select customer.custid
     , customer.ownfname
     , customer.ownlname
     , customer.ownorgname 
  from customer
inner
  join hostacct 
    on customer.custid = hostacct.custid
 where hostacct.domain = 'ldsx.net' 
)
union
(
select customer.custid
     , customer.ownfname
     , customer.ownlname
     , customer.ownorgname 
  from customer
inner
  join customerorder
    on customer.custid = customerorder.custid 
inner
  join domainorder   
    on customerorder.ordid = domainorder.ordid 
 where domainorder.domain = 'ldsx.net'
)
order 
    by customer.ownlname

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[jd1515]

Thanks r937,

I had to make one modification, but it works great now. I was getting an error about the "by customer.ownlname" at the end so I moved it before the parentheses like this:

(
select customer.custid
, customer.ownfname
, customer.ownlname
, customer.ownorgname
from customer
inner
join hostacct
on customer.custid = hostacct.custid
where hostacct.domain = 'ldsx.net'
order by customer.ownlname
)
union
(
select customer.custid
, customer.ownfname
, customer.ownlname
, customer.ownorgname
from customer
inner
join customerorder
on customer.custid = customerorder.custid
inner
join domainorder
on customerorder.ordid = domainorder.ordid
where domainorder.domain = 'ldsx.net'
order by customer.ownlname
)


I have another question now. I need to add some new conditions to this like "where ownlname = 'smith' and owncity = 'new york'". Where would I put these? Thanks again for all the help.

 

[jd1515]

nevermind, I got it. Thanks.

 

[r937]

ah, sorry about the ORDER BY, too fast cuttin' and pastin'

if you put an ORDER BY inside each query, does everything get sorted? or does it sort the separate results and then just concatenate the two sets of rows?

what you could try is ORDER BY 3 at the end

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)