• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Ordering from three tables

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
shatrox
Asked:
shatrox
  • 5
  • 3
  • 3
1 Solution
 
arataniCommented:
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
 
shatroxAuthor Commented:
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
 
shatroxAuthor Commented:
For the record ... the persons displayed here should have ordered=1 and payed=0 in one or more of the Test tables...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
arataniCommented:
Also, what database did you use? I assume it was mysql right.
0
 
shatroxAuthor Commented:
yes it's a mysql database
0
 
arataniCommented:
Oops .. i just read it was mysql sorry .. :).
0
 
techtonikCommented:
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
 
shatroxAuthor Commented:
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
 
techtonikCommented:
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
 
shatroxAuthor Commented:
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
 
techtonikCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now