Select from two tabels

[Donn]

Hi

I have the below query currently looking at table 'emp', I want to pull a field from Table 'emp2 and add it to the result.

Both tables have a matching field, this being 'passcode'


$query = "SELECT Passcode, status, MAX(Date) FROM emp GROUP BY Passcode";
$result = mysql_query($query);
$newline = "<br />";
echo "". $row['Passcode']. " " .$row['MAX(Date)']." " .$row['status'];
while($row = mysql_fetch_array($result)){
echo "". $row['Passcode']. " " .$row['MAX(Date)']." " .$row['status'];
echo "<br />";


what would I need to add to insert the field 'Name' from table 'emp2' in the result,

any help appreciated

regards

Donn

[artacus]

Its called a "join"
SELECT *
FROM emp
JOIN emp2 ON emp.passcode = emp2.passcode

[Donn]

[quote author=artacus link=topic=118642.msg485030#msg485030 date=1166127988]
Its called a "join"
SELECT *
FROM emp
JOIN emp2 ON emp.passcode = emp2.passcode
[/quote]

Hi, where would I need to put this in here?
SELECT Passcode, status, MAX(Date) FROM emp GROUP BY Passcode

cheers

[artacus]

*Sigh* Why does everyone want spoon fed?

Why don't you TRY to work it out yourself and if you are still stumped in an hour, I'll write it for you.

[Donn]

[quote author=artacus link=topic=118642.msg485039#msg485039 date=1166129052]
*Sigh* Why does everyone want spoon fed?

Why don't you TRY to work it out yourself and if you are still stumped in an hour, I'll write it for you.
[/quote]

:'( Appologies, I really am a beginner, I have got this far with this

SELECT Passcode, status, MAX(Date) FROM emp JOIN emp2 ON emp.Passcode = emp2.Passcode GROUP BY Passcode
But it does nothing,

What I have from Table "emp" is

[i]Passcode, datetime, status[/i]

What I have from Table "emp2" is

[i]Passcode, Name, Surname[/i]

I am after inserting Surname from emp2 before Datetime in emp1 on the result. But must keep the MAX funtion on table "emp" as I only want to display the highest time recorded agains the passcode.

I will be honest, I have only being doing this just short of 8 days but finding it very interesting

regards

Donn

[artacus]

Sorry to be short. I just wish more people would make a serious effort to put the pieces together on their own.
Anywho:
[code]

SELECT emp.Passcode, emp.status, emp2.Surname,
DATE_FORMAT(MAX(emp.datetime),'%m/%d/%Y') AS maxdate
FROM emp
JOIN emp2 ON emp.Passcode = emp2.Passcode
GROUP BY emp.Passcode[/code]