连接表的问题 [英] problem with joining tables

问题描述

我在使用join从3个表中获取记录时遇到问题：

加入表有两个条件，[项目编号]和[当前月份] - 日期字段。

我指定了获得记录的月份和年份，但结果是实际记录的4倍。

任何帮助都将受到赞赏。

< pre lang =vb> INSERT INTO patempTable SELECT pr。[项目编号]，pr。[员工编号]， pr。[当月]，pr。[净工资]，al。[净额]，pd。[net] FROM [pay_roll] pr，[allowance] al，[per_diem_accomodation] pd& _

WHERE（pr。[project number] ='& ; projcode& 'al al。[项目编号] ='& projcode& < span class =code-string> 'AND pd。[project number] ='& projcode& 'AND& _

MONTH（pr。[当月]）=& monthnumber& AND MONTH（al。[current month]）=& monthnumber& AND MONTH（pd。[当月]）=& monthnumber& AND & _

YEAR（pr。[current mont] h]）=&期间& AND YEAR（al。[current month]）=&期间& AND YEAR（pd。[当月]）=&期间& ）

解决方案

我在你提供的代码中看到了几个主要问题：



1）你说你试图从三张桌子上得到一张记录，但是你向我们展示的查询是一个INSERT子句，而不是一个SELECT子句。

2）你在谈论使用JOIN，但你实际上从未在查询中加入你的表（WHERE子句并不意味着要处理JOIN）。

3）你应该使用参数化查询而不是像你那样连接字符串值。



你应该先去那里：

加入基础知识 [< a href =http://technet.microsoft.com/en-us/library/aa213233(v=sql.80).aspx\"target =_ blank> ^ ]



然后肯定在那里：

如何创建参数化SQL查询？我为什么要这样做？ [ ^ ]



祝你好运。希望这会有所帮助。

检查此查询：

  INSERT   INTO  patempTable 
（
 [项目编号]，
 [员工编号]，
 [当前月]，
 [净工资]，
 [净额]，
 [[净] 
 ）
  SELECT  pr。[项目编号]，pr。[员工编号]，pr。[当前 month]，pr。[net pay]，al。[net allowance]，pd。[net] 
  FROM  
 [pay_roll] pr 
  INNER   JOIN  [allowance] al  ON  pr。[项目编号] = al。[项目编号] 
  AND  MONTH（pr。[当前月]）= MONTH（al。[当前月]） AND 年（ p r。[当前月]）=年（al。[当前月]）
 < span class =code-keyword> INNER   JOIN  [per_diem_accomodation] pd  ON  pr。 [项目编号] = pd。[项目编号] 
 和 MONTH（pr。[当前月]）=月（pd。[当前月]） AND 年（pr。[当前月]）=年（pd。[当前月]）
  WHERE  
 
 pr。[项目编号] = ' & ; projcode& ' 
  AND  
 MONTH（pr。[当前月份]）=  &月份数和 
  AND  
年份（pr。[当前月份] ）=  &期间&  
  GROUP   BY  
 pr。[项目编号]， pr。[员工编号]，pr。[当前月]，pr。[净工资]，al。[净额]，pd。[net] 

我刚用括号括起了第一个连接语句，因为我正在使用访问数据库。

   INSERT INTO [patempTable] SELECT pr。[项目编号]，pr。[员工编号]，pr。[当月]，pr。[净工资] ]，& _ 
   al。[net allowance]，pda。[net ] FROM（[pay_roll] pr INNER JOIN [allowance] al ON pr。[项目编号] = al。[项目编号] AND& _ 
    pr。[员工编号] = al。[员工编号]和月份（pr。[当月]）= MONTH（al。[当月]）和YEAR （公元[当月]） =年（al。[当月]））& _ 
   INNER JOIN [per_diem_accomodation] pda ON pr。[项目编号] = pda。[项目编号]和& _ 
   pr。[员工编号] = pda。[员工编号] AND MONTH（pr。[current月]）=月（pda。[当月]）和年（pr。[当月]）=年（pda。[当月]） 

I've a problem in getting a record from 3 tables using join:
There are two conditions for joining the tables,[project number] and [current month]-a date field.
I specify the month and year to get the records but it results in 4 times the actual record.
Any help would be appreciated.

"INSERT INTO patempTable SELECT pr.[project number],pr.[employee number],pr.[current month],pr.[net pay],al.[net allowance],pd.[net] FROM [pay_roll] pr,[allowance] al,[per_diem_accomodation] pd" & _

"WHERE (pr.[project number]='" & projcode & "' AND al.[project number]='" & projcode & "' AND pd.[project number]='" & projcode & "' AND " & _

"MONTH(pr.[current month])=" & monthnumber & " AND MONTH(al.[current month])=" & monthnumber & " AND MONTH(pd.[current month])=" & monthnumber & " AND " & _

"YEAR(pr.[current month])=" & period & " AND YEAR(al.[current month])=" & period & " AND YEAR(pd.[current month])=" & period & ")"

解决方案

I see several major problems in the code you provided:

1) You say you're trying to get a record from three tables, but the query you show to us is an INSERT clause, not a SELECT one.
2) You are talking about using JOIN, but you never actually join your tables in your query (WHERE clause is not meant to handle the JOIN).
3) You should use a parameterized query instead of concatenating string values like you do.

You should go there first:
Join Fundamentals[^]

And then surely there:
How do I create a parameterized SQL query? Why Should I?[^]

Good luck. Hope this helps.

Check this query :

INSERT INTO patempTable 
(
[project number],
[employee number],
[current month],
[net pay],
[net allowance],
[[net]
)
SELECT pr.[project number],pr.[employee number],pr.[current month],pr.[net pay],al.[net allowance],pd.[net]
 FROM
 [pay_roll] pr 
 INNER JOIN [allowance] al ON pr.[project number]=al.[project number] 
			AND MONTH(pr.[current month])=MONTH(al.[current month]) AND YEAR(pr.[current month])=YEAR(al.[current month])
  INNER JOIN [per_diem_accomodation] pd ON pr.[project number]=pd.[project number] 
			AND MONTH(pr.[current month])=MONTH(pd.[current month])  AND YEAR(pr.[current month])=YEAR(pd.[current month])
 WHERE 
 
 pr.[project number]='" & projcode & "'
 AND
 MONTH(pr.[current month])=" & monthnumber & "
 AND
 YEAR(pr.[current month])=" & period & "
 GROUP BY 
 pr.[project number],pr.[employee number],pr.[current month],pr.[net pay],al.[net allowance],pd.[net]

I just enclosed the first join statement with a bracket,since I'm working with access database.

"INSERT INTO [patempTable] SELECT pr.[project number],pr.[employee number],pr.[current month],pr.[net pay]," & _
                    "al.[net allowance],pda.[net] FROM ([pay_roll] pr INNER JOIN [allowance] al ON pr.[project number]=al.[project number] AND " & _
                    "pr.[employee number]=al.[employee number] AND MONTH(pr.[current month])=MONTH(al.[current month]) AND YEAR(pr.[current month])=YEAR(al.[current month]))" & _
                    "INNER JOIN [per_diem_accomodation] pda ON pr.[project number]=pda.[project number] AND " & _
                    "pr.[employee number]=pda.[employee number] AND MONTH(pr.[current month])= MONTH(pda.[current month]) AND YEAR(pr.[current month])=YEAR(pda.[current month])"

这篇关于连接表的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！