Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Ordering from three tables

Posted on 2004-09-11
11
Medium Priority
?
198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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
 
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 750 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

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!

Question has a verified solution.

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

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …
Suggested Courses

610 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