在SQL中选择查询（如何） [英] Select query in SQL (how to)

问题描述

我有2张桌子：



表1：属性

内容为2个字段

attrID;属性

DV;潜水员

CL;登山者

FF;消防员



表2：userAttributes

内容为2个字段

attrFK;用户名

CL;彼得

FF;彼得

CL; Joe



然后我必须在html和php中下拉列表，我需要一个选择查询，以便当我点击一个包含用户名的下拉列表并选择它时，其他下拉列表将仅显示用户缺少的属性



任何人都可以帮助我吗？



谢谢

解决方案

试试这个：

  SELECT  t1.username，t2.attribute 
  FROM  userAttributes  AS  t1  INNER   JOIN 属性 AS  t2  ON  t1.attrFK = t2.attrID 
  WHERE  t1.username =  @username  

< pre lang =SQL> SELECT 属性 AS MissingAttribute
FROM 属性
< span class =code-keyword> WHERE attrID NOT IN （
SELECT t2.attrFK AS AttrID
FROM userAttributes AS t1 INNER JOIN 属性 AS t2 ON t1.attrFK = t2.attrID
WHERE t1.username = @ username ）

更多： IN （T-SQL） [ ^ ]

I have 2 tables:

Table 1: Attributes
contents are 2 fields
attrID ; attribute
DV ; Diver
CL ; Climber
FF ; Fire Fighter

Table 2: userAttributes
contents are 2 fields
attrFK ; username
CL ; Peter
FF ; Peter
CL ; Joe

Then I have to dropdown lists in html and php, I need a select query so that when I click on one dropdown which holds the usenames and select it, the other dropdown list will show only those missing attributes of the users

Can any one help me?

Thanks

解决方案

Try this:

SELECT t1.username, t2.attribute
FROM userAttributes AS t1 INNER JOIN Attributes AS t2 ON t1.attrFK = t2.attrID
WHERE t1.username = @username

[EDIT #1 - missing attributes]

SELECT attribute AS MissingAttribute
FROM Attributes 
WHERE attrID NOT IN (
    SELECT t2.attrFK AS AttrID
    FROM userAttributes AS t1 INNER JOIN Attributes AS t2 ON t1.attrFK = t2.attrID
    WHERE t1.username = @username)

[EDIT]

More: IN (T-SQL)[^]

这篇关于在SQL中选择查询（如何）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！