Single query for Data selection

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

I have two table Jobs(name,job1,job2) and Users(name), while i m using where cluase getting this records

1.

Name job1 job2
A 5 88
B 8 4
C 6 10
Where Dep='Op'
2.

Name job1 job2
A 1 20
B 3 25
C 5 11
Where Dep='SA'
But i want single fetchable data like below after applying both where Conditions

Name job1 job2 job1 job2
A 5 88 1 20
B 8 4 3 25
C 6 10 5 11

Any suggestion or Help.

Posted 5-May-12 0:43am

SDAP_INDIA

Add a Solution

3 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)

VJ Reddy · Answer 1 · 2012-05-05T02:03:00

As seen from the question two tables are given,
Jobs(name,job1,job2) and Users(name). But in the results shown in the question, the Where Dep='Op' is given, from which it appears that there is one more column Dep in the Jobs table. Further the Users table is not used for the results shown.

If there is a column Dep in the Jobs table then the following sql statement can be used

SQL

SELECT Jobs1.Name, Jobs1.Job1, Jobs1.Job2, Jobs2.Job1, Jobs2.Job2
FROM
    (SELECT Name, Job1, Job2 
     FROM Jobs
     WHERE Dep = 'Op')  Jobs1
JOIN
    (SELECT Name, Job1, Job2
     FROM Jobs
     WHERE Dep = 'SA') Jobs2
ON Jobs1.Name = Jobs2.Name

[Edit] From the comment given to the Solution 1, it is seen that Dep is available in Users Table. Accordingly the Query is modified [/Edit]

SQL

SELECT Jobs1.Name, Jobs1.Job1, Jobs1.Job2, Jobs2.Job1, Jobs2.Job2
FROM
    (SELECT Jobs.Name, Job1, Job2
     FROM Jobs
     JOIN Users ON Jobs.Name = Users.Name
     WHERE Users.Dep = 'Op')  Jobs1
JOIN
    (SELECT Jobs.Name, Job1, Job2
     FROM Jobs
     JOIN Users ON Jobs.Name = Users.Name
     WHERE Users.Dep = 'SA') Jobs2
ON Jobs1.Name = Jobs2.Name

Arul R Ece · Answer 2 · 2012-05-05T01:10:00

Solution 1

SQL

select a.name ,a.job1,a.job2,b.job1,b.job from OP  a join SA b where a.Name=b.Name

Posted 5-May-12 1:10am

Arul R Ece

v2

Comments

SDAP_INDIA 5-May-12 8:03am

i m using

1. select a.name,b.job1,b.job2 from Jobs b inner join Users a on a.name=b.name where b.dep='OP'

2. select a.name,b.job1,b.job2 from Jobs b inner join Users a on a.name=b.name where b.dep='SA'

I want result of both query result like below in a single selection query.

Name job1 job2 job1 job2
A 5 88 1 20
B 8 4 3 25
C 6 10 5 11

Mantu Singh · Answer 3 · 2012-05-05T02:06:00

Solution 4

Use the following query.......

SQL

select a.name ,a.job1,a.job2,b.job1,b.job2 from Jobs  a join users b where a.Name=b.Name where a.Dep In('OP','SA') OR b.Dep in ('OP','SA')

Best of luck........

Hope it helps.....

Posted 5-May-12 2:06am

Mantu Singh