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|||select D.freqmin, D.freqmax, count(a.id) as Acount
from TableD C
inner
join TableC D
on C.keyid = D.keyid
inner
join TableB B
on D.freqmin between B.freqmin and B.freqmax
and D.freqmax between B.freqmin and B.freqmax
inner
join tableA A
on B.id = A.id
where C.name = 'userpick'
group
by D.freqmin, D.freqmax|||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 !
Originally posted by r937
select D.freqmin, D.freqmax, count(a.id) as Acount
from TableD C
inner
join TableC D
on C.keyid = D.keyid
inner
join TableB B
on D.freqmin between B.freqmin and B.freqmax
and D.freqmax between B.freqmin and B.freqmax
inner
join tableA A
on B.id = A.id
where C.name = 'userpick'
group
by D.freqmin, D.freqmax
Dhaliwal: