SQL Query for condition stored as column in table

akv003 · May 25, 2009

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
Field2

roduct_Name
Field3:condition
Field4

ocument_Name

Table2: Department
field:ID
Field2: Dept_Name
Field3

ept_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 · May 25, 2009

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 · May 25, 2009

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 · May 26, 2009

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 · May 27, 2009

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 · May 28, 2009

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

SQL Query for condition stored as column in table

akv003

Programmer

r937

Technical User

akv003

Programmer

carp

MIS

fredericofonseca

IS-IT--Management

simian101

MIS

Similar threads

Part and Inventory Search

Sponsor