如何设置MySQL查询？

How do I set up this query? I have awards in a table. For example, a trophy may be award_id 1 in the award table. A ribbon may be award_id 2 in the award table. I then have another table, award_user, where it has the award_id and the userid of the user. So, if I wanna give the user the award_id 1, and their userid is 25, it will be in the award_user table as award_id 1 and userid 25 (which means they have that award). I want to select all of the awards the user has based off of their userid.

如何设置此查询?我在表中获奖。例如,奖杯表中的奖杯可以是award_id 1。功能区可以是奖励表中的award_id 2。然后我有另一个表,award_user,它有award_id和用户的用户ID。所以,如果我想给用户一个award_id 1,并且他们的用户ID是25,那么它将在award_user表中作为award_id 1和userid 25(这意味着他们有该奖励)。我想选择用户基于其用户ID的所有奖励。

Here is the thing, though. The awards are in the award table, but the actual user information about the awards are in the award table. So, I will have to select from two different places, I guess.

不过这是件事。奖项在奖项表中,但有关奖项的实际用户信息在奖项表中。所以,我猜我必须从两个不同的地方选择。

Here is what I have so far..

这是我到目前为止...

$query = mysql_query("SELECT * from award WHERE award_id = (SELECT award_id FROM award_user WHERE userid = $userid)");

$ query = mysql_query(“SELECT * from award WHERE award_id =(SELECT award_id FROM award_user WHERE userid = $ userid)”);

Not sure if this will work or not.

不确定这是否有效。

Basically, it has to select the awards from the award table with the award_id's that the userid has. $userid is a valid variable, it is their userid.

基本上,它必须从奖励表中选择具有userid所具有的award_id的奖励。 $ userid是一个有效变量,它是他们的用户ID。

Thanks!

2 个解决方案

#1

Your way of expressing the query should work, with one minor tweak. You need to change the = to in, because there can be more than one aware

您表达查询的方式应该有效,只需进行一次小调整即可。您需要将=更改为in,因为可以识别多个

SELECT *
from award a
WHERE a.award_id in (SELECT au.award_id FROM award_user au WHERE au.userid = $userid)

More typically, this would be expressed as a join:

更典型的是,这将表示为连接:

select a.*
from award a join
     award_user au
     on a.award_id = au.award_id
where au.userid = $userid;

The join form is often more efficient as well.

连接形式通常也更有效。

#2

What you need is a join:

你需要的是一个加入:

SELECT awards.* FROM awards
  JOIN award_user USING (award_id)
WHERE userid = :userid

By the way, learning how joins work is the one thing you really need to do in order to make proper use of SQL. They're what relational databases are all about. If you're using an SQL database but not using joins, it's a bit like, say, having a fancy new computer but only using it to read and write text in Notepad.

顺便说一下,学习联接是如何工作的,你需要做的就是正确使用SQL。它们是关系数据库的全部内容。如果您使用的是SQL数据库但没有使用连接,那么有点像,比如拥有一台花哨的新计算机,但只使用它来在记事本中读写文本。

#1