March 29, 2013 at 6:04 am
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
March 29, 2013 at 6:28 am
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
March 29, 2013 at 6:57 am
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.
March 29, 2013 at 7:28 am
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
March 29, 2013 at 7:57 am
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
Viewing 5 posts - 1 through 4 (of 4 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