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?
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...?
no there is more then one area per fundprogram
let me give you the complete structure as I am doing something wrong
I will need to do a complete query querying based on areaofinterst,deadline, and geographicid
Can you help me?
SELECT A.name, A.deadline, B.areaeng, C.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
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
SELECT A.name, A.deadline, B.areaeng, C.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 = 2 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.
Thanks for your help kiwi :)!!!
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?
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);"
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?
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:
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 (
(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.