...

View Full Version : relationship help in query



esthera
01-28-2005, 08:27 AM
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?

Sayonara
01-28-2005, 09:45 AM
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...?

esthera
01-28-2005, 10:04 AM
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?

Kiwi
01-28-2005, 02:10 PM
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

esthera
01-28-2005, 02:57 PM
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

Kiwi
01-28-2005, 09:56 PM
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.

esthera
01-29-2005, 07:06 PM
Thanks for your help kiwi :)!!!

esthera
01-29-2005, 09:17 PM
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?

Kiwi
01-29-2005, 10:34 PM
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);"

esthera
01-30-2005, 07:38 AM
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?

Kiwi
01-30-2005, 10:57 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum