This is an exact example of the database i am using (Im not using access im using SQl server, this is just for demonstration)
这是我正在使用的数据库的一个确切示例(我没有使用使用SQl服务器访问即时通讯,这仅用于演示)
What I want to do is reference or join the department table into the project table. I should be able to use the following query successfully. I need this queries specific result
我想要做的是将department表引用或加入到项目表中。我应该能够成功使用以下查询。我需要这个查询特定的结果
SELECT P.ProjectID, P.ProjectName, P.Department, D.BudgetCode
FROM DEPARTMENT D, PROJECT P
This query executes successfully. However, I get duplicate values in my result and it is not formatted properly. This is the result I get
此查询成功执行。但是,我在结果中得到重复的值,并且格式不正确。这是我得到的结果
ProjectID ProjectName Department BudgetCode
1000 2010 Q3 Product Plan Marketing BC-300-10
1000 2010 Q3 Product Plan Marketing BC-100-10
1000 2010 Q3 Product Plan Marketing BC-400-10
1000 2010 Q3 Product Plan Marketing BC-500-10
1000 2010 Q3 Product Plan Marketing BC-800-10
1000 2010 Q3 Product Plan Marketing BC-200-10
1000 2010 Q3 Product Plan Marketing BC-700-10
1000 2010 Q3 Product Plan Marketing BC-600-10
1100 2010 Q3 Portfolio Analysis Finance BC-300-10
1100 2010 Q3 Portfolio Analysis Finance BC-100-10
1100 2010 Q3 Portfolio Analysis Finance BC-400-10
1100 2010 Q3 Portfolio Analysis Finance BC-500-10
1100 2010 Q3 Portfolio Analysis Finance BC-800-10
1100 2010 Q3 Portfolio Analysis Finance BC-200-10
1100 2010 Q3 Portfolio Analysis Finance BC-700-10
1100 2010 Q3 Portfolio Analysis Finance BC-600-10
1200 2010 Q3 Tax Preparation Accounting BC-300-10
1200 2010 Q3 Tax Preparation Accounting BC-100-10
1200 2010 Q3 Tax Preparation Accounting BC-400-10
1200 2010 Q3 Tax Preparation Accounting BC-500-10
1200 2010 Q3 Tax Preparation Accounting BC-800-10
1200 2010 Q3 Tax Preparation Accounting BC-200-10
1200 2010 Q3 Tax Preparation Accounting BC-700-10
1200 2010 Q3 Tax Preparation Accounting BC-600-10
1300 2010 Q4 Product Plan Marketing BC-300-10
1300 2010 Q4 Product Plan Marketing BC-100-10
1300 2010 Q4 Product Plan Marketing BC-400-10
1300 2010 Q4 Product Plan Marketing BC-500-10
1300 2010 Q4 Product Plan Marketing BC-800-10
1300 2010 Q4 Product Plan Marketing BC-200-10
1300 2010 Q4 Product Plan Marketing BC-700-10
1300 2010 Q4 Product Plan Marketing BC-600-10
1400 2010 Q4 Portfolio Analysis Finance BC-300-10
1400 2010 Q4 Portfolio Analysis Finance BC-100-10
1400 2010 Q4 Portfolio Analysis Finance BC-400-10
1400 2010 Q4 Portfolio Analysis Finance BC-500-10
1400 2010 Q4 Portfolio Analysis Finance BC-800-10
1400 2010 Q4 Portfolio Analysis Finance BC-200-10
1400 2010 Q4 Portfolio Analysis Finance BC-700-10
1400 2010 Q4 Portfolio Analysis Finance BC-600-10
I need to get this result instead
我需要得到这个结果
I tried using a JOIN:
我尝试使用JOIN:
SELECT P.ProjectID, P.ProjectName, P.Department, D.BudgetCode
FROM DEPARTMENT D
RIGHT JOIN PROJECT P ON P.ProjectID = D.DepartmentName
But I get a conversion error
但是我收到转换错误
Conversion failed when converting the varchar value 'Accounting' to data type int.
将varchar值'Accounting'转换为数据类型int时转换失败。
Which i assume is because its not possible to convert a varchar to an int. Unless a cast may work...
我假设是因为它无法将varchar转换为int。除非演员可以工作......
SELECT P.ProjectID, P.ProjectName, P.Department, D.BudgetCode
FROM DEPARTMENT D
RIGHT JOIN PROJECT P ON cast(P.ProjectID AS varchar) = D.DepartmentName
Using the cast, I got this result
使用演员表,我得到了这个结果
ProjectID ProjectName Department BudgetCode
1000 2010 Q3 Product Plan Marketing NULL
1100 2010 Q3 Portfolio Analysis Finance NULL
1200 2010 Q3 Tax Preparation Accounting NULL
1300 2010 Q4 Product Plan Marketing NULL
1400 2010 Q4 Portfolio Analysis Finance NULL
This is almost what i was looking for, but it shows NULL values for my BudgetCodes, witch is not correct. They are not null values.
这几乎是我正在寻找的,但它显示我的BudgetCodes的NULL值,这是不正确的。它们不是空值。
So at this point, I'm out of ideas. (As a side note, I understand that in my database i have a varchar as a primary key, and i was trying to JOIN that primary key with a Primary key that was an int. If they were of the same type, Im sure my join would have worked)
所以在这一点上,我没有想法。 (作为旁注,我理解在我的数据库中我有一个varchar作为主键,我试图用一个int的主键来加入那个主键。如果它们属于同一类型,我确定我的加入会有效)
1 个解决方案
#1
1
If Project.Department is a forigen key to Department.DepartmentName then you can join by that.
如果Project.Department是Department.DepartmentName的forigen键,那么你可以加入。
JOIN Project P ON P.Department = D.DepartmentName
if not, you need to see which column is shared between the two tables Project and Department, and link both tables by that column. shared column needs to have the same datatype on both tables.
如果没有,您需要查看两个表Project和Department之间共享哪个列,并按该列链接两个表。共享列需要在两个表上具有相同的数据类型。
Another tip, you're querying Department and JOIN Projects, and in the same time, you only use BudgetCode from Department, and the rest are from Project. In this case, you need to query Project and JOIN Department.
另一个提示,您要查询Department和JOIN Projects,同时,您只使用Department中的BudgetCode,其余来自Project。在这种情况下,您需要查询Project和JOIN Department。
SELECT
P.ProjectID,
P.ProjectName,
P.Department,
D.BudgetCode
FROM PROJECT P
INNER JOIN DEPARTMENT D ON D.DepartmentName = P.Department
#1
1
If Project.Department is a forigen key to Department.DepartmentName then you can join by that.
如果Project.Department是Department.DepartmentName的forigen键,那么你可以加入。
JOIN Project P ON P.Department = D.DepartmentName
if not, you need to see which column is shared between the two tables Project and Department, and link both tables by that column. shared column needs to have the same datatype on both tables.
如果没有,您需要查看两个表Project和Department之间共享哪个列,并按该列链接两个表。共享列需要在两个表上具有相同的数据类型。
Another tip, you're querying Department and JOIN Projects, and in the same time, you only use BudgetCode from Department, and the rest are from Project. In this case, you need to query Project and JOIN Department.
另一个提示,您要查询Department和JOIN Projects,同时,您只使用Department中的BudgetCode,其余来自Project。在这种情况下,您需要查询Project和JOIN Department。
SELECT
P.ProjectID,
P.ProjectName,
P.Department,
D.BudgetCode
FROM PROJECT P
INNER JOIN DEPARTMENT D ON D.DepartmentName = P.Department