Subqueries in Crystal

Which version of Crystal?

Can you build the query in the database (if so the report will run much faster)

Select Room.RoomNo, RoomPerson.*
FROM  Room LEFT OUTER JOIN RoomPerson
ON Room.RoomNo = RoomPerson.RoomNo

Create a parameter of type DATE
SelectedDate

Then use the Select Expert to add the filter criteria

{?SelectedDate} in {RoomPerson.StartDate} to {RoomPerson.EndDate}

mlmcc

I am using crystal 8.5.  So are you saying that I do not use the report wizard, but just create the SQL myself in Crystal (I don't have the abilty to create the SQL in the database).  How do I create thw query in Crystal without using the Report Expert.

Also, I've had problems where if I modify the SQL statement manually instead of using the Report Wizard, it ignores my select critieria.  Will that be the case here as well?

Will that query give me what I want?  Once I add the selected date parameter, won't it will then filter out any rooms that do not have a move in date (which means they are currently not in use)

For example, if I have this data:

ROOM
---------------------
B102
B103
B104
C103
C104

ROOM_PERSON
---------------------------------------------------
Joe Smith    B102    Jan 1, 05          Jun 1, 05
Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05


I think the query you gave me will return this:

Joe Smith    B102    Jan 1, 05          Jun 1, 05
Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05
<null>        C103    <null>            <null>

If I then add the date parameter of 7/1/2005, it will return

Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05

and room C103 will drop off the list because it does not have a date, right?  Furthermore, if C103 had an assignment but it was for 2004, that assignment would not show up on the list, nor would it show me that it is available.  For example, if I had this data:


ROOM
---------------------
B102
B103
B104
C103
C104

ROOM_PERSON
---------------------------------------------------
Joe Smith    B102    Jan 1, 05          Jun 1, 05
Chris Jones  B102    Jun 2, 05         Dec 1, 05
Tom Smith  B103    Jun 2, 05         Dec 1, 05
Jon Kim       C104    Jun 2, 05        Dec 1, 05
Tim Tom     C103    Jun 2, 04        Dec 1, 04

Running the query would give me exacty what's in Room_Person.  Then, when I applied the date filter, C103 would drop off the list but I would want to show that it was available.  That's why I think I need to use subqueries:  first run a query from Room_Person to get the assignments as of a date, then compare that list with what's in Room to pull any additional rooms that were not in the first query.  But I don't know how to do that in Crystal.  Maybe I am missing something?? :)

Using a LEFT OUTER JOIN will ensure all rooms in the ROOM table are included in the query.  If the room is empty then the columns from the other table will have NULL.

To get the empty rooms included after the selection criteria try

(isNull({?SelectedDate})) OR ({?SelectedDate} in {RoomPerson.StartDate} to {RoomPerson.EndDate})

mlmcc

Thanks again for the info.  Now, I tried the left outer join in SQL+ (not in Crystal yet because I am working with someone who has CR 8.5 installed whereas I do not) but it does not give me what I want.  The left outer join just gives me NULLS in cases where the room was NEVER in the room_person table, but I need to factor in a date in order for this query to give me what I want.  If I apply the filter after that query, it will give me rooms that were booked as of a date, but since the initial query did not have the correct listing of NULLS, my empty rooms will not appear properly (it will only be rooms that were NEVER in the room_person table).  

I'm really having a hard time seeing how I can do this without using a UNION query to union rooms as of a date with all rooms missing from that initial list.  For example, I tried this and it gave me what I wanted:

select a.pk_bed_space,  b.ck_move_in_date, b.room_person_move_out_date, c.ix_last_name,  
c.ix_first_name
from rooms a left outer join room_person b
on a.pk_bed_space = b.ck_bed_space, person c where a.pk_bed_space like 'AL%'  
and b.ck_move_in_date <= '2005-09-01' and b.room_person_move_out_date >= '2005-09-01' and
a.secondary_bed_space is not null and c.pk_rms_id = b.ck_rms_id
 
UNION
 
select a.pk_bed_space , NULL, NULL, NULL, NULL from rooms a where  
a.pk_bed_space not in (select a.pk_bed_space from rooms a left outer join room_person b
on a.pk_bed_space = b.ck_bed_space where a.pk_bed_space like 'AL%'  
and b.ck_move_in_date <= '2005-09-01' and b.room_person_move_out_date >= '2005-09-01' and
a.secondary_bed_space is not null) and a.secondary_bed_space is not null and
a.pk_bed_space like 'AL%'

This gives me an accurate snapshot of rooms in the AL building as of 9/1/2005.  The problem is, I want to make this query parmeter based so I can pass the date and the building via a parameter, but I'm not sure how to do it using this type of UNION query in Crystal...  Any thoughts?

Thanks again for all of your help so far! :)

We used a subreport to get us what we needed.  Thanks!

Glad i could help

mlmcc