中国网络(86SQL.COM)企业邮件系统

I am wanting to figure out how to write a query that will select specific rows from different tables, which shouldnt be a problem, i will just use the related keys to relate the tables. there is one table that I need to do something different with. In this table, the key will relate to another table fine, but then there is another field that has a number value and each of these number values mean different things. for instance, The key in this table may be the number "5". There could be 20 values for "5" with the value in one of the rows different in each, which that is the row that tells me what the record is for. more specifically, lets say there is a table called claimant and the table in question is called Sub_Data. They will be related by the key claimant_id. WHen they are related, the Sub_Data table has a field called value and a field called ID. The ID field is a number that can be translated to tell me what the generic field value is a value for. I want to do a select where I can get all of the claimant values, with the Sub_Data.Value field when it is a certain value only. This is what I have gotten, but it doesnt work, and i know why it doesnt work, just putting it down to give people an idea….

select *, (select value from Sub_Data, claimant where Sub_Data.claimant_id = Claimant.claimant_id and Sub_Data.id = ‘72′) as B from Claimant

The above returns multiple rows on the subquery which obviously wont work,
Any ideas? this was kind of hard to explain so it may be confusing…

Thnks Keg, I will try that tomorrow and see if that is what I am looking for
ok, same question but I need to add something, what if I want to also get the result where the id is say ‘22′, ‘72′, and ‘600′ for each claimant id; how would I get a select statement where it returns all the results for claimant along with columns of the result of value with the above 3 ids from the sub_data table?

You can do this by referencing the sub_data table under 3 aliases.

select
c.*,
s72.value as value_72,
s22.value as value_22,
s600.value as value_600
from
claimant c
left outer join sub_data s72
on c.claimant_id = s72.claimant_id and
s72.id = ‘72′
left outer join sub_data s22
on c.claimant_id = s22.claimant_id and
s22.id = ‘22′
left outer join sub_data s600
on c.claimant_id = s600.claimant_id and
s600.id = ‘600′