Need help with this query... ER diagram attached

• tercou1

  SSC Enthusiast

  Points: 118

  More actions

  March 29, 2013 at 3:37 pm

  #272289

  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

• Seraj Alam-256815

  SSC Eights!

  Points: 804

  More actions

  March 29, 2013 at 3:45 pm

  #1602176

  select Customers.idCustomers , count(*)

  from Customers

  INNER JOIN Meters ON Customers.idCustomers = Meters.Customers_idCustomers

  group by Customers.idCustomers having count(*)>1

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  March 29, 2013 at 3:55 pm

  #1602179

  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;

• tercou1

  SSC Enthusiast

  Points: 118

  More actions

  March 29, 2013 at 3:59 pm

  #1602181

  Hi Seraj

  Thanks for the quick reply... That worked perfectly... Much appreciated

  Thank you

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  March 29, 2013 at 4:09 pm

  #1602184

  tercou1 (3/29/2013)

  ---

  Hi Seraj

  Thanks for the quick reply... That worked perfectly... Much appreciated

  Thank you

  I'm curious, what school do you attend?

• tercou1

  SSC Enthusiast

  Points: 118

  More actions

  March 30, 2013 at 10:18 am

  #1602249

  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