Sql and statement

[mchambers]

Hello,

i need some help writing a SQL statement.

Here is what I am looking to do:
I am trying to find a customer serviced at 2 separate locations.

so I have select customer from tablename where location is 123 and location is 150.

I get zero returns but I know there are results that should be returned.

Can anyone help me with this. greatly appreciated thanks.

 

[NiceArms]

i think you're looking for the OR syntax

SELECT customer FROM tableA WHERE location = 123 OR location = 150

if you use AND both values must be true.

/Nice

 

[HarleyQuinn]

Maybe:

select customer from tablename where location [red]=[/red] 123 [red]or[/red] location [red]=[/red] 150

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[Glasgow]

This is difficult without knowing more about the data structure/schema and what exactly you are trying to achieve. It might be as simple as replacing the 'and' in

select customer from tablename where location is 123 and location is 150

with an 'or' - i.e.

select customer from tablename where location is 123 or location is 150

Whatever, clearly if 'location' is a field in the table it can NEVER equal two values for the same record which is what the first query (if that is indeed your actual query) is trying to achieve. I would recommend you post some 'live' code.

 

[Glasgow]

Oops - I type too slow!

 

[mchambers]

Well i am looking for both location values to be true not either or. So I need location 123 to be true and 150 to be true.

SELECT mis_c_ep_rec.c_ep_bdp2, mis_c_ep_rec.c_id, mis_c_ep_rec.c_ep_ru, mis_c_ep_rec.c_ep_end, mis_client_demographics.c_fn, mis_client_demographics.c_ln, mis_zdw_reportingunit.ru_name

FROM mis_c_ep_rec INNER JOIN
mis_client_demographics ON mis_c_ep_rec.c_id = mis_client_demographics.c_id INNER JOIN
mis_zdw_reportingunit ON mis_c_ep_rec.c_ep_ru = mis_zdw_reportingunit.ru_number INNER JOIN
mis_c_eoc ON mis_c_ep_rec.c_ep_adm = mis_c_eoc.c_esa_admi
WHERE (mis_c_ep_rec.c_ep_bdp2 BETWEEN @StartDate AND @EndDate) AND (mis_c_ep_rec.c_ep_ru = '123') AND (mis_c_ep_rec.c_ep_ru = '150')
ORDER BY mis_c_ep_rec.c_id

 

[NiceArms]

(mis_c_ep_rec.c_ep_ru = '123') AND (mis_c_ep_rec.c_ep_ru = '150')

they can't both be true. based on what you have above.

what you are asking with the above statment is that the same field is 2 different things at the same time.

Try "OR" and see how you get on.

 

[simian336]

They both can't be true at the same time in the same field... you need 'or'


WHERE (mis_c_ep_rec.c_ep_bdp2 BETWEEN @StartDate AND @EndDate)
AND (mis_c_ep_rec.c_ep_ru = '123' or mis_c_ep_rec.c_ep_ru = '150')

Simi

 

[Glasgow]

A wild guess - perhaps you need a UNION of two selects - you could try

SELECT ..... WHERE datecondition and location='123'
UNION
SELECT ..... WHERE datecondition and location='150'

 

[Glasgow]

Nah - that's not right either sorry. Maybe

SELECT Customer WHERE CustomerId IN
(SELECT ..... WHERE datecondition and location='123')
AND CustomerId IN
(SELECT ..... WHERE datecondition and location='150')

 

[HarleyQuinn]

Though what is there at the moment will not work for the reasons outlined in previous posts, what I think mmarkes is saying is that they want only clients that have both 123 AND 150 within the mis_client_demographics (multiple locations per client) table, an OR statement will return clients with one or the other, or both.

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[PWise]

how about

Select customer
from customers
inner join(Select customer
from Servicelocation
where Servicelocationid=150
)Servicelocation150
on customers.customer=Servicelocation150.customer
inner join(Select customer
from Servicelocation
where Servicelocationid=123
)Servicelocation123
on customers.customer=Servicelocation123.customer

 

[HarleyQuinn]

Glasgow's third example illustrates what I meant in my last post

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[markros]

If you're only concerned for 2 specific location:

select customer from tablename where location = 123 and exists (select 1 from Customer Cust where Cust.CustomerID = Customer.CustomerID and Cust.Location = 150)

----------------------
Alternative solution - SQL Server 2005 and up

select Customer from (select Customer, count(*) over (partition by Customer) as CustomerLocationsCount from Customers where Location in (130,150)) X where CustomerLocationCount = 2 -- each customer is from 2 locations