在 mySql join 语句中显示第一个表中的所有结果，但仅显示第二个表中的第一个结果 [英] Show all results from 1st table but only 1st result from 2nd table in mySql join statement

问题描述

我有一个 HTML 表单，用于在下表中搜索名字、姓氏、地址(以及为了简单起见我在此省略的其他几列):

成员

id |名字 |姓---+-----------+-----------1 |乔恩 |美国能源部2 |玛丽 |史密斯3 |乔恩 |詹姆士

地址

id |member_id |地址---+-----------+-----------1 |1 |家庭地址2 |1 |工作地址3 |2 |家庭地址4 |3 |家庭地址5 |3 |工作地址6 |3 |假期地址

一个成员可以拥有无​​限数量的地址.

如何搜索所有出现的Jon"作为名字但只显示第一个地址?例如:

Jon Doe 家庭住址乔恩·詹姆斯家庭住址

这是我们到目前为止的 mySQL 语句:

SELECT * FROM tbl_members T1 INNER JOIN tbl_addresses T2 ON T1.id = T2.member_id WHERE firstname = 'Jon'

我们也尝试过:

SELECT * FROM tbl_members T1 LEFT JOIN tbl_addresses T2 ON T1.id = T2.member_id WHERE firstname = 'Jon'

这两种说法都给了我们:

Joh Doe 家庭住址Jon Doe 工作地址乔恩·詹姆斯家庭住址乔恩·詹姆斯工作地址乔恩詹姆斯假日地址

非常感谢您的帮助！

解决方案

我认为你应该这样做:

SELECT T1.firstname, T1.lastname, T2.address来自 T1 的成员INNER JOIN (SELECT member_id, address来自地址GROUP BY member_id) AS T2ON T1.id = T2.member_id

这里是 SQL Fiddle 看看它是如何工作的......

GL！

当然，如果您需要，您可以在查询末尾添加 WHERE 子句以从表或名称组中选择特定名称

WHERE firstname = 'joe'

或任何...

雇用是小提琴...

I have a HTML form which searches for firstname, lastname, address (and several other columns which I have left out here for simplicity) in the following tables:

MEMBERS

id | firstname | lastname
---+-----------+-----------
1  | Jon       | Doe
2  | Mary      | Smith
3  | Jon       | James

ADDRESSES

id | member_id | address
---+-----------+-----------
1  | 1         | Home address
2  | 1         | Work address
3  | 2         | Home address
4  | 3         | Home address
5  | 3         | Work address
6  | 3         | Holiday address

A member can have an unlimited number of addresses.

How do I search for all occurences of 'Jon' as firstname but only display the first address? For example:

Jon Doe Home address
Jon James Home address

This is our mySQL statement so far:

SELECT * FROM tbl_members T1 INNER JOIN tbl_addresses T2 ON T1.id = T2.member_id WHERE firstname = 'Jon'

We have also tried:

SELECT * FROM tbl_members T1 LEFT JOIN tbl_addresses T2 ON T1.id = T2.member_id WHERE firstname = 'Jon'

Both these statements give us:

Joh Doe Home address 
Jon Doe Work address 
Jon James Home address 
Jon James Work address 
Jon James Holiday address

Many thanks for any help!

解决方案

Hi I think you should do something like this:

SELECT T1.firstname, T1.lastname, T2.address
FROM MEMBERS AS T1
INNER JOIN (SELECT member_id, address
            FROM ADDRESSES
            GROUP BY member_id) AS T2
ON T1.id = T2.member_id

Here is SQL Fiddle for that to see how it's work...

GL!

EDIT:

Of course if you need you can add WHERE clause at the end of query to select specific name from table or group of name like

WHERE firstname = 'joe'

or whatsoever...

Hire is a Fiddle for that...

这篇关于在 mySql join 语句中显示第一个表中的所有结果，但仅显示第二个表中的第一个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！