?
Solved

join tables

Posted on 2003-03-15
11
Medium Priority
?
157 Views
Last Modified: 2010-04-04
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
Comment
Question by:mgv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 3

Expert Comment

by:ILE
ID: 8147094
you think u wona have amountdate1 and amountdate2 or u have amountdate 3,.....amountdate 12321 ?


or just two dates
0
 
LVL 17

Expert Comment

by:geobul
ID: 8147189
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
 

Author Comment

by:mgv
ID: 8147538
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:Stuart_Johnson
ID: 8147834
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
 

Author Comment

by:mgv
ID: 8148059
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
 

Expert Comment

by:-Yoshi-
ID: 8148751
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
 

Author Comment

by:mgv
ID: 8151935
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
 
LVL 9

Expert Comment

by:xenon_je
ID: 8152102
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
 
LVL 9

Expert Comment

by:xenon_je
ID: 8152162
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
 

Author Comment

by:mgv
ID: 8152916
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
 

Accepted Solution

by:
-Yoshi- earned 280 total points
ID: 8158257
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question