Solved

Ordering from three tables

Posted on 2004-09-11
11
187 Views
Last Modified: 2008-03-06
I've got a mysql database with three different tables which contain user evaluation data for different tests.

The tables all contain a unique id, date field, customerid field, ordered field and payed field.
Now the list I need to create is based on the latest date first. BUT a customer can order 2 or 3 different tests on different dates, these customers still have to be bundled by the first order date occurance...
A test can be ordered from any of the three tables or a combination of these.
The list is based on all the customers which have ordered but not payed yet, all customer data will come from a fourth table based on customerid.

Can anybody help me?? It sounds simple, but I haven't been able to solve it yet....
0
Comment
Question by:shatrox
  • 5
  • 3
  • 3
11 Comments
 
LVL 4

Expert Comment

by:aratani
ID: 12035788
It just requires a SQL query that would work, could you please post the data in each talbe and some sample data and exactly what you want it to answer with?

thanks

aj
0
 

Author Comment

by:shatrox
ID: 12037621
The three main tables are Test_AK, Test_KIQ and Test_RV and the Customer table is IQ_Klanten

Test_AK
id  int(11)
klantid  int(11)
niv1  int(11)
datum  datetime
tijd  int(11)
betaald  tinyint(4)
besteldatum  datetime
besteld  tinyint(4)

Test_KIQ
id  int(11)
klantid  int(11)
niv1  int(11)
niv2  int(11)
niv3  int(11)
datum  datetime
tijd  int(11)
betaald  tinyint(4)
besteldatum  datetime
besteld  tinyint(4)

Test_RV
id  int(11)
klantid  int(11)
niv1  int(11)
niv2  int(11)
niv3  int(11)
niv4  int(11)
niv5  int(11)
niv6  int(11)
datum  datetime
tijd  int(11)
betaald  tinyint(4)
besteldatum  datetime
besteld  tinyint(4)

IQ_Klanten
nummer  int(11)
voornaam  varchar(255)
achternaam  varchar(255)
straat  varchar(255)
huisnummer  varchar(5)
toevoeging  varchar(15)
woonplaats  varchar(255)
postcode1  smallint(8)
postcode2  char(2)
land  varchar(255)
geslacht  varchar(255)
branche  varchar(255)  
beroep  varchar(255)  
occupatie  varchar(255)
opleiding  varchar(255)
email  varchar(255)
geboorteplaats  varchar(255)
geboorteland  varchar(255)  
geboortedatum  varchar(255)
wachtwoord  varchar(255)

Everything is in dutch, so I'll explain some of it: klantid is the customerid, besteldatum is the order date.
The betaald field is payed and the besteld field is ordered.. which are both boolean.
I need to show allmost all of the customerdata (IQ_Klant) from the latest orders ... and if somebody has ordered 2 or 3 tests it should be displayed immediatly too, no matter what the date of the latest ordered test is.
the nummer field in IQ_Klant is matched with the klantid field in all of the three tables...

Output should look somewhat like this: (the first one is somebody who has only ordered one test, the second has got multiple)

Customer kenmerk: 187008
Name: John Doe
Address: Housestreet 2
[etc..]
Test made: KIQ
Date: 2004-07-03 12:15:15
Right anwers level1: 5
Right anwers level2: 3
Right anwers level3: 1
Time: 813 seconds
Ordered: yes
Date ordered: 12-08-2004

----

Customer kenmerk: 190385
Name: Jane Doe
[etc..]
Test made: KIQ
Date: 2004-07-03 12:15:15
Right anwers level1: 5
Right anwers level2: 3
Right anwers level3: 1
Time: 813 seconds
Ordered: yes
Date ordered: 12-08-2004

Test made: RV
Date: 2004-07-03 12:45:05
Right anwers level1: 5
Right anwers level2: 3
Right anwers level3: 2
Right anwers level4: 2
Right anwers level5: 3
Right anwers level6: 1
Time: 811 seconds
Ordered: yes
Date ordered: 17-08-2004


0
 

Author Comment

by:shatrox
ID: 12037623
For the record ... the persons displayed here should have ordered=1 and payed=0 in one or more of the Test tables...
0
 
LVL 4

Expert Comment

by:aratani
ID: 12039104
Also, what database did you use? I assume it was mysql right.
0
 

Author Comment

by:shatrox
ID: 12039151
yes it's a mysql database
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:aratani
ID: 12039325
Oops .. i just read it was mysql sorry .. :).
0
 
LVL 9

Expert Comment

by:techtonik
ID: 12083431
So, you need to select information about customer only if (s)he ordered any test on specified date?
But you also need to mention if (s)he participated in tests on other days?
Each customer can participate only once in each test (i.e. each klantid can have only one record in each table) ?

Something like this?
SELECT * FROM `Customer`
    LEFT JOIN `Test_AK` ON `Test_AK`.`klantid` = `nummer`
    LEFT JOIN `Test_KIQ` ON `Test_KIQ`.`klantid` = `nummer`
    LEFT JOIN `Test_RV`  ON `Test_RV`.`klantid` = `nummer`
WHERE
    your_date IN (`Test_AK`.`besteldatum`,`Test_KIQ`.`besteldatum`,`Test_RV`.`besteldatum`);
0
 

Author Comment

by:shatrox
ID: 12083664
Well no not exactly, I need to create a list of customers who have ordered (to work through the payments).
Each customer can only participate once in each test, higher scores will replace earlier tries.
And somehow in the where clause should be the conditions betaald=0 and ordered=1 which counts for each of the Test tables.
I hope this helps clearing up some things
0
 
LVL 9

Accepted Solution

by:
techtonik earned 250 total points
ID: 12086573
BTW, does my query work? =) I don't have these tables, so can't test.
Well, you can try this then:
SELECT *, GREATEST(`Test_AK`.`besteldatum`,`Test_KIQ`.`besteldatum`,`Test_RV`.`besteldatum`) as 'lastdate'
FROM ...
WHERE
 (`Test_AK`.`betaald` = 0 AND `Test_AK`.`ordered` = 1) OR (`Test_KIQ`.`betaald` = 0 AND `Test_KIQ`.`ordered` = 1) OR (`Test_RV`.`betaald` = 0 AND `Test_RV`.`ordered` = 1) ORDER BY lastdate;
0
 

Author Comment

by:shatrox
ID: 12141207
Thanks for the help... the query timed out though, it took too long to search (all tables have over 150K records)
So I've decided to rebuild it a total different way
0
 
LVL 9

Expert Comment

by:techtonik
ID: 12145893
AFAIR OR operation is badly optimized in MySQL 4.x and the query should be slow on 150k records tables.

While optimizing big tables it definitely worth to add some indexes.
http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now