Avatar of aej1973
aej1973
 asked on

getting data from 2 tables...

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
RoutersMicrosoft SQL Server

Avatar of undefined
Last Comment
ralmada

8/22/2022 - Mon
sammySeltzer

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

David Kroll

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
ahann87

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 ^^
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
ralmada

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aej1973

ASKER
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
aej1973

ASKER
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
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ralmada

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ralmada

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