Using JOIN to get results from two tables

This is PHP4 Code!

Example 1

	$result = @mysql_fetch_array(@mysql_query("
		SELECT *
		FROM group_invitations
		JOIN member_profile
		ON member_profile.user_id = group_invitations.user_id
		WHERE indexer = $indexer
		"));

Example 2

$query = @mysql_query("
  SELECT * FROM group_profile AS GP
  JOIN group_membership AS GM ON GM.group_id = GP.indexer
  WHERE (member_id = $user_id AND GP.indexer = $group_id) AND (GM.approved = 'yes' OR GP.public_private = 'public');
");
if (@mysql_num_rows($query) == 0)

Using JOIN to get results from three tables

This is PHP4 Code!

Example 1

$query102 = @mysql_query("
	SELECT	PP.indexer AS project_id,
				project_name,
				project_picture,
				project_description,
				DATE_FORMAT(start_date, '%d-%m-%y') AS start_date,
				DATE_FORMAT(end_date, '%d-%m-%y') AS end_date,
				CONCAT_WS(' ', first_name, last_name) AS project_lead
	FROM project_profile AS PP
	JOIN group_membership AS GM ON GM.group_id = PP.group_id
	JOIN member_profile AS MP ON MP.user_id = GM.member_id
	WHERE PP.group_id = $group_id AND group_admin = 'yes'
	LIMIT $proj_set_limit, $proj_limit");

Using GROUP BY To Get One Row Per Match

This is PHP4 Code!

Note – when using GROUP BY, if you also use ORDER BY it must come after the GROUP BY

	$sql_query	= "
	SELECT 
		table1.indexer
	FROM table1
	JOIN table2
	ON table2.id = table1.id
	WHERE
		table1.approved = 1 AND
		table2.type = 10
	GROUP BY videos_usage.usage_video_id
	";
	//Note that if using WHERE, GROUP BY must be after it

http://www.w3schools.com/sql/sql_groupby.asp

http://stackoverflow.com/questions/6896723/mysql-join-get-one-row-per-match