March 29, 2013 at 3:37 pm
Hi
Can some one please help with this query:
List all Customers with multiple Meters.
I'm a complete newb so forgive me if this is an easy problem to solve..
Customers – minimum 20 records
Meters – min. 30 records
Meter Readings – min. 100 readings
Invoices – 1 per Meter Reading
I have tried the following query with no success...
select *
from Customers
LEFT OUTER JOIN Meters ON Customers.idCustomers = Meters.Customers_idCustomers
where Customers.idCustomers = Customers.idCustomers;
I have also attempted SELECT CASE queries
ER diagram is attached
Thanks
March 29, 2013 at 3:45 pm
select Customers.idCustomers , count(*)
from Customers
INNER JOIN Meters ON Customers.idCustomers = Meters.Customers_idCustomers
group by Customers.idCustomers having count(*)>1
March 29, 2013 at 3:55 pm
Seraj Alam-256815 (3/29/2013)
select Customers.idCustomers , count(*)from Customers
INNER JOIN Meters ON Customers.idCustomers = Meters.Customers_idCustomers
group by Customers.idCustomers having count(*)>1
Or:
select
c.idCustomers
from
dbo.Customers c
cross apply (select count(*) as CustomerCount from dbo.Meters m where m.Customers_idCustomers = c.idCustomers) ds(CustomerCount)
where
ds.CustomerCount > 1;
March 29, 2013 at 3:59 pm
Hi Seraj
Thanks for the quick reply... That worked perfectly... Much appreciated
Thank you
March 29, 2013 at 4:09 pm
tercou1 (3/29/2013)
Hi SerajThanks for the quick reply... That worked perfectly... Much appreciated
Thank you
I'm curious, what school do you attend?
March 30, 2013 at 10:18 am
Hi Lynn
Thank you for your reply also... I'm not attending a school, I'm teaching myself about databases by solving various questions that i'm picking up on the internet... I'm from Ireland, does that cure your curiosity? :hehe:
I stumbled across this forum and I must say I love it.
Anyway thanks for your help.
Terry
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy