Need help with select statement

[maxf]

fds_id | region | impact_value | condition_id

1..........West............1.02..............6
2..........West............2.02..............7
3..........South...........1.76..............6


Im trying to select distinct regions and their corresponding fds_id, impact vaule based on the condition_id. So in this example, for a condition_id of 6, I would like the records returned to be:

fds_id=1, region=West, impact_value=1.02
fds_id=2, region=South, impact_vaule=1.76

Im using the SQL statment:

"SELECT DISTINCT fds.region FROM fds WHERE EXISTS (Select fds.impact_value, fds.fds_id FROM fds WHERE fds.condition_id=6)"

The problem is that in the recordset returned, the impact_value and fds_id values arent being returned. Its only showing the distinct region names. How can I access the corresponding fds_id and impact_value?"

 

[MeanGreen]

Try this:

select fds_id,region,impact_value from fds
where condition_id = 6
group by condition_id,fds_id,region,impact_value
order by condition_id,fds_id


Good Luck.

 

[maxf]

No. That doesnt return distinct records by region.

 

[MeanGreen]

I think this will do it.


select fds_id,region,impact_value from fds
where condition_id = 6
group by condition_id,region,fds_id,impact_value
order by condition_id,region


However, the question I have is what if you have 2 identical regions for a condition_id? I assume you want all regions with a condition_id broken out by fds_id and impact_value. Is there some other criteria you will use to identify what data row you want, or do you want all as in this select statement?

Hopefully this will help.

 

[maxf]

There arent identical condition_ids for regions. There are mulitple years for regions, and is why your query doesnt produce only distinct regions. For example, the Northwest region, might have 4 entries for the condition_id=6, for the years 2000-2003. With you select statement, Northwest shows up 4 times, once for each year. I only need each region to appear once only for a condition_id.

 

[SQLBill]

You didn't say anything about a year entry. Try changing MeanGreen's suggestion to this (change 'year' to whatever your column name is that holds the year data):

select fds_id,region,impact_value from fds
where condition_id = 6
and year = 2002
group by condition_id,region,fds_id,impact_value
order by condition_id,region

SQLBill

 

[MeanGreen]

Actually, with my select statement their was no indication of multiple years, but you could add the years to the group by and limit retrieval to just specific years.
Using the Group by condition_id,Region will get you the distinct rows. The question is with your example of Northwest showing up 4 times based on each year, if you exclude the year in the group by, then you would only get the region_id and Region once. What you need to do is define uniquely what you are really wanting. Do you want for every condition_id an associated Region, or a listing of all Regions with their associated condition_id? Or do you want to limit it to the current year? You have so many different issues that you need to define in order to get your data properly.
I am pretty sure the Group by statement is what you need to use. I hope this helps explain a little about the select statement presented so far.

Good Luck!