使用两个表中的条件从MySQL表中选择数据

I have two MySQL table HouseHold and UserProfile. Both tables contain the columns HouseHoldID. I want to select all rows from HouseHold Table where HouseHoldID in both tables are equal. How cani do that? I am using the following code

我有两个MySQL表HouseHold和UserProfile。两个表都包含HouseHoldID列。我想从HouseHold Table中选择两个表中HouseHoldID相等的所有行。我怎样才能做到这一点?我使用以下代码

select * from Household where Household.HouseholdID = UserProfile.HouseholdID

But its not giving anything rather showing error. Can anyone help me?

但它没有给出任何反映错误的东西。谁能帮我?

6 个解决方案

#1

You can use a JOIN:

你可以使用JOIN:

select * from Household
JOIN UserProfile
ON UserProfile.HouseholdID=Household.HouseholdID

Or with an EXISTS:

或者有一个EXISTS:

select * from Household
WHERE EXISTS
(
   SELECT NULL
   FROM UserProfile
   WHERE UserProfile.HouseholdID=Household.HouseholdID
)

Or with an IN:

或者使用IN:

select * from Household
WHERE Household.HouseholdID IN 
(
   SELECT UserProfile.HouseholdID
   FROM UserProfile
)

Or the old way with the from statement

或者使用from语句的旧方法

select * from Household, UserProfile
WHERE UserProfile.HouseholdID=Household.HouseholdID

It kinda depends on what data you want

它有点取决于你想要的数据

#2

You have to select from two table
Like the following way:

您必须从两个表中选择,如下所示:

select * from Household, UserProfile where Household.HouseholdID = UserProfile.HouseholdID

Or Use Join

或者使用加入

select * from Household h
JOIN UserProfile u
ON u.HouseholdID=h.HouseholdID

#3

You need to select the UserProfile table as well

您还需要选择UserProfile表

select * from Household h JOIN UserProfile u ON h.HouseholdID = u.HouseholdID

#4

You need to JOIN the tables. Try this:

你需要加入表格。试试这个:

select * from 
Household h JOIN
UserProfile u ON u.HouseholdID =h.HouseholdID

Learn more about joins here.

在此处了解有关联接的更多信息

#5

Well, the SQL language has very neat way to select records from both tables by given criteria, it is called JOIN:

那么,SQL语言有非常简洁的方法可以按给定的条件从两个表中选择记录,它叫做JOIN:

SELECT H.*, U.* FROM Household H 
INNER JOIN UserProfile U ON U.HouseholdID = H.HouseholdID

#6

You can try:

你可以试试:

SELECT A.* FROM Household A INNER JOIN UserProfile B ON A.HouseholdID = B.HouseholdID

#1