Solved

Ordering from three tables

Posted on 2004-09-11
11
186 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

747 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

11 Experts available now in Live!

Get 1:1 Help Now