Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Senior Coder
    Join Date
    May 2004
    Posts
    1,457
    Thanks
    15
    Thanked 0 Times in 0 Posts

    relationship help in query

    I have a main table of entries
    I have a separate table listing areas

    Now I have a link table with the entry id and area id (as each entry has more then one area)

    How can I run a query searching all the entries for a specific area?

    what is my relationships on those 3 tables?

    Am I going about this in the wrong way?

  • #2
    Regular Coder
    Join Date
    Oct 2004
    Posts
    256
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Assuming (have I got this right?) you have a number of areas, and each entry has one area associated with it, you can simply use a column for area_id in your entry table which your query joins with the area_id in your areas table.

    If more than one area can be associated with an entry, then your current set-up is fine.

    The best query to use depends on which set-up it is...?

  • #3
    Senior Coder
    Join Date
    May 2004
    Posts
    1,457
    Thanks
    15
    Thanked 0 Times in 0 Posts
    no there is more then one area per fundprogram


    let me give you the complete structure as I am doing something wrong

    4 tables

    1. fundprograms - id,geographyid,name,deadline,comments
    2. fundareallinks - id,areaofinterestid,fundprogramid
    3. areasofinterst - id, areaeng
    4. geographic-- id, geo

    I will need to do a complete query querying based on areaofinterst,deadline, and geographicid

    Can you help me?

  • #4
    Regular Coder
    Join Date
    Oct 2002
    Posts
    380
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    SELECT A.nameA.deadlineB.areaengC.geo
    FROM 
     fundprograms A
    ,
     
    areasofinterst B,
     
    geographic C,
     
    fundareallinks D
    WHERE
     A
    .id D.fundprogramid AND
     
    A.geographyid C.id AND
     
    D.areaofinterestid B.id 

  • #5
    Senior Coder
    Join Date
    May 2004
    Posts
    1,457
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Hi Thanks for your help.

    I'm just trying to understand your statement.
    How would i add a where clause to it.
    I want to say I want to select all with an areaid of 2 and a deadline of 1/30/2005 and gegraphicid of 2?

    Thanks again for you help

  • #6
    Regular Coder
    Join Date
    Oct 2002
    Posts
    380
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    SELECT A.nameA.deadlineB.areaengC.geo
    FROM 
     fundprograms A
    ,
     
    areasofinterst B,
     
    geographic C,
     
    fundareallinks D
    WHERE
     A
    .id D.fundprogramid AND
     
    A.geographyid C.id AND
     
    D.areaofinterestid B.id AND
     
    B.id AND
     
    A.deadline '1/30/2005' AND
     
    C.id 2

    You can add as many AND clauses as you like. This assumes that the id fields are numeric. If they're not, you will need to put the values in quotes. Finally, I'm not 100% sure on the date format - if this doesn't work, check your sql manual to confirm the date formats used by whichever engine you're using.

  • #7
    Senior Coder
    Join Date
    May 2004
    Posts
    1,457
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Thumbs up

    Thanks for your help kiwi !!!

  • #8
    Senior Coder
    Join Date
    May 2004
    Posts
    1,457
    Thanks
    15
    Thanked 0 Times in 0 Posts
    one more issue

    SELECT A.name, A.deadline, B.areaeng, C.geoeng
    FROM fundprograms AS A, areasofinterest AS B, geographic AS C, fundarealinks AS D
    WHERE A.id = D.fundprogramid AND A.geographicid = C.id AND D.areaofinterestid = B.id AND (A.geographicid=[C].[id]) AND (C.ID=1)AND (A.deadline>=#1/1/2005# And A.deadline<=#3/31/2005#) or a.deadline=null;


    If I do the above query and say entry with the name of xxx has an area of 2 and 3 -- If I do the above query choosing not to query by area the xxx will come out twice in both areas. How can I limit it to only come up once?

  • #9
    Regular Coder
    Join Date
    Oct 2002
    Posts
    380
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're missing some brackets: "...AND (A.deadline>=#1/1/2005# And A.deadline<=#3/31/2005#) or a.deadline=null;" should be "...AND ((A.deadline>=#1/1/2005# And A.deadline<=#3/31/2005#) or a.deadline=null);"

  • #10
    Senior Coder
    Join Date
    May 2004
    Posts
    1,457
    Thanks
    15
    Thanked 0 Times in 0 Posts
    no this still returns me the name twice if it was in the arealinks table twice.
    It also will not return me rows where the deadline is null.

    Do I have to do a select distinct name where in(select.....)

    is that the correct syntax?

  • #11
    Regular Coder
    Join Date
    Oct 2002
    Posts
    380
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The other problem I just spotted is that you've got both:
    A.geographicid = C.id and
    (A.geographicid=[C].[id]) AND (C.ID=1) AND (A.deadline>=#1/1/2005# And A.deadline<=#3/31/2005#)

    I wouldn't have thought this would produce a cartesean join, but it's definitely not quite right. Try something more like:
    PHP Code:
    SELECT A.nameA.deadlineB.areaengC.geoeng
    FROM
     fundprograms 
    AS A,
     
    areasofinterest AS B,
     
    geographic AS C,
     
    fundarealinks AS D
    WHERE A
    .id D.fundprogramid AND
     
    A.geographicid C.id AND
     
    D.areaofinterestid B.id AND (
      (
    C.ID=1) AND
      (
    A.deadline>=#1/1/2005# And A.deadline<=#3/31/2005#) OR
      
    a.deadline=null
      
    ); 
    Distinct is not a good way of getting around this problem (it's really most useful when you are intentionally selecting part of a composite index; using it to over-come a cartesean join is not really wise). If this select doesn't work, then all I can think of is there is either a missing join criteria or bad data in the tables.


  •  

    Posting Permissions

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