从一个查询中的两个mysql表中选择数据

问题描述：

我有两个表格，房东和房产。我的属性表有; ID，地址，邮政编码，租赁和landlordID。我面对的问题是：如果我想搜索所有拥有Mr.Spina作为房东的房产，我需要在名为“spina”的房东数据库中搜索他的ID并将其保存在属性数据库中提取属性的详细信息。从一个查询中的两个mysql表中选择数据

我认为这会工作，但它不正确：

> SELECT property.ID, property.address, property.postcode, property.lease, landlords.firstName, landlords.lastName FROM property INNER JOIN landlords ON landlords.firstName LIKE '%spina%' OR landlords.lastName LIKE '%spina%'

我附上的表格结构的图像。

斗地主：

只允许一个链接

属性：

http://img5.imageshack.us/img5/7199/propertyn.gif

插入 “脊柱” 进入该领域的结果应该然后是：只允许一个链接

这是我提取的代码...

> if($field=="landlord"){ 
>  
>  $sql="SELECT property.ID, property.address, property.postcode, 
> property.lease, landlords.firstName, 
> landlords.lastName FROM ".$do." INNER 
> JOIN landlords ON landlords.firstName 
> LIKE '%".$q."%' OR landlords.lastName 
> LIKE '%".$q."%'"; 
>   } else{ 
>  $sql="SELECT * FROM ".$do." WHERE " . $field . " LIKE '%" . $q . "%'"; 
> } //end special case  $result = 
> mysql_query($sql); 
>  echo "$sql"; 
>  echo "<table border='1'> 
>  <tr> 
>  <th>ID</th> 
>  <th>Address</th> 
>  <th>Post Code</th> 
>  <th>Lease</th> 
>  <th>Landlord</th> 
>  </tr>"; 
> 
> while($row = 
> mysql_fetch_array($result)) 
>  { 
>  echo "<tr>"; 
>  echo "<td>" . $row['ID'] . "</td>"; 
>  echo "<td>" . $row['address'] . "</td>"; 
>  echo "<td>" . $row['postcode'] . "</td>"; 
>  echo "<td>" . $row['lease'] . "</td>"; 
>  echo "<td>" . $row['firstName'] ." ". $row['lastName'] ."</td>"; 
>  echo "</tr>"; 
>  } echo "</table>"; 
> 
> mysql_close();

非常感谢提前！

答

好像你不正确地使用INNER JOIN;你想要的是这样的：

SELECT 
    property.ID, property.address, property.postcode, property.lease, landlords.firstName, landlords.lastName 
FROM 
    property 
INNER JOIN 
    landlords 
ON 
    landlords.ID = property.landlord 
WHERE 
    landlords.firstName LIKE '%spina%' 
    OR 
    landlords.lastName LIKE '%spina%'

非常感谢，您的文章用一个组合上面做记号！ – nicky 2010-01-22 18:02:23

答

你说：

> SELECT property.ID, property.address, property.postcode, property.lease, firstName, lastName FROM property INNER JOIN landlords ON firstName LIKE '%spina%' OR lastName LIKE '%spina%'

您的加盟表达需要说这两个表是如何相关，例如

... LEFT JOIN landlords ON landlords.ID == property.landlord_ID ...

然后移动选择到WHERE条款：

... WHERE firstName LIKE '%spina%' OR lastName LIKE '%spina%'

示例：

> cat > landlords.csv 
1,Alex,Spina 
2,Spina,Brown 
3,Katell,Jentreau 

> cat > properties.csv 
1,toytown 
2,hogwarts 
3,mars 
4,new york 
2,sheffield 

> sqlite3 

> CREATE TABLE landlords (ID,firstname,lastname); 
> .import "landlords.csv" "landlords"; 
> CREATE TABLE properties (landlord,address); 
> .import "properties.csv" "properties"; 
> SELECT * FROM properties JOIN landlords ON landlord = ID 
    WHERE (firstname LIKE "Spina" OR lastname LIKE "Spina"); 
landlord,address,ID,firstname,lastname 
1,toytown,1,Alex,Spina 
2,hogwarts,2,Spina,Brown 
2,sheffield,2,Spina,Brown

答

在你的示例查询中，你需要加入房东的ID。

SELECT property.ID, property.address, property.postcode, property.lease, 
    landlords.firstName, landlords.lastName 
FROM property INNER JOIN landlords ON landlords.id = property.landlordID 
WHERE landlords.firstName LIKE '%spina%' OR landlords.lastName LIKE '%spina%'

从一个查询中的两个mysql表中选择数据

相关推荐