David, You left out the Rodopi version making it a little harder to help with designing this query.
Anyway, I have a tip for all of you here which should make writing queries much easier: Start by visiting http://www.rodopi-faq.com and clicking on Database Models. Choose the one nearest to your current version making sure you choose the correct database (AbacBill holds most of the data). Now as your query wants Customer information we will start with that table, so click on tables and then Customers in the table list. You will see the Customers Table Definition appear in the content pane, and towards the top off that is a link 'Switch to graphical View'. Click this link to view the table and its relationships graphically. As we need the information from the Customers table we start with pulling that out: --- SELECT * FROM Customers --- The next item you may require is the Plan information, and you can see from the graphical view that the Plans table is linked to the Customers table. It is actually easy to see that the Plans table has the field CustomerID which links to the CustomerID in the Customers table, in some cases you might need to query the data in both tables to figure out which field is related (you can also look at the information shown under Foreign Keys and Indexes in the Text View for the tables). So, knowing we want Plans that is linked via CustomerID we have the next part of our query: --- SELECT * FROM Customers JOIN Plans ON Customers.CustomerID = Plans.CustomerID --- We would probably want the PlanInfo so we'll add that next: --- SELECT * FROM Customer JOIN Plans ON Customers.CustomerID = Plans.CustomerID JOIN PlanInfo ON Plans.PlanInfoID = PlanInfo.PlanInfoID --- At this point you probably have so many fields it is becoming hard to read your data so you should eliminate any unnecessary fields from the results: --- SELECT Customer.RegNumber, Customer.FirstName, Customer.LastName, Customer.Company, Plans.StartDate, PlanInfo.Description FROM Customer JOIN Plans ON Customers.CustomerID = Plans.CustomerID JOIN PlanInfo ON Plans.PlanInfoID = PlanInfo.PlanInfoID ----- Next we should look at which records are appearing that we need to eliminate to build a WHERE clause. In this example we'll eliminate all the closed Plans: --- SELECT Customer.RegNumber, Customer.FirstName, Customer.LastName, Customer.Company, Plans.StartDate, PlanInfo.Description FROM Customer JOIN Plans ON Customers.CustomerID = Plans.CustomerID JOIN PlanInfo ON Plans.PlanInfoID = PlanInfo.PlanInfoID WHERE Plans.Closed = 0 ----- Hopefully this provides you with a little insight into creating queries that whilst being basic provide the details you need. Once you get the hang of doing this you could start learning more elements of a select statement such as GROUP BY, and look at optimizing the JOINS using LEFT, RIGHT, INNER and OUTER. Also, this shows people that I did the Database Models originally for my own benefit, I just decided to share these since they were done (very pain staking as well since each image in the graphical view was captured from a SQL Diagram, edited and saved, after which an Image Map was create for the web). --- If the opposite of pro is con, then what must be the opposite of progress? (--------------------------------) {(((((( ( Suneel Jhangiani ) /_ _ ) ( Technical Director ) ( . . ) ( Inter-Computer Technology Ltd. ) ( / ) (----------------------------------oOOo------------oOOo----) ( 40 James Street Tel: +44 (0) 20 7486 9601 ) ( London W1U 1EU Fax: +44 (0) 7050 678 978 ) ( United Kingdom Email: [EMAIL PROTECTED] ) ( Website: http://www.inctech.com ) (----------------------------------------------------------) ________________________________________ From: David Bauman [mailto:[EMAIL PROTECTED] Sent: 20 January 2004 02:23 To: [EMAIL PROTECTED] Subject: [Rodopi] SQL Query assistance Greetings, � Im trying to create a query to pull every single active customer, sorted with: Plan, MRC, City If anyone can assist, it would be greatly appreciated.� Im not sure which tables need to be queried, and Im not great at linking multiple tables together. � David Bauman http://www.anet.com --------------------- To Leave the Rodopi mail list send a message to [EMAIL PROTECTED] with the word LEAVE as the message body. Please also visit the Rodopi FAQ at http://www.rodopi-faq.com ---------------------
