Mysql Join – Basic example Data from two tables

7th November 2015

MySql

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'];
}

$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;

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' ");

$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");