如何匹配两个表中的记录一次

Question

This is the Match Table I'm trying to achieve I have a problem i am trying to resolve. Kindly help. Thanks in Advance.

Scenario: There are three tables in a database. Let's say tblA, tblB and tblC. It's for a service swap kind of scenario. So, tblA contains records of people who request for a service and tblB contains records of those who offer their services. So, tblB is supposed to be matched with someone requesting the same service at the same available time and put the match records into tblC.

What I have Done/Tried: I have been able to create a query for a match to occur between both tables which is good progress. But this leads me to a major problem.

The Problem: Problem is based on what i've done, the query matches one person requesting to more than one person offering. I want it to be once it matches a record in tblA to someone in tblB and put it in tblC, it should delete immediately so that it doesn't match those records to other people.

Example code:

    $match = "SELECT * FROM tblmatch";
    $Resmatch = mysql_query($match, $localhost) or die(mysql_error());
    $row_match = mysql_fetch_assoc($Resmatch);
    $mat_offuemail = $row_match['useremail'];
    $mat_offustype = $row_match['stype'];
    $mat_offtrange = $row_match['trange'];

    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "request-form")) {
      $insertSQL = sprintf("INSERT INTO tblrequest (orderid, useremail, catname, rdate, stype, trange, rdesc, rloc) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
                           GetSQLValueString($_POST['uorder'], "text"),
                           GetSQLValueString($_POST['uemail'], "text"),
                           GetSQLValueString($_POST['rcat'], "text"),
                           GetSQLValueString($_POST['date'], "text"),
                           GetSQLValueString($_POST['serv'], "text"),
                           GetSQLValueString($_POST['trange'], "text"),
                           GetSQLValueString($_POST['rdesc'], "text"),
                           GetSQLValueString($_POST['rloc'], "text"));

      If ($_POST['uemail'] == $mat_offuemail AND $_POST['serv'] == $mat_offustype AND $_POST['trange'] == $mat_offtrange){
        echo "Match Done Previously";
        }
      else{
        $inmatch = "INSERT INTO tblmatch (useremail, userorder, stype, uemail, uorder, trange)
SELECT tbloffer.useremail, tbloffer.orderid, tbloffer.stype, tblrequest.useremail, tblrequest.orderid, tbloffer.trange
FROM tbloffer
INNER JOIN tblrequest
ON tbloffer.stype = tblrequest.stype
AND tbloffer.trange = tblrequest.trange
WHERE tbloffer.useremail != tblrequest.useremail
AND tbloffer.catname != tblrequest.catname
ORDER BY tbloffer.useremail
LIMIT 1";
        }

      mysql_select_db($database_localhost, $localhost);
      $Result1 = mysql_query($insertSQL, $localhost) or die(mysql_error());
      $Result2 = mysql_query($inmatch, $localhost) or die(mysql_error());

      $insertGoTo = "match.php";
      if (isset($_SERVER['QUERY_STRING'])) {
        $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
        $insertGoTo .= $_SERVER['QUERY_STRING'];
      }
      header(sprintf("Location: %s", $insertGoTo));
    }

Answer 1

In order to match with only one offer we can use a sub-query and the GROUP BY clause. I am assuming that orderid is unique and can be used as primary key to select only one matching offer if they are more than one. With the MIN function on the orderid I am always picking the one with smallest ID number, but any other aggregate function will work just as well (e.g. MAX).

Here is the complete SELECT stmnt:

SELECT tbloffer.useremail, tbloffer.orderid, tbloffer.stype, tblrequest.useremail, tblrequest.orderid, tbloffer.trange
FROM tbloffer
JOIN tblrequest
ON tbloffer.stype = tblrequest.stype
AND tbloffer.trange = tblrequest.trange
WHERE tbloffer.useremail != tblrequest.useremail
AND tbloffer.catname != tblrequest.catname
AND tbloffer.orderid IN (
    SELECT min(orderid)
    FROM tbloffer
    GROUP BY stype, trange
)

Again I would suggest to evaluate if you could use a view instead of inserting matching rows by PHP script. A view can be created easily based on the above SQL statement and work as substitute for your script.