join tables

you think u wona have amountdate1 and amountdate2 or u have amountdate 3,.....amountdate 12321 ?


or just two dates

Hi,

What database are you using? In MS Access it's pretty easy (crosstab query) but not that easy in MSSQL server for example.

Regards, Geo

hello everybody,
thanks for answered. Based on table1(code,name) and table2(date,code,amount) .
I only have to enter by keyboard date1(01/02/01) and date2(01/03/02) and the report should print like this

name       amountdate1               amountdate2
             (01/02/01)                (01/03/02)  
 -----      ----------------------  -------------
   a           10                     40
   b           20                     50
   c           30                     60
   d           0                      70
Thanks guys.by the way I am using MSSQL

mgv,

You're table doesn't quite make sense.  Can you please provide the true column headings, as one table can't have two columns of the same name (code).  Also, name and date are reserved words and shouldn't be used as column headings.

I can probably provide you with SQL if you can give me a proper table structure.

Stu

This are the structure of the tables:
Table1(code(P.K),code_name)
Table2(cod_table2(P.K),ddate,code(F.K),amount)
this is a relation 1-M
example:
Table1                   Table2
--------                 --------
code code_name         ddate      code amount
1    a                 01/02/01   1   10
2    b                 01/02/01   2   20
3    c                 01/02/01   3   30
4    d                 01/03/02   1   40
5    e                 01/03/02   2   50
                       01/03/02   3   60
                       01/03/02   4   70


I want the report like this based on the transaction of table2:

code code_name       amountdate1               amountdate2
                    (01/02/01)                (01/03/02)  
----  -----      ----------------------  -------------
   1    a           10                     40
   2    b           20                     50
   3    c           30                     60
   4    d           0                      70

I tried this code:
SELECT DISTINCT c.name, s.code, s.c_date, s.amount, s1.code, s1.c_date, s1.amount
FROM class AS c, sales AS s, sales AS s1
WHERE ((((s.c_date)=#1/10/2001#)) and (((s1.c_date)=#2/10/2002#)) and (c.code=s.code) and  c.code=s1.code);
but it does not show code_name(d)
thanks again

Not sure if this will be all correct and what you want but I hope it might help you.. don't know if you'd like an inner join or a left.. (the inner join doesn't show unmatched rows.. play with it if unsure)

SELECT C.code, C.code_name, S1.amount AS amountdate1, S2.amount AS amountdate2
FROM class AS C
LEFT JOIN sales AS S1 ON S1.code=C.code AND S1.ddate="01/02/01"
LEFT JOIN sales AS S2 ON S2.code=C.code AND S2.ddate="01/03/02"

Hi Yoshi,
We are very close to this task.
I tried your SQL and it shows like this.
code code_name       amountdate1               amountdate2
                   (01/02/01)                (01/03/02)  
----  -----      ----------------------  -------------
  1    a           10                     40
  2    b           20                     50
  3    c           30                     60
  4    d           0                      70
  5    e            0                      0

if you see table2 there is no transaction for code(5),so I do not want to show  it.
thank you
bye

Try this:

SELECT DISTINCT
  c.name, s.code, s.c_date, isNull(s.amount,0) as amountdate1 , s1.code, s1.c_date, isNull(s1.amount,0) as amountdate2
FROM
  class AS c LEFT JOIN  sales AS s on  c.code=s.code
  LEFT JOIN sales AS s1 on c.code=s1.code
WHERE
  s.c_date='1/10/2001' and s1.c_date='2/10/2002'

in fact the dates you don't need so its enough:
SELECT DISTINCT
  c.name, isNull(s.amount,0) as amountdate1 , isNull(s1.amount,0) as amountdate2
FROM
  class AS c LEFT JOIN  sales AS s on  c.code=s.code
  LEFT JOIN sales AS s1 on c.code=s1.code
WHERE
  s.c_date='1/10/2001' and s1.c_date='2/10/2002'

This should wotk if you have MSSQL server. I wrote above the query that works in the query analyzer.

good luck, xenon

Omit from above the DISTINCT keyword (my mistake).

I just saw the
5  e   0   0

So you wanted  to say that you want only those who have at least one amount different from 0?
Than here is another sql:

SELECT
 c.name, s.code, s.c_date, isNull(s.amount,0) as amountdate1 , s1.code, s1.c_date, isNull(s1.amount,0) as amountdate2
FROM
 class AS c LEFT JOIN  sales AS s on  c.code=s.code
 LEFT JOIN sales AS s1 on c.code=s1.code
WHERE
 s.c_date='1/10/2001' and s1.c_date='2/10/2002'
  and ((s.amount is not null) or (s1.amount is not null))

I hope this is what you need.
   xenon

I tried your sql and shows the matched records in both dates which is good ,besides that  i need to show the code  
that has at least one amount different from 0 in one date and the other date assume that is zero.
Take a look the tables(table1,table2) that are above.
thanks