SQL Query for condition stored as column in table

[akv003]

Hi,

I need to form an SQL query to fetch the values from table A where conditions are satisfied. These conditions are stored in Table A itself in a column called "condition"

Could some one please help to form the query on this?

My table structure is as given
Table1: documentList
Field1: ID
Field2roduct_Name
Field3:condition
Field4ocument_Name

Table2: Department
field:ID
Field2: Dept_Name
Field3ept_Type
Field_type_Dept_code

the resultant value should fetch all the document_name where condition in condition column satisfies the result.

Note: Condition values are field name of Table 2 extactly same what we put in "WHERE" clause.

Sample Values in Table1 is
ID Product_Name Condition Document_name
1 Books Table2.Dept_code='101' Book1
2 Books Table2.Dept_code='102' Book2
3 laptop Table2.Dept_Type='IT' laptop1

I need a SQL query from above to get all records where condition in Table1 is such that Table2.Dept_code='101' satisfied


plz help

 

[r937]

are we allowed to try to talk you out of this design?

what possible benefit do you see in doing things this way?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[akv003]

With above design, I am trying to avoid multiple where clause statement to be fired on table for fetching more than one condition match records.

Also, conditon are unrestricted on field vales of table2, so I do not have control on it other than incorporating condition in table itself.

Do you have any other suggestion for above design.

 

[carp]

While I don't quite understand the reasoning, there are different approaches that might make more sense. How many different rows (WHERE specifications) are you expecting for Table1?

 

[fredericofonseca]

I think I see what you are trying to do - but as it is quite hard to be sure - associated to the fact that it is VERY BAD design, can you please try and give a sample sql (even if it is wrong and wont work) of how you would like this to work.

e.g. give input data, input conditions ,e.g. how will your SQL be executed - how will you supply parameters (conditions) to it - how many conditions can there be?, what would be your desired output given the input data and input conditions, and everything else you can think of.
Please give examples of more than one number of parameters to the SQL and with enough input data samples to make it clear of what you are trying to accomplish



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[simian101]

I am not sure if it is ANSI or not but in SQL Server you could just use dynamic queries. The fields names are not the same I used a table I already had...

First you have to get your condition into the table. I assume you figured that out.

insert into table2 values (2,'docname = ' + char(39) + 'ASR08222184-1349-20090526' + char(39))

Then you have to set up your queries.

declare @test varchar(100)
declare @sqlstr varchar(300)
select @test = (select condition from table2 where id=2)
print @test
select @sqlstr = 'select * from document where ' + @test
print @sqlstr
exec (@sqlstr)

Good Luck.

Simi