to join or not to join .

[mschultem]

hi,
i want to join three tables and do some queries from the result. thought that i got the concept but i get weird results ...

would like to end up with a list of email (from the demography table) qualified by pid, experiment_id and finished_Exp in invited. finally only those should be selected that are not already listed in table payed with a pid and experiment_id.
simply said: show me the email addresses that were invited for a experiment_id but were not payed yet.

$query="SELECT demography.email, invited.pid FROM demography, invited WHERE invited.finishedExp = '1' AND invited.experiment_id='1' AND invited.pid NOT IN (SELECT distinct payed.pid from payed WHERE payed.experiment_id='1')";

CREATE TABLE `demography` (
`pid` char(12) NOT NULL,
`email` char(100) default NULL,
...

CREATE TABLE `invited` (
`id` int(11) NOT NULL auto_increment,
`pid` char(12) NOT NULL,
`experiment_id` varchar(20) NOT NULL,
`finishedExp` tinyint(1) default NULL,
...

CREATE TABLE `payed` (
`id` int(11) NOT NULL auto_increment,
`pid` char(12) NOT NULL,
`experiment_id` varchar(20) NOT NULL,
`paymentdate` datetime NOT NULL,
`email` varchar(100) NOT NULL,
...

an insight on this is appreciated!

thanks
m

[darrengeorge]

Hi, Im tired and as such havent looked very hard, but experiment_ID is an integer and you are comparing it to a string:

experiment_id = '1'

to

experiment_id = 1

Not sure if that will make a difference, but might help until someone can take a proper look..

[darrengeorge]

Also Id look for an alternative to using a sub-query for this as it seems like an expensive solution to a simple task..

At the moment you are saying

'select all the guys who arent in (these guys)'

A simpler solution IMO would be

'select all the guys whos payed.experiment_ID = 0'

That to me would be simpler but may need a slight modification to your table/s (ie if guys are only added to table payed if they have payed)

Just a thought, could well be wrong :)

[darrengeorge]

Sorry, I just read through again and seems I didnt quite get what you were saying until now - sorry!

[mcibor]

You forgot to join demography with invited. It should be sth like:

$query="SELECT demography.email, invited.pid FROM demography, invited WHERE demography.pid=invited.pid AND invited.finishedExp = '1' AND invited.experiment_id='1' AND invited.pid NOT IN (SELECT distinct payed.pid from payed WHERE payed.experiment_id='1')";

There should be none influence what so ever whether it's string or int, but to say the truth storing id as varchar is a bit strange :)

Regards
Michal

[mschultem]

i missed the demogrpahy.pid=invited.pid part.
it works fine now.

about the '' for the 1 - i use a variable there, just replaced it for posting ...
and of course the varchar has to be changed to INT ...

thanks for all your comments
m