query to get desired result

Question

1.00/5 (1 vote)

See more:

i have 2 tables ( dept and employee )

dept table structure

sno     deptname
1       hr
2       finance
3       accounts

employee table structure

empsno (pk )    deptsno( Fk)         empname
1                   1                 a
2                   1                 b
3                   1                 c   
4                   1                 d
5                   2                 e  
6                   2                 fry
7                   2                 ghy
8                   2                 abc
9                   3                 yun
10                  3                 john
11                  3                 ppp
12                  3                 xyz

i want to make query
to show record like this

this should be resuls after query executing

empsno              deptsno           empname
3                   1                 c   
7                   2                 ghy
11                  3                 ppp

help me to make query to get above output

[edit]Code block added - OriginalGriff[/edit]

Posted 3-May-14 23:23pm

qasimidl

Updated 3-May-14 23:43pm

v3

Add a Solution

Comments

Kornfeld Eliyahu Peter 4-May-14 5:29am

What is common between the records in your result?

Abhinav S 4-May-14 6:00am

What is your selection criteria?

King Fisher 5-May-14 2:07am

Why do you want to select this.

3 solutions

Solution 2

Try this:

SQL

;WITH MyIds AS
(
    --start from 3
    SELECT 3 AS empsno
    UNION ALL
    --next empsno must step by 4
    SELECT empsno +4
    FROM MyIds
    WHERE empsno <14
)
SELECT Emp.*
FROM Employee AS Emp RIGHT JOIN MyIds ON Emp.empsno = MyIds.empsno

The basic idea is to use CTE[^] to generate set of empsno's and then to join data from employee table ;)

[EDIT]
As Kornfeld Eliyahu Peter mentioned in the comment to my answer, there is another solution:

SQL

SELECT *
FROM Employee
WHERE empsno IN(3,7,11)

[/EDIT]

Posted 4-May-14 2:16am

Maciej Los

Updated 4-May-14 9:01am

v2

Comments

Kornfeld Eliyahu Peter 4-May-14 8:21am

Why? Why not 'empsno in (3, 7, 11)'?

Maciej Los 4-May-14 8:25am

It's another possibility. What if OP want to get 99 employees? Would you like to type 99. empsno?

Kornfeld Eliyahu Peter 4-May-14 8:26am

No. I want some clarification from OP :-)...

Solution 1

You can't really do that, because the results you show are not consistent: the final column of your results is not a unique value or a groupable value based on the other data you want to retrieve.
The first two columns are easy:

SQL

SELECT COUNT(empsno), deptsno FROM Employees GROUP BY deptsno

But there is no way to link a unique empname to the deptsno so you can't retrieve that info.
If you wanted the deptname, then that's easy:

SQL

SELECT COUNT(e.empsno), e.deptsno, d.deptname FROM Employees e 
JOIN depts d 
ON e.desptsno=d.sno
GROUP BY e.deptsno

Should do it.

Posted 3-May-14 23:48pm

OriginalGriff

Comments

Kornfeld Eliyahu Peter 4-May-14 5:55am

I can't see how you figured it out! Why count(*)? It seems to me that OP try to return some unrelated rows...

OriginalGriff 4-May-14 6:05am

You could be right - I read the number on the left as a count of rows - but a closer look says that could well be wrong. Let's see what the OP comes back with...

Kornfeld Eliyahu Peter 4-May-14 6:09am

IF comes back! You may scared him to death! :laugh:

OriginalGriff 4-May-14 6:28am

Aw! I didn't even show him my "big stick wiv nails in" yet... :laugh:

Abhinav S 4-May-14 6:00am

Is this what the OP wants?

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)

Darshan.Pa · Accepted Answer · 2014-05-04T20:04:00

After analyzing your result, i found that you require every 3rd employee in each department.

If is it so than following query may give you desired result.

SQL

select A.empsno,A.deptsno,A.empname
from
(
select ET.empsno,ET.deptsno,ET.empname,Row_Number() OVER (partition by DT.deptsno ORDER BY ET.empsno) as No
from EmpTable ET 
inner join DepTable DT on DT.deptsno=ET.deptsno
)A 
where A.No=3

query to get desired result

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us