Nice example of a Join where you need things using the same linked ID
I want the information from login based on the visitby column of visit, the id column of login is the same as the column called visitby in the table called visit. Got it!
$taskid=213;
echo $result = mysql_query("SELECT * FROM login
JOIN visit
ON visit.visitby = login.id
WHERE visit.id = '$taskid' ");
while($row = mysql_fetch_array($result))
{
echo $fname = $row['fname'];
}
or this example
SELECT * FROM songs
JOIN votes
ON songs.song_id = votes.song_id
WHERE songs.song_id = 1
AND votes.vote_res = 1;
It’s like a membership number, get all books and overdue fines where id number is 12347.
another example
$result = mysql_query("SELECT * FROM tasks
JOIN residents
ON residents.id = tasks.forwho // does not matter which way round
WHERE tasks.id = '$taskid' ");
and using AS to select ambiguous column names solving that problem when you have used fname in many tables, and it picks the wrong fname!
$result = mysql_query("SELECT *, login.id AS loginid, login.fname AS resfname, login.sname as ressname FROM login
JOIN resid
ON login.connectedid = resid.id
WHERE login.role='family' AND login.hideyesno='no'
Order by login.id DESC LIMIT 10");
