This is PHP4 Code!

Using JOIN to get results from two tables

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

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

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

USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.

Comments

Your email address will not be published.