i have 3 tables, account table with id, col1, col2, col3 attribute table has id name and label values eg. 1 = col1 = country, 2 =col2 = city predefined table where 1= UK, or US Now i want to get all get from account table which has col1=1 as county = UK How i write MS SQL query for this?

I'm not even going to ask why you felt the need to create your own method of naming table columns, when SQL Server already provides a way of doing so far more efficiently than you can. OK, yeah i am. Why doesn't your account table have columns like id, country, city, etc? You wouldn't be having this problem if it did...and you'd at least have some hope of keeping your data straight. SQL Server, like every real database, has the ability to check data for the right type of values before it ever gets stored in the database. (Foreign keys are cool like that.) But it can't do that when a column can contain any of dozens of types of values. You're begging for troubles with this schema of yours...how many times in your life are you going to have to change a country to a city anyway? Probably never...but not only is that the kind of thing your schema lets you do, but you can do it *easily*. Read: you're making it easy to screw up your data, in order to gain flexibility you will never need or use. (If you inherited this schema from some other "database engineer", you have my sympathies. If you came up with it yourself, read all that stuff above a few more times and consider restructuring to something more sane, so i don't have to offer my sympathies to the poor schmuck who inherits that big ball of bits when you get fired or leave for the loony bin.) Anyway, the schema you have there needs a bit more explanation before i can make much sense of it. Perhaps it's the = signs that are throwing me off...i dunno. Specific questions: * The IDs in the attributes table...do they correspond to IDs in the accounts table? Or are they just unique numbers, and the attributes table only has 3 rows? Or is there some other, far more annoying scheme here? * Do the IDs in this "predefined" table relate to the IDs in the attributes table?

Hi, I would have to agree with chao that this is not looking too good from the onset. I have a slightly different understanding of the table structure than chao, thought sharing might help: Account: id, col1, col2, col3 1 | 1 | 2 | ... 2 | 3 | 4 | ... (Where cols1, col2, col3 are FKs to Predefined, see below.) Attribute: id, label 1 | country 2 | city ... (Where 1 represents col1, 2 represents col2, etc. - ouch?) Predefined (=countries, cities, ...): id, name 1 | UK 2 | London 3 | US 4 | New York ... Not sure at all about this, as chao pointed out more information would be great, with perhaps some sample data to make things crystal clear. Thanks. Elinor Elinor Hurst http://elinorhurst.blogspot.com