Joining 3 tables for 2 reports

[PAH]

I have three tables within a report
the order table
orderid Date
101 31/03/2004
102 31/03/2004
103 31/03/2004
104 31/03/2004


The Job Table
OrderID Job type
101 1
101 2
102 1
103 2
103 3
104 1
104 4

The Job Type table
job type id Job type description
1-----------------Jobt1
2-----------------jobT2
3-----------------jobt3
4-----------------jobt4


I need to produce 2 reports that will return the following
all the orders that contain job type 1 alongside the other job types associated with that order

eg
orderid job type des
101 Jobt1
101 Jobt2
102 Jobt1
104 jobt1
104 jobt4

and another report that produces the output all the orders that do not contain a jobtype of 1

orderid jobtype des
103 jobt2
103 jobt3

these will be extracted for specific dates
what are the joins and select criteria I will need for each report

 

[lyanch]

Joins:

OrderTable.orderId -> jobTable.orderId
jobtable.jobTypeId -> jobtypeTable.jobTypeId

This assumes that there are always records in all three tables for each order.

Select criteria:

depends on your version of crystal and database type, which you did not give. You will probably need to use a subselect to get the results you want.


Lisa

 

[PAH]

using CR 8.5 MS SQL 7
the joins

order table order table INNER JOIN jobTable ON
OrderTable.orderId = jobTable.orderId

INNER JOIN jobtypeTable ON
jobtable.jobTypeId = jobtypeTable.jobTypeId
and a jobTypeId <> Jobt1
gives a result of

orderid job type des
101 Jobt2
103 jobt2
103 jobt3
104 jobt4
when I want
orderid jobtype des
103 jobt2
103 jobt3

 

[lbass]

One approach is not to use a select for either report. For the first report, group on {job.orderID} and then create a formula {@jobtype1}:

if {job.jobtype} = 1 then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@jobtype1},{job.orderID}) > 0

This will return all order IDs that have at least one job type = 1.

For the second report, use the same formula {@jobtype1} and in the GROUP selection formula use:

sum({@jobtype1},{job.orderID}) = 0

-LB