This script is called test-join.php

On the Net, we found info about selecting data from one table if it meets criteria from another table and of course info about selecting data from one table if it meets criteria from that same table, but searching for info about selecting data from one table if it meets criteria from 2 separate tables got us nowhere. We were led to MySQL JOIN statements in our searches, but it was always Google saying "sorry—this is the best we can do." (Of course, they said they had millions of results. But what about GOOD results? Okay—it's not their fault the stuff that was returned as search results sucked massively.) No one had put the answers we needed on the Net.

Happily, we WERE led to JOIN statements as the most likely place to get something that would work. But the problem is that the examples everywhere showed criteria being tested in only one table. At first glance it looked like there were 2 tables' criteria tested, but one of these criteria was always the t1.column1=t2.column1 type of thing, which is not a search criteria at all, but simply a statement of HOW the 2 tables are joined with a common column. It even took quite a while to come up with the fact that the 2 tables' common columns could have different names! The info at W3Schools and the MySQL Manual did NOT state this anywhere. By showing all common columns as having the same name, us eager MySQL learners were all led to believe, erroneously, that the names must be the same. And by showing the criteria as always from one table, we were similarly led to believe that that's what we must do as well. If we missed an obvious page somewhere that defined what was allowed in JOIN statement syntax, please lead us to it (blushing?).

The bottom line is that we had to just try lots of stuff until something worked. Eventually, something did. Many web pages on the Net stated that JOINs were a bit hard to wrap one's head around. We disagree. The JOINing process seems straightforward enough, once you see the light. It was the inadequate info about how to use them and the lack of examples that showed the potential of these cool MySQL gems that made JOINs a bit hard to wrap one's head around. You'd think that once the MySQL gurus had such cool, dynamic commands working right, they'd have proudly explained to us all the cool things you can do with them, rather than merely describing the simplest things. Which brings up an embarrassing question: Could it possibly be that the MySQL gurus never actually tested their JOIN command's potentials for selecting data from one table if it meets criteria from 2 separate tables? Maybe they don't even realize it. Maybe no one does!

Well . . . WE do! Perhaps once everyone sees how much more powerful MySQL is with this newly discovered capability, they'll rename it MySQL Plus! (Okay—maybe not! Whatever . . . )

Check out the LEFT JOIN in our SELECT statement. Because we need to select data from one table if it meets criteria in two separate tables, we figured at first that we were in for a rough ride—we were not even convinced that our goal was doable. Consulting W3Schools LEFT JOIN page was little help. The closest we could find was the SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1; statement found on the MySQL Manual's LEFT JOIN and RIGHT JOIN Optimization page.

Don't let the dot stuff throw you—this is a legitimate way to refer to a table column, using a dot between table and column name. We normally don't need this syntax, but in JOINs we do. The t1.column1=t2.column1 statement merely declares the common column that allows the join since they both have column1. But our tables' common columns were not even the same name (id and member_id). And one of our table's names was a variable ($aa) which was not allowed in our LEFT JOIN (we tried), so we had to prepare simple variables and hope we could sneak them by the MySQL parser. But, more importantly, no example anywhere on the internet showed how to select data from one table if it meets criteria in two separate tables in one powerful SELECT. All examples show WHERE statements with search criteria relating to ONE table.

So we tried a few configurations and JOIN types and finally hit on one that worked: SELECT member_id FROM $aa LEFT JOIN my_members ON my_members.id = $l WHERE my_members.score>0 AND $q LIKE '%$v%', with $v=$fieldvalues[$i], $l=$aa.".member_id", and $q=$aa.".".$fieldnames[$i]. Whew!

This is the logical equivalent, avoiding PHP variables, of SELECT member_id FROM t2 LEFT JOIN t1 ON t1.id = t2.member_id WHERE t1.score>0 AND t2.uggfk_ftf_bb LIKE '%ugg4%'. This shows the tables (whose names were changed to make a clearer example) are JOINed on columns id and member_id and there's a separate criterion for each table to fill to get a positive result set.

We guess that all searches for select data from one table if it meets criteria from two separate tables from now on will end up at our doorstep—right, Google? We searched hard for it and got squat, nada, doodah, zip, bupkiss, zipadeedoodah, and irrelevant crap. Surely we are not the first people with the need to have criteria from 2 tables in one SELECT! WE guess we are simply the first to put it on the Web.

Using SELECT score FROM my_members WHERE id= . . . etc. BEFORE the SELECT member_id FROM $aa LEFT JOIN . . . etc. is not a good way to avoid needing a JOIN, because we do not know the id to put in the WHERE at that point and find it out only in the JOIN statement's results where the member_id we locate that fills all criteria is the same number as the id in the other table.

<?php
define('_NODIRECTACCESS', TRUE);
include_once"includes/config.php";
$fieldvalues=array('ugg4');
$aa="mytest_database";
$i=0;
$gotmatch=0;
$l=$aa.".member_id";
$fieldnames=array('uggfk_ftf_bb');
$q=$aa.".".$fieldnames[$i];
$v=$fieldvalues[$i];
$r=mysql_query("SELECT member_id FROM $aa LEFT JOIN my_members ON my_members.id = $l WHERE my_members.score>0 AND $q LIKE '%$v%'") or die(mysql_error());
$num_rows = mysql_num_rows($r);
$row = mysql_fetch_array($r);
$ID=$row['member_id'];echo $ID."<BR>";
if($num_rows>0){$gotmatch=1;}
echo $gotmatch;
?>

In the real application script, we do the same thing without slapping in variable values like above:

$v=$fieldvalues[$i];
$l=$aa.".member_id";
$q=$aa.".".$fieldnames[$i];
$r=mysql_query("SELECT member_id FROM $aa LEFT JOIN my_members ON my_members.id = $l WHERE my_members.score>0 AND $q LIKE '%$v%'") or die(mysql_error());}
$num_rows = mysql_num_rows($r);
if($num_rows>0){$gotmatch=1;}

The whole LEFT JOIN example above is real and from a script match_.php found in our CMS system for our HTML Form Creator in the HTML Form Creator—Search and Match: Search for Compatible Groups script. Except for the fact that we added values where needed to make this test script (upper) work, it's real and it works. Once it worked, we were able to incorporate this LEFT JOIN select statement into our lower script, just above, the one in our app.

On the W3schools JOIN page, the JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. This is right, but not all. This assumes you are trying to get data from one or more tables, but in no way leaves it open to get data from one table based on satisfying criteria from multiple tables with one common column each. And the various types of JOINS seem at least as limited, and we quote:
•JOIN: Return rows when there is at least one match in both tables
•LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
•RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
•FULL JOIN: Return rows when there is a match in one of the tables

This surely gives no inkling of what we learned LEFT JOIN is capable of, once again bringing up our central thesis: Do they even KNOW?