Doen February 2016

Sql queries usng SELECT statement

I have these table from a database. The table WORKER has 2 fields one of them is ID_W(number) and the other is DUTY(text). WORKER(ID_W,DUTY)

I want to create an sql query that selects only ID_W(id of worker) which have the same DUTY where DUTY is a text type field. Can someone help me? I want to use an aggregate function but none of them helps.

Answers


Tim Southard February 2016

I used a temp table for an example but this would do what you are wanting I believe:

SELECT Duty,
STUFF((
    SELECT ', ' + CAST(ID_W AS VARCHAR(MAX)) 
    FROM #WORKER 
    WHERE Duty = T.DUTY
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS IDs FROM #WORKER T
GROUP BY Duty


Siyual February 2016

If I am understanding your question correctly, you are looking for only the IDs of the workers whose DUTY field matches that of another worker.

You can do this with a WHERE EXISTS clause, but you need to CONVERT the TEXT column into a VARCHAR (MAX) in order to compare them. This conversion on both sides will make the query expensive, but this is another way to do it:

Select  ID_W
From    WORKER  A
Where   Exists
(
    Select  *
    From    WORKER  B
    Where   A.ID_W <> B.ID_W
    And     Convert(Varchar (Max), A.DUTY) = Convert(Varchar (Max), B.DUTY)
)

Post Status

Asked in February 2016
Viewed 2,634 times
Voted 6
Answered 2 times

Search




Leave an answer