getting data from 2 tables...

aej1973
aej1973 used Ask the Experts™
on
Hi, I have a mysql db with two tables as follows:

Table_1
code         acct      status  
001           1            0
002           2            0
003           3            1


Table_2
code         acct      notes  
001           1            active

I need a query that will give me the following output...

account      status   notes
001-1            0        active
002-2            0
003-3            1

Thanks for the help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

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

UNION ALL

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

Open 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

Commented:
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>";
    }


  }

Open in new window


Hopefully can help you. Good Luck ^^
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

try

select       cast(a.code as varchar(3)) + '-' + cast(a.acct as varchar(1)) as account,
      a.status,
      ifnull(b.notes, '') as notes
from Table_1 a
left join Table_2 b on a.acct = b.acct

Author

Commented:
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

Author

Commented:
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
use LEFT OUTER JOIN
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 

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial