Re: Highlighting data selected from one table that appear in another
| From: | boclair | Date: | Fri, 02 Sep 2005 00:47:41 +0000 |
| Subject: | Re: Highlighting data selected from one table that appear in another | ||
| References: | 1 2 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-40199@lists.php.net to get a copy of this message | ||
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 ASCI am obviously handling the join incorrectly. Louise