this sql command make me sick

[enzo]

hello everyone...

after several days trying to find the right command.. i have to ask you
people...

this is the deal:

i have two tables related...
the first one is the master, the second the slave...
the first has fields like code, name, department and periodicity of
payment...
the second has the payment data... code, date of payment, amount to
pay...

not every one in the first table has the payment in the same month...
because a few people receives its pay one time in the month, and other
receives payment every two or three months (periodicity of payment)...

my trouble is when i want to see all the payments in one month of the
people from one specific department, because if somebody doesn't get
his payment (different periodicity) i have to see the code and name of
the worker AND the date and amount of payment in blank (in that
case)...

i thing that i must to be a simple LEFT JOIN but it doesn't work in
Access (i have a web asp page with and access database in this case)...

i don't know what to do.. please.. if somebody has some ideas.. i need
it...

thanks..

Enzo..

ps/.. my left join code:
SELECT table1.name, table2.date, table2.amount
FROM table1 LEFT JOIN table2 ON table1.code = table2.code
WHERE (((table1.depar tm)=53) AND ((table2.date)= #2/15/2006#));

[David Portas]

enzo wrote:

hello everyone...

after several days trying to find the right command.. i have to ask you
people...

this is the deal:

i have two tables related...
the first one is the master, the second the slave...
the first has fields like code, name, department and periodicity of
payment...
the second has the payment data... code, date of payment, amount to
pay...

not every one in the first table has the payment in the same month...
because a few people receives its pay one time in the month, and other
receives payment every two or three months (periodicity of payment)...

my trouble is when i want to see all the payments in one month of the
people from one specific department, because if somebody doesn't get
his payment (different periodicity) i have to see the code and name of
the worker AND the date and amount of payment in blank (in that
case)...

i thing that i must to be a simple LEFT JOIN but it doesn't work in
Access (i have a web asp page with and access database in this case)...

i don't know what to do.. please.. if somebody has some ideas.. i need
it...

thanks..

Enzo..

ps/.. my left join code:
SELECT table1.name, table2.date, table2.amount
FROM table1 LEFT JOIN table2 ON table1.code = table2.code
WHERE (((table1.depar tm)=53) AND ((table2.date)= #2/15/2006#));

You are asking an Access question in a SQL Server forum. Access <> SQL
Server.

The following is untested but try it out:

....
FROM table1
LEFT JOIN table2
ON table1.code = table2.code
AND table2.date=#2/15/2006#
WHERE table1.departm= 53 ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Hugo Kornelis]

On 1 Jun 2006 15:54:27 -0700, enzo wrote:

(snip)

i thing that i must to be a simple LEFT JOIN but it doesn't work in
Access (i have a web asp page with and access database in this case)...
Hi enzo,

You have asked your question in a SQL Server group. Since you are using
Access, maybe you should try an Access group instead?
ps/.. my left join code:
SELECT table1.name, table2.date, table2.amount
FROM table1 LEFT JOIN table2 ON table1.code = table2.code
WHERE (((table1.depar tm)=53) AND ((table2.date)= #2/15/2006#));

For SQL Server, you'd have to change the format for the date constant.
And for each database that supports LEFT JOIN, you'd have to move the
filtering condition for table2.date.

In SQL Server syntax:

SELECT t1.name, t2.date, t2.amount
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t2.code = t1.code
AND t2.date = '20060215'
WHERE t1.departm = 53

--
Hugo Kornelis, SQL Server MVP

[enzo]

thanks to both of you...

i'm sorry.. it was my mistake to be here.. sorry again..
anyway thanks for the tip.. i will try it...

thanks,

Enzo.