Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    37

    Unanswered: A SQL query for the SQL Guru

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    37

    Re: A SQL query for the SQL Guru

    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Posts
    37

    Re: A SQL query for the SQL Guru

    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

  5. #5
    Join Date
    Oct 2003
    Posts
    37
    Gracias ! It worked great !



    Originally posted by r937
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •