SQL Command problems 1

[dbaJSmith]

Using CR XI R2
Hello all:
I'm trying to create a SQL Command using a series of joins to get multiple columns or results from a single column. The purpose of this is to get a single view of all the data in a single view. The data must be presented this way in order to preserve the current format and layout of the report. The data is stored as:

MasterRecord
RecordID    Name
1           Bob
2           Jane
3           John
...

Multichoice
RecordID    ValueResult
1           "S"
1           "P"
2           "N"
...

...and I want the following result:

RecordID    ValueS    ValueP    ValueN
1             S         P         ""
2             ""        ""        N
3             ""        ""        ""
...

My first thought was to perform multiple joins in a single view:

select MasterRecord.RecordID, MC1.ValueResult as ValueS, MC2.ValueResult as ValueP, MC3.ValueResult as ValueN
from MasterRecord left outer join Multichoice as MC1 on MasterRecord.RecordID = MC1.RecordID and MC1.ValueResult = "S"
left outer join Multichoice as MC2 on MasterRecord.RecordID = MC2.RecordID and MC2.ValueResult = "P"
left outer join Multichoice as MC3 on MasterRecord.RecordID = MC3.RecordID and MC3.ValueResult = "N"

This usually works fine; however, it turns out the SQL ODBC I'm working with right now does not allow multiple conditions in joins.

My next thought was to create multiple SQL Commands, one for each field. Example:

select MasterRecord.RecordID, Multichoice.ValueResult
from MasterRecord left outer join Multichoice on MasterRecord.RecordID = Multichoice.RecordID
where Multichoice.ValueResult = "S"

This created a whole new set of issues. First, I can only join this to the primary table using an inner join; any other join gives me an "Invalid Argument" error. Even past that, I'm receiving strange counts of records which could only happen due to using these multiple SQL Commands.

So this being the case, does anyone have any ideas on another way this data can be generated in a format I can use? Any help is appreciated. Thanks!

 

[Madawc]

You might be able to get the same result more simply within Crystal using Crosstab.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

YOu should use a case statement and group by

select MasterRecord.RecordID,
max(case when MC1.ValueResult = 'S' then 'S' else '' end) valueS,
max(case when MC1.ValueResult = 'P' then 'P' else '' end) valueP,
max(case when MC1.ValueResult = 'N' then 'N' else '' end) valueS

from MasterRecord left outer join Multichoice as MC1 on MasterRecord.RecordID = MC1.RecordID

group by MasterRecord.RecordID

Ian

 

[dbaJSmith]

Thanks for the quick response!

Madawc: This report is ungrouped; without using a subreport I would not be able to generate a cross-tab. In addition, the format of the report would not lend itself to using a cross-tab easily, and would most likely slow it down.

IanWaterman: Thanks! This command works fine with the SQL ODBC. Excellent query alternative!!