getting data from 2 tables...

select  t1.acct,t1.status,' '  from table1 as t1

UNION ALL

 select  t2.acct,0, t2.notes  from table2 as t2

Select allOpen in new window

select coalesce(convert(varchar, t1.code) + '-', '') + coalesce(convert(varchar, t1.acct), '') as account, t1.status, t2.notes
from table1 t1
inner join table2 t2 on t2.code = t1.code and t2.acct = t1.acct

Hi aej1973,

try this

while($row = mysql_fetch_array($result))
  {
  $code=$row['code'];
  
  $result2 = mysql_query("SELECT notes FROM table2 where code='".$code."'");

  while($row2 = mysql_fetch_array($result2))
    {
	echo "<table border=0 width=100%>
	<tr>
		<td>account</td>
		<td>status</td>
		<td>notes</td>
	</tr>
	<tr>
		<td>".$row['code']." - ".$row['acct']."</td>
		<td>".$row['status']."</td>
		<td>".$row2['notes']."</td>
	</tr>
	</table>";
    }


  }

Select allOpen in new window

Hopefully can help you. Good Luck ^^

dkrollCTN:

I made some changes to the statement you posted but used the inner join;

select collection_id,concat(c.code, '-', c.account)as accountNo, c.name, c.charge, c.status, n.notes from collections c
inner join notes n on n.code = c.code and n.account = c.account;

The output that I get shows only the rows that have notes, why is this? Any suggestions? Thanks.

A

Thanks ralmada, the left join works. How can I include a where clause in this statement? I have a statement as shown below but when I execute it I get an error:

SELECT collection_id, concat( c.code, '-', c.account ) AS accountNo, c.name, c.recurring_charge, c.amount_due, c.status, n.notes, c.lastUpdate, u.userName, d.description
FROM collections c,  codes d, user u
LEFT JOIN collection_notes n ON n.code = c.code
AND n.account= c.account and d.code_id = c.status and c.agent_id = u.user_id

Thanks for the help.

A

try

SELECT       collection_id,
      concat( c.code, '-', c.account ) AS accountNo,
      c.name, c.recurring_charge,
      c.amount_due,
      c.status,
      n.notes,
      c.lastUpdate,
      u.userName,
      d.description
FROM       collections c  
LEFT JOIN collection_notes n ON n.code = c.code and n.account= c.account
LEFT JOIN      codes d d.code_id = c.status
LEFT JOIN      user u ON c.agent_id = u.user_id

or

SELECT       collection_id,
      concat( c.code, '-', c.account ) AS accountNo,
      c.name, c.recurring_charge,
      c.amount_due,
      c.status,
      n.notes,
      c.lastUpdate,
      u.userName,
      d.description
FROM       collections c  
LEFT JOIN collection_notes n ON n.code = c.code and n.account= c.account
INNER JOIN      codes d d.code_id = c.status
INNER JOIN      user u ON c.agent_id = u.user_id

or you can also do:

SELECT       collection_id, 
      concat( t1.code, '-', t1.account ) AS accountNo, 
      t1.name, t1.recurring_charge, 
      t1.amount_due, 
      t1.status, 
      n.notes, 
      t1.lastUpdate, 
      t1.userName, 
      t1.description
FROM       (
	select 	c.code, c.account, c.name, c.recurring_charge, c.amount_due, 
		c.status, c.lastUpdate, u.username, d.description
	from collections
	INNER JOIN      codes d d.code_id = c.status 
	INNER JOIN      user u ON c.agent_id = u.user_id
) t1 
LEFT JOIN collection_notes n ON n.code = t1.code and n.account= t1.account 

Select allOpen in new window