I have a been presented with a question on writing a sql query that involves four tables. I can kind of get there but I'm missing a piece and can't figure it out. Here goes:
Table A (id is pk)
Table B (corresponding id field, not pk)
Table B has freq min and freq max fields (search based on these)
Table C (keyid is pk)
Table D (corresponding keyid field, not pk)
Table D has freq min and freq max fields (search based on these)
The ultimate goal is to get the number of records in Table A

The user selects 'name' from table C, the corresponding keyid is then used to select all the records in Table D that match.
Select freqmin, freqmax from Table D
where table d.keyid = table c.keyid
Let say the return was 2 records
Record 1
freqmin = 12
freqmax = 15
Record 2
freqmin = 18
freqmax = 21
I use the values to find the number of records in Table B that meet the following criteria: (this is where I run into a problem)
Select id
from Table B
where record 1. freqmin between table B.freqmin and table B.freqmax
and record1.freqmax between table B.freqmin and table B.freqmax
When both records are compared the id in Table B needs to be the same or else it's an invalid result.

I don't think this can be done in One Query ... if it can I'm all ears. I couldn't find a way to do it because I have no connection between Table B & Table C.
Any and all inputs are appreciated.

Hi Schimelcat

A couple of questions :-

What sort of sql environment are you using (in oracle you can add sub queries in the from clause - which I find really useful when linking so many tables together) ?

What are you trying to achive? (sorry, its not that clear from the information) - it might be useful if you describe more of the columns in each table.


As a quick pointer - in your first sql you haven't specified table c in your from clause, yet you've linked to it in your where clause.

Kind regards

Keith

Hi Keith,
First let me answer the easy question. It's MS SQL talking to an Access database.
The ultimate goal is to get all the product ids from Table A
that meet the selection criteria found in Table D.
The following are the fields I'm dealing with:
Table A
Product ID(PK)
Table B
Product ID, Freqmin, Freqmax
Table C
KeyID, KeyName
Table D
KeyID(PK), Freqmin, FreqMax
If the user selects a keyname(Table C) that results in several keyid's in Table D all the values of freqmin/freqmax need to then be compared to Table B.
Let's say keyname generated a keyid of 6, I take the keyid and count how many times I find it in Table D. Lets say there are 3 records, and the values for freq min for the 3 different records are 15.5, 18, 20.1 and the values for freqmax are 17, 20, 22.5
I now have 6 values: 15.5 - 17, 18-20, 20.1-22.5
I need to look and see if I can find those six values in the "range" of the freqmin and freqmax of Table B(PK field is FeatureID).
Example:
ProductID = 4
Freqmin = 15
Freqmax = 17
ProductID = 4
Freqmin = 30
Freqmax = 31
ProductID = 4
Freqmin = 45
Freqmax = 46

I should get a return of zero records because ProductID 4 didn't meet the 18 - 20 or the 20.1 - 22.5.

If I only had one record set from Table D and that was the 15.5 - 17 then ProductID 4 would be a valid recordset.

I hope this makes sense ..... I appreciate the help on trying to get this in "one" query.

Regards ! Tammy

Gracias ! It worked great !