ER diagram and related query!

Question

ER diagram and related query!

tercou1

SSC Enthusiast

Points: 118
More actions
March 29, 2013 at 6:04 am

#275788

Hi
I am getting 1000 rows returned from this Query: (see attached ER diagram)
select *
from Customers,Meters, MeterReadings, Invoice
where Customers.idCustomers = 2;
Obviously that's wrong!! so i'm thinking that maybe the ER diagram is set up wrong... for example, I should get two metersIDs back for customerID 2, with the associated readings and invoices but thats not the case, Where am i going wrong??
Customers – minimum 20 records
Meters – min. 30 records
Meter Readings – min. 100 readings
Invoices – 1 per Meter Reading
The query is:
For any specific Customer list each of their Meters, Meter Readings and Invoices
If you haven't guessed already I'm a complete newb
Hope you can help... even if it's just to tell me that the ER diagram is set up correctly!! If need be I can attach the data that I used to populate the tables
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

join criteria is critical in any sql to make any sense from multiple tables.

because you are using the old style joins, and never identify the join criteria, you are getting a cross join or cartesian product: all rows from four tables time their # of rows. Customers * Meters * MeterReadings * Invoice

change to the standard join syntax,a nd it would make more sense:

i guessed on the column names

select *

from Customers

LEFT OUTER JOIN Meters ON Customers.CustomerID=Meters.CustomerID

LEFT OUTER JOIN MeterReadings ON Meters.MeterID = MeterReadings.MeterID

LEFT OUTER JOIN Invoice ON Customers.CustomerID=Invoice.CustomerID

where Customers.idCustomers = 2;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tercou1 SSC Enthusiast Points: 118 More actions · Answer 2

Hi Lowell

Thanks for the reply.

I've tried that query and got the following error:

Error Code: 1054. Unknown column 'Meters.idCustomers' in 'on clause'

select *

from Customers

LEFT OUTER JOIN Meters ON Customers.idCustomers = Meters.idCustomers

LEFT OUTER JOIN MeterReadings ON Meters.idMeters = MeterReadings.idMeters

LEFT OUTER JOIN Invoice ON Customers.idCustomers = Invoice.idCustomers

where Customers.idCustomers = 2;

Any ideas? I've tried googling it but to no avail.

Lowell SSC Guru Points: 323482 More actions · Answer 3

look at the ER diagram i think you posted. the Meters table doesn't have that column, so you cannot try to join it in your query like that;

the error here is specific to you and your tables, you'll have to look at your diagram, and see that each arrow joining two tables together are the LEFT JOIN/ ON criteria you need.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tercou1 SSC Enthusiast Points: 118 More actions · Answer 4

Thankyou very much for your help Lowell, it's working perfect now...

select *

from Customers

LEFT OUTER JOIN Meters ON Customers.idCustomers = Meters.Customers_idCustomers

LEFT OUTER JOIN MeterReadings ON Meters.idMeters = MeterReadings.Meters_idMeters

LEFT OUTER JOIN Invoice ON Customers.idCustomers = Invoice.Customers_idCustomers

where Customers.idCustomers = 10;

Really appreciate it.. Thanks