×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Query for condition stored as column in table

SQL Query for condition stored as column in table

SQL Query for condition stored as column in table

(OP)
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:Product_Name
Field3:condition
Field4:Document_Name

Table2: Department
field:ID
Field2: Dept_Name
Field3:Dept_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          

RE: SQL Query for condition stored as column in table

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

RE: SQL Query for condition stored as column in table

(OP)
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.

RE: SQL Query for condition stored as column in table

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?

RE: SQL Query for condition stored as column in table

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: SQL Query for condition stored as column in table

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

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close