在一个查询中从两个 mysql 表中选择数据
我有两张桌子,房东和物业。我的属性表有;其中包含 ID、地址、邮政编码、租约和房东 ID。我面临的问题是:如果我想搜索以 Mr.Spina 作为房东的所有房产,我需要搜索名为“spina”的房东数据库以获取他的 ID,该 ID 保存在房产数据库中,我可以从中获取提取属性详细信息。
我认为这会起作用,但它不正确:
> 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
将“spina”插入该字段的结果应为: 只允许一个链接
这是我提取的代码...
> 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();
提前非常感谢!
I have two tables, landlords and properties. My properties table has; ID, Address, Postcode, lease and landlordID in it. The problem I face is: If I want to search for all the properties that have Mr.Spina as their landlord I need search the landlords database with the name "spina" to get his ID which is saved in the properties database from which I can extract the properties details.
I thought this would work but it doesn't properly:
> 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%'
I have attached images of the table structures.
Landlords:
only allowed one link
Properties:
http://img5.imageshack.us/img5/7199/propertyn.gif
The result of inserting "spina" into the field should then be:
only allowed one link
Here is my extracted code...
> 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();
Many thanks in advance!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
在示例查询中,您需要加入房东 ID。
In your sample query, you need to join on the landlord ID.
看来您使用
INNER JOIN
的方式不正确;你想要的是这样的:Seems like you're using
INNER JOIN
incorrectly; what you want is something like this:您说:
您的联接表达式需要说明两个表如何相关,例如
然后将您的选择移动到
WHERE
子句中:例如:
You said:
Your join expression needs to say how the two tables are related, for instance
And then move your selection into a
WHERE
clause:example: