Conversion from Union to join

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi friends,
I need help in my DB,i wanna convert a UNION Query Into Join Query.PlZ help me in this task.how can i convert that..............?

Two tables Table1 and Table2.One common field(userID) in both tables.two condition for searching(fullname like'%ab%') and (companyname like '%ab%').

Posted 3-Oct-10 20:10pm

PRADEEPPEDNEKAR

Add a Solution

2 solutions

Solution 2

<pre lang="sql">SELECT designation,fullname,company_name,country, FROM(SELECT fullname,company_name,designation,country, Row_number() OVER (ORDER BY fullname DESC) RowNum FROM (SELECT P.fullname,P.company_name, P.designation, P.country FROM People AS P inner JOIN Company AS C ON P.company_id = C.company_id WHERE ((P.fullname like 'a%') and )or(C.company_name like 'a% )GROUP BY P.fullname,P.company_name,P.designation,P.country, ) t) t1 WHERE RowNum BETWEEN (0*25+1) AND ((2+1)*25)

this is my query
it is giving me out put in jumbled format.
I need it in proper format.
Full name with 'a%' all columns and then companyname with 'a%'.
out put should be in this format
FUll name companyname
aabbc cccccc
abcc sssss
ab sssbshjhsj
ddfdfd aaabbbbaa
eryeyruy aabbbdd
ruhj ahfhff

Plz friends help me in this query.

Posted 7-Oct-10 0:35am

PRADEEPPEDNEKAR

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)

Hiren solanki · Accepted Answer · 2010-10-03T20:13:00

Solution 1

As per I understood your problem I can conclude below solution.

SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T1.userID = T2.userID
WHERE fullname LIKE '%ab%' AND companyname like '%ab%'

Please vote and Accept Answer if it Helped.

Posted 3-Oct-10 20:13pm

Hiren solanki

Comments

PRADEEPPEDNEKAR 5-Oct-10 7:59am

Thanks bro.But i want to bring the searched things(columns) in a order first its should display fullname details then companyname details

Hiren solanki 5-Oct-10 8:09am

you can have this columns selection inside select statement.