mysql选择两个表_MySQL查询从两个表中进行选择(MySQL query to select from two tables)

I am trying to write a MySQL query that uses two tables.

Table 1 contains a field named "Avail" Table 2 contains a field named "CompDate"

Both tables contains a fields named "UniqueID"

The query I need should look at table 1 where "Avail" contains "3" and table 2 "CompDate" contains, for example "2013-08-26"

If there is a match run the query.

I have tried many combinations of JOINS with no success. The aim of the query is to copy the returned data in to a new table "$NewTable" which is created prior to running this query.

My example query:

mysql_select_db($database_iPin, $conn);

$query_Hist_Temp_Insert = sprintf("INSERT INTO ".$NewTable." ( RecordID,UniqueID,HotelID,Room,AuditBy,AuditorID,LastAudit,Complete,Fail,CompleteDate)

SELECT RecordID,UniqueID,HotelID,Room,AuditBy,AuditorID,LastAudit,Complete,Fail,CompleteDate

FROM RUB_Hist

WHERE CompStamp <= '".$FromDate."'");

I have not included a JOIN in this example bacause the ones I have tried did not work.

Can anyone see how this can be done. It feels like I have been trying to get this to work for days.

Many thanks in advance for your time.

Cheers

I have used the code provided by below:

SELECT RUB_Hist.RecordID, RUB_Hist.UniqueID, CompleteDate,CompStamp

FROM RUB_Hist INNER JOIN RUB_Avail

ON RUB_Hist.UniqueID = RUB_Avail.UniqueID

WHERE RUB_Avail.Avail = '3' AND RUB_Hist.CompStamp <= '2014-01-01'

which returns zero, if I remove "AND RUB_Hist.CompStamp <= '2014-01-01'" from the query it returns data.

I have double checked to make sure that RUB_Hist.CompStamp contains record where the date is <= than my test date.

I have cut down on the fields returned for testing.