[PHP-DB] Re: Highlighting data selected from one table that appear in another

[boclair]

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 '-00-00',
 `instr` mediumtext,
 `ecom` decimal(5,2) default NULL,
 `efdate` date default '-00-00',
 `acom` decimal(5,2) default NULL,
 `afdate` date default '-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

[PHP-DB] Re: Highlighting data selected from one table that appear in another

[Frank Flynn]

I'm thinking that you need a "LEFT JOIN" (see the mysql  
documentation) to do this.  Basically LEFT JOIN (or RIGHT JOIN) allow  
you to select all the rows from the table on the left side even if  
they have no corresponding row on the right side (the values of the  
columns in the select that belong to the right hand table will be  
NULL where there is no row to join to).


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.


Good Luck,
Frank


On Sep 1, 2005, at 5:00 AM, [EMAIL PROTECTED] wrote:


From: boclair <[EMAIL PROTECTED]>
Date: September 1, 2005 4:50:05 AM PDT
To: "php-db@lists.php.net" 
Subject: Highlighting data selected from one table that appear in  
another



Content-Type: multipart/alternative;
boundary="070700030402040702060708"
X-Authentication-Info: Submitted using SMTP AUTH PLAIN at  
omta05ps.mx.bigpond.com from [138.130.220.111] using ID boclair at  
Thu, 1 Sep 2005 11:49:59 +


This is a multi-part message in MIME format.
--070700030402040702060708
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

mysql 4.0.25
php 4.3.1

I seem to be unable to solve this problem without some help.

A webpage is to list all the clients in a  mysql " Client" table.   
Each

client has an an allocated clid.

Those clients who are listed, identified by their allocated clid, in a
"Transactions" are to be highlighted in different ways according to
progress of the transaction, the progress being determined on whether
date values have been inserted into various fields.

Since mysql select joining the tables lists only the clients whose
appear in both tables.

I have been attempting to create a temporary table inserting the data
from "Client" and "Transactions"  and selecting from that but not
successfully yet. But is this the way to go about it.

The scale is an anticipated 1000 clients per month of which an
anticipated 800 will involve transactions

Louise