• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

join tables

Hello,
this is my problem. I have the following tables:
Table1          Table2
--------        --------
code name       date      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 to print in quick report base on the date like this
 
  name       amountdate1               amountdate2
              (01/02/01)                (01/03/02)  
  -----      ----------------------  -------------
    a           10                     40
    b           20                     50
    c           30                     60
    d           0                      70

I tried with left and Right outer Someone can help me? please
thanks

0
mgv
Asked:
mgv
  • 4
  • 2
  • 2
  • +3
1 Solution
 
ILECommented:
you think u wona have amountdate1 and amountdate2 or u have amountdate 3,.....amountdate 12321 ?


or just two dates
0
 
geobulCommented:
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
0
 
mgvAuthor Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Stuart_JohnsonCommented:
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
0
 
mgvAuthor Commented:
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
0
 
-Yoshi-Commented:
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"
0
 
mgvAuthor Commented:
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
0
 
xenon_jeCommented:
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
0
 
xenon_jeCommented:
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
0
 
mgvAuthor Commented:
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
0
 
-Yoshi-Commented:
Hmm.. if I get you right all is correct except you're reciving empty rows... if you don't won't them just add a where clause...

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"
WHERE S1.amount<>0 OR S2.amount<>0
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now