MS SQL simple table query

[shinysk21]

Hi,

I hav a table as follows

Addrd EmpId status
 1      1      1
 2      1      2
 3      1      3
 4      2      1
 5      2      2
 

From the above table , I want to select empid with status 1, 2 and 3 i.e., the employee which has all the 3 status. How do i have a query for this? I am using MS SQL 2005.

Thanks in advance.

shinysk21

[sanjib65]

I suppose, there is a Foriegn key relationship with empid and statusid.
If I were you, i'd like to drag two controls. First, a GridView and second a DetailsView. In GridView, I'd go for edit command options and take a select command so that when you select empid, the all status values will be shown in DetailsView.

To understand the whole procedure you can see this link in MSDN:

http://msdn.microsoft.com/en-us/library/ms171924.aspx

[Frinavale]

Bytes has a quick couple of articles on how to use a database as well.
Check out:
http://bytes.com/topic/net/insights/...e-your-program
http://bytes.com/topic/net/insights/...rogram-part-ii

Are you having a problem trying to figure out what you need in your SQL query?


-Frinny

[semomaniz]

Here is the sql statement

Select empid from [TableName] where status =1 or status =2 or status =3 

[Frinavale]

@Semomaniz: I'm not great with SQL because I haven't used a database very much in the last few years....but I don't think your SQL query is going to result in what the user is looking for.

If you run:

Select empid from [TableName] where status =1 or status =2 or status =3

It will return all empIDs that have a status of 1, 2, or 3.
What the original poster is looking for is any empIDs that have a status of 1, 2, and 3.

I think this is going to require a compound SQL query.

Or maybe you could use the SUM...

This is probably the most complicated way to do this (like I said I'm not good with SQL) .... but here I go.

You know that they have to have a status of 1, 2, and 3.
1+2+3=6

This means that if the employee has all 3 statuses then adding up all of the statuses found for that employee should equal 6.


So you could do something like select all employee ids where the sum of the status's is 6:

SELECT empid, SUM(status)
FROM [TableName]
GROUP BY empid
HAVING SUM(status) = 6

-Frinny

[semomaniz]

You are right Frinny my sql statement does not work in this case. Did a test with yours and did get the required result.You r awesome.. Thanks

[Frinavale]

I don't think that it is the best solution though.
The solution should probably use 2 "Subqueries"...I'm just not sure how to do this and can't test it.

[Frinavale]

Does this work????

SELECT empID FROM [TableName] t1
WHERE t1.status = 1 
  AND 
  t1.status = (SELECT status FROM [TableName] t2
               WHERE t2.status = 2 AND t1.empID = t2.empID) 
  AND
  t1.status = (SELECT status FROM [TableName] t3
               WHERE t3.status = 3 AND t1.empID = t3.empID)

Like I said, I'm not that great with SQL.

-Frinny

[semomaniz]

Frinny i got an error with you code above. How ever i used stored procedure to get the required data. here is the stored procedure

CREATE Procedure testpro    
 
    @status1 int, --desired status id = 1
    @status2 int,    --status id =2
    @status3 int   --statusid =3
 
AS
 
-- Creating temp table to store employee id's wth status 1
Select addrd, empid, status Into #temptable1
From test
where status = @status1
 
 
-- Creating temp table to store employee id's wth status 2
Select addrd, empid, status Into #temptable2
From test
where status = @status2
 
 
-- Creating temp table to store employee id's wth status 3
Select addrd, empid, status Into #temptable3
From test
where status = @status3
 
Select test.empid as id from test 
    join #temptable2 on #temptable2.empid = test.empid 
     join #temptable3 on #temptable3.empid = test.empid
     join #temptable1 on #temptable1.empid = test.empid
group by test.empid
 
--Droping temptables
Drop TABLE #temptable1
Drop TABLE #temptable2
Drop TABLE #temptable3
GO
 

I am hoping/ guessing this is the best solution

[Frinavale]

What was the error message?
I wonder if you can preform a JOIN on a resulting subquery?

[MMcCarthy]

This will get you the result you want ...

SELECT Table1.empID
FROM Table1
group by empid
having count(empid)=3
 

However, your problems arise because you have structured your data badly. I would suggest you check out our article on Database Normalization and Table Structures in the Insights seceion.

Mary

[shinysk21]

Thank you all for the replies... I used

SELECT empid, SUM(status) 
FROM [TableName] 
GROUP BY empid 
HAVING SUM(status) = 6

and it worked..

Thank you all once again..

[NeoPa]

Unless you know more about the possible values than you've stated in your OP (Maybe Status can only be between 1 & 3 at all) I suggest you may want to be a little more specific :

SELECT   [empid]
FROM     [TableName]
WHERE    [Status] Between 1 & 3
GROUP BY [empid]
HAVING   SUM([Status]) = 6

Essentially, only you know the exact requirements. We can only suggest from the information you share.

[shinysk21]

Thank you NeoPa!! I got to know an another way too from you. Thank you so much

[NeoPa]

Very pleased to help.

Frinny put out a call so we like to try to help :)