Select Query from multiple column

Question

4.00/5 (1 vote)

See more:

Currently I am working on sql server 2005
i am having a single table with 10 column in which 5 are of "companyName" and 5 for their "HRstatus".
I want that if i select a company and a HRStatus all data with matching records should come..
i have done the code for retreiving with single Hrstatus how can i do it for both..

SQL

select *
from TotTab
where 'Dispatch' in (HRStatus,HRStatus2,HRStatus3,HRStatus4,HRStatus1)
   or 'Rejected' in (HRStatus,HRStatus2,HRStatus3,HRStatus4,HRStatus1)

i want the query that will match the "companyName" with "hrstatus"

any1 plz suggest

thanks in advance
Vikram Acharya

Posted 13-Dec-11 20:04pm

Acharya Vikram

Updated 13-Dec-11 20:43pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-12-13T20:09:00

Solution 1

If possible, you should change the design of the database. Do not store repeating data in different columns, store them on different rows. So organize the tables so that you have the company name in a single column and hrstatus in another column. Possibly if you need you can have other columns to categorize the rows (for example orderno if the company names or statuses are ordered etc).

Addition, normalization:
The correct way to normalize your table depends on several factors but to get something to start with, could you consider something like:

Employee
- Srno as primary Key
- Cid (for identification of employee)
- Date,
- Name,
- ContactNumber,
- DOB,
- Age,
- Location,
- Qualification,

EmployeeHRStatus
- Srno (foreign key to Employee)
- HRStatus,
- StatusDate

EmployeeSentTo
- Srno (foreign key to Employee)
- SentTo,
- SentToDate

etc.

Posted 13-Dec-11 20:09pm

Wendelius

Updated 13-Dec-11 20:46pm

v2

Comments

Acharya Vikram 14-Dec-11 2:36am

Thanks 4 your reply
Actually I know the importance of normalization in the database structure and based on that I have done that before by making two tables but in my case its the need of client and situation are such that made me to make one table.
below are my fields can you help me in normalizing it..

Srno as primary Key
Cid (for identification of employee)
Date,
Name,
ContactNumber,
DOB,
Age,
Location,
Qualification,

HRStatus,
HRStatus1,
HRStatus2,
HRStatus3,
HRStatus4,
SentTo,
SentTo1,
SentTo2,
SentTo3,
SentTo4,
Remark,
Remark1

i have to show them as one...
thanx in advance

Wendelius 14-Dec-11 2:46am

Answer updated

Amir Mahfoozi 14-Dec-11 2:50am

+5 ;)

Wendelius 14-Dec-11 2:55am

Thank you :)

Acharya Vikram 14-Dec-11 3:26am

Thanx 4 ur help...

Wendelius 14-Dec-11 5:27am

You're welcome :) If you like, you can mark the answer as accepted if it helped

Acharya Vikram 14-Dec-11 5:45am

still i didn't got the intended answer bcoz
the main concept is I have candidates that visit multiple company in a day and their status is stored in HRStatus and every srno has all the details of employee and the company's in which the employee went with their status as HRSTATUS.
I need everything in a single row.
This is not possible when I normalize the table and that is the reason i was following such a database structure as my client is interested in having the all the details together along with company and hrstatus in a single row.

suggest me something by helping me a bit more!!

Wendelius 14-Dec-11 7:01am

Okay, I see. Despite of the fact that you need to report the data on the same row I would normalize the tables. But when you fetch the data you can get different statuses on the same row. One way is to repeat the same table in the query several times and use outer join. This is the 'traditional' way. A bit more elegant way is to use Pivot[^]

Acharya Vikram 14-Dec-11 8:02am

Actually I have datagridview which I have made specially for Save,Edit,Update.I have designed that grid specially for this particular client and situation,In order to suit that need and save my time on doing add,update again,I asked for an help.
I can do it with outer join but that will take time.
I have been doing all my work on my grid and connecting it via wizard in no time.
I will do everything again,but the only concern is of retrieving the records in a single row.

Wendelius 14-Dec-11 16:13pm

IMO how the data is shown and how it is stored should be separated. For this reason I think you should stick to normalized database structure. Did you test either the outer join or the pivot for example in SSMS to ensure that it's a proper approach for you. If not, create some small test tables and try the concept to see that it's working in your case before you make any dramatic changes to the project.

Sara Dark · Answer 2 · 2011-12-15T02:37:00

SQL

select *
from TotTab
where ('your_status_condition' = HRStatus1 AND 'your_company_name' = Companyname1) OR ('your_status_condition' = HRStatus2 AND 'your_company_name' = Companyname2) OR ('your_status_condition' = HRStatus3 AND 'your_company_name' = Companyname3)OR ('your_status_condition' = HRStatus4 AND 'your_company_name' = Companyname4)
OR ('your_status_condition' = HRStatus5 AND 'your_company_name2' = Companyname5)

I'm not sure if i understood your question right and i'm just a beginner but i wanted to try to help