Expanding a complicated query

[OceanDesigner]

Last week I got terrific help with a query. I would like to add one more criterion to the query, but it is just too complicated for me. The new criterion itsself is somewhat complicated so I will ask about the simplest form first.

Here are the original tables
tblService:
ServiceID Desc
a first
b second
c third
d fourth

tblBoat:
ID Boat ServiceID Status
1 101 a ok
2 101 b pending
3 102 a ok
4 103 d ok

Here is the query and result I have now thanks to r937:

select S.ServiceID
     , B.Status
  from tblService S
left outer
  join tblBoat B
    on S.ServiceID 
     = B.ServiceID
   and B.BoatID = 101

Results for Boat 101:
ServiceID Status
a ok
b pending
c NULL
d NULL

Now I am adding a new table. The table tells me which boats the service bulletin affects.

tblAffects:
ServiceID Criteria
a BETWEEN 100 AND 102
b BETWEEN 100 AND 106
c BETWEEN 104 AND 108
d BETWEEN 104 AND 108

So now I need to edit the query above so that it only returns bulletins that affect the selected boat

New Results for Boat 101:
ServiceID Status
a ok
b pending

The only way I can think to do this is to generate a separate query that returns valid ServiceID's and use that in a where clause. Can I do this with JOINs?

Jeff

 

[vongrunt]

I'm not sure about table tblAffects - Criteria contains SQL expression?

Wanna do it by book, make many-to-many table that binds services and boats:

tblServiceBoats
ServiceID BoatID

 

[tektipdjango]

It's unclear if the tblBoat table is the result of the tblAffects selection criterias.

If this is the case, to get the results you want, you just have to do

select ServiceID,Status
from
tblBoat
where Boat=101

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[OceanDesigner]

OK, let me try to clarify. First tblBoat is input from the user on whether the service bulletin has been completed. It is not the result of any selection criteria.

The tblService table and tblAffects table are related one-to-many. What the tblAffects table tells me is which bulletins pertain to which boats (for instance, bulletin 1 may only pertain to boats 1,2 and 3, while bulletin 2 may pertain to 3,4 and 5). Actually, with the data I have demonstrated above, I could have just made an extra "affects" column in tblService, but I envision there being multiple ranges of boats for each bulletin (for example, between 1 and 5 and between 8 and 10 - so I need two entries).

I initially thought the easiest thing to do would be to write the WHERE clause directly in the affects field, but I am not so sure that is the best thing to do. That is, however, the intent of the data in the table above. I hope that is clear.

Here is what the current query does. The user selects a boat - say boat 101. It prints out the status of the boat for each bulletin. Where there is no entry for that combination of boat and bulletin in tblBoat, it simply prints out a NULL. That works great.

Now what I want to do is restrict the output for boat 101 to just those bulletins that apply to it. So if only the first three bulletins affect boat 101, I only want the query to print the status of the boat for the first three bulletins. It should omit the remaining bulletins.

I am open to suggestions on how to store the "affected" data most effectively and how to modify the query to consider that information.