Frank Flynn wrote:
But if you could be more specific - send the schema and precisely what
you were looking to do I could give you an example of how to make it work.
I am still having difficulties with my workup and more help would be
appreciated .
I have two tables
CREATE TABLE `clients` (
`id` tinyint(4) NOT NULL auto_increment,
`clid` varchar(5) NOT NULL default '',
`clfname` varchar(50) NOT NULL default '',
`cllname` varchar(30) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `clid` (`clid`)
) TYPE=MyISAM COMMENT='workup table for client data';
CREATE TABLE `transactions` (
`id` tinyint(4) NOT NULL auto_increment,
`fmid` varchar(5) NOT NULL default '',
`clid` varchar(5) NOT NULL default '',
`bdate` date NOT NULL default '0000-00-00',
`instr` mediumtext,
`ecom` decimal(5,2) default NULL,
`efdate` date default '0000-00-00',
`acom` decimal(5,2) default NULL,
`afdate` date default '0000-00-00',
`transid` varchar(10) NOT NULL default '',
PRIMARY KEY (`id`,`fmid`),
UNIQUE KEY `transid` (`transid`),
KEY `fmid` (`fmid`)
) TYPE=MyISAM; COMMENT='workup table for transaction data';
What is needed is to display a list of all clients each denoted
applicably as
1.. No entry in transaction table (client's clid not in transaction table)
2.. Client not yet interviewed ( instr empty, efdate default value and
afdate default value)
3.. Client interviewed ( instr !empty, efdate=default value and
afdate=default value)
4.. Client's transaction in progress ( instr !empty, efdate!=default
value and afdate=default value)
5.. Clients transaction finalised ( instr !empty, efdate!=default value
and afdate!=default value)
What I appear to be getting is
LEFT JOIN only selects those clients with a matching clid in both tables
LEFT OUTER JOIN selects all clients with the appropriate
transaction.fmid (for example) except when the client.clid is not
matched by a transaction.clid when a spurious fmid is given.
SELECT * FROM clients LEFT OUTER JOIN transaction ON
clients.clid = transactions.clid ORDER BY clients.cllname ASC
I am obviously handling the join incorrectly.
Louise
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
