Need help for SQL query (how to join two query)

Question

0.00/5 (No votes)

See more:

Hi all

SQL

select p.title from project_info pi inner join project_member pm where pi.projectid = pm.projectid and pm.name = 'bhagirathi'

union 

select p.title form project_info pi inner join project_test_member ptm where pi.projectid = ptm.projectid and ptm.name = 'bhagirathi';

How to solve the above problem without using union ,in one select query?

Thanks In advance.

Posted 15-Apr-12 23:44pm

bhagirathimfs

Updated 16-Apr-12 1:42am

P.Salini

v3

Add a Solution

Comments

Herman<T>.Instance 16-Apr-12 9:18am

what is your exact goal? please show example

bhagirathimfs 16-Apr-12 9:47am

bhagirathi is an employee.
some projects are assigned to him and stored in project_member table and some testing projects are assigned to him and stored in project_test_member table.

Now i want to see the projects assigned(both normal project and the test project) to bhagirathi.

I have written the above code and it is showing the result but i want use join instant of union.

2 solutions

Solution 1

you must use union
its the good way

SQL

select t  from (

select p.title as t from project_info pi 
Right join project_member pm on pm.projectid = pi.projectid 
right join project_test_member ptm on pi.projectid = ptm.projectid 
) where t = 'bhagirathi';

not sure may it works

Posted 16-Apr-12 1:40am

Photon_

Updated 16-Apr-12 2:10am

v3

Comments

bhagirathimfs 16-Apr-12 7:45am

It is possible to solve the above problem using any join.

Thanks

Photon_ 16-Apr-12 8:05am

in union rows are joined and in join columns are joined do you still think the same its easy to do with union or intersection

Photon_ 16-Apr-12 8:11am

if it solve your problem please upvote

bhagirathimfs 16-Apr-12 9:50am

sry photon here bhagirathi is not the title of the project it is an employee name for which i want to see the project titles assigned to him.

Photon_ 16-Apr-12 10:38am

Sorry i'll update it 2 Amar this time i'm from mobile and I think you can make some changes to get exact what you want

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)

kishore sharma · Accepted Answer · 2012-04-16T01:46:00

Solution 2

Try Like This

SQL

select p.title from project_info pi inner join project_member pm 
on
 pi.projectid = pm.projectid 
inner join
project_test_member ptm 
on
 pi.projectid = ptm.projectid 
where  ptm.name = 'bhagirathi' and  pm.name = 'bhagirathi'

Posted 16-Apr-12 1:46am

kishore sharma

Comments

bhagirathimfs 16-Apr-12 7:53am

It is showing null.

Herman<T>.Instance 16-Apr-12 9:18am

what happens if you set the last AND to OR ?

bhagirathimfs 16-Apr-12 9:47am

it is showing the projects of project_test_member not the project_member project if we use or .

Herman<T>.Instance 16-Apr-12 10:40am

and when where ptm.name = 'bhagirathi' or pm.name = 'bhagirathi' is changed to where ( ptm.name = 'bhagirathi' or pm.name = 'bhagirathi' )

bhagirathimfs 17-Apr-12 2:54am

Yes it is working

Thanks for solving my problem
Thanks a ton

Herman<T>.Instance 17-Apr-12 3:39am

maybe yo could accept solution 2, so this question is marked as answered and agreed.

kishore sharma 17-Apr-12 3:43am

thanks for suggestions about query