| Author |
Message |
yerram
Newbie
Joined: 07 Sep 2010
Location: United States
Online Status: Offline
Posts: 19
|
  
Topic: Left Outer Join
Posted: 15 Sep 2010 at 11:34am |
Here is the information from the tables. I am trying to inner join the table1 to table 2, left outer join table 2 to table 3.
Table 1
Inp_id
123
124
125
Table 2
Inp_id FSD_Id
123 400
124 500
125 600
Table 3
Fsd_Id Flo_Id Line Value
400 11 1 10
400 11 2 20
500 14 1 80
600 18 1 119
Selection criteria
Table1.inp_id = Table2.Inp_Id and
Table2.Fsd_Id = Table 3. fsd_id and //left outer join here
Flo_id in [11,14]
I wnat the records with flo-id in 11 and 14 and also other inp_ids without any data in Value field.
Inp_id flo_id line Value
123 11 1 10
123 11 2 20
124 14 1 80
125 null null null
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Sep 2010 at 11:52am |
change your select criteria to
isnull(value) or Flo_id in [11,14]
you do not need the table1=table2...that is already defined via the joins Edited by DBlank - 16 Sep 2010 at 3:47am
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Sep 2010 at 11:53am |
unless you do not know how to do thwe joins in crystal?
do you need assistance with that part as well?
|
| IP Logged |
|
yerram
Newbie
Joined: 07 Sep 2010
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 16 Sep 2010 at 2:15am |
|
Thanks DBlank.
I think I am ood now. i will try it and let you know if its not working
|
| IP Logged |
|
yerram
Newbie
Joined: 07 Sep 2010
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 16 Sep 2010 at 3:44am |
What if the data in that tbale is like below:
Inp_id flo_id line Value
123 11 1 10
123 11 2 20
124 14 1 80
125 18 4 90
I want 125 showing up on the report? MAke sense?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Sep 2010 at 3:50am |
why do you need it to show up? if it is because you want to include 18's now just alter the select to inclue that
isnull(value) or Flo_id in [11,14,18]
if not, what is your new inclusion reasons
|
| IP Logged |
|
yerram
Newbie
Joined: 07 Sep 2010
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 16 Sep 2010 at 4:06am |
|
On the report I am trying to get a count of total inp_ids in the table and a count of inp_ids with corresponding flo_ids (only 11 and 14).
|
| IP Logged |
|
yerram
Newbie
Joined: 07 Sep 2010
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 16 Sep 2010 at 4:09am |
On the report I want to see the list of all Inp_ids and flo-Ids if its 11 or 14. For the INp-IDS without 11 or 14 flo_ids I wnat to see null. Below is how I want the output. now I can count the unit count in inp_id and count the values in flod-id.
Inp_id flo_id
123 11
123 14
124 11
125 null
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Sep 2010 at 4:11am |
counting is easy with Running totals rather than select expert.
Do you want a distinctcount or a row count?
|
| IP Logged |
|
yerram
Newbie
Joined: 07 Sep 2010
Location: United States
Online Status: Offline
Posts: 19
|
Posted: 16 Sep 2010 at 4:17am |
Ok. I need to have the output on the report first to start counting. I am using running total now to count the flo_ids seperatly for 11 and 14. but, my question is how to get the inp_id on to the report if it dod not have a corresponding flod_id of 11 or 14 and something else? Let me know if I need to explain this more..
|
| IP Logged |
|
|
|
