Join the Same Table Twice in MySQL

Sometimes yo need to query the same database table twice in one query where the two rows have a common link. Here we have 2 queries on the MODx template variable value table I need to merge into one:

$q = "SELECT value FROM modx_site_tmplvar_contentvalues WHERE contentid='".$resource->get('id')."' AND tmplvarid='5'";
$q = "SELECT value FROM modx_site_tmplvar_contentvalues WHERE contentid='".$resource->get('id')."' AND tmplvarid='6'";

To do this you need to join the table to itself but tell the query which the common field is so you don't get too many rows back. This is what you do:

$q = "SELECT table1.value AS value1, table2.value AS value2 FROM modx_site_tmplvar_contentvalues table1 LEFT JOIN modx_site_tmplvar_contentvalues table2 ON table1.contentid = table2.contentid WHERE table1.contentid='".$resource->get('id')."' AND table1.tmplvarid='5'  AND table2.tmplvarid='6'";

Here, we need to identify the table with unique names (table1 and table2 in this example), plus we also need to name the fields we are returning with unique names (value1 and value2):

table1.value AS value1, table2.value AS value2

Then we join the table to itself using the names already declared:

modx_site_tmplvar_contentvalues table1 LEFT JOIN modx_site_tmplvar_contentvalues table2

Then we tell it what we are joining it on (the common link):

ON table1.contentid = table2.contentid

Then we carry on with a standard query making sure we use the table name each time we refer to a field.

WHERE table1.contentid='".$resource->get('id')."' AND table1.tmplvarid='5'  AND table2.tmplvarid='6'";