How to get data from only one table.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi all,

I have two tables named 'X' and 'Y'. 'Y' table contains two columns 'code'(non unique) and 'Name'. 'X' table contains column like 'ID'(unique),'Code'(non Unique) etc. I want to take 'Name' column from table 'Y'and push it into table 'X' based on 'Code' column. My 'X' table contains rows 100 and 'Y' table contains rows '18'. I want only 100 rows as my final result. I did left join, i used sub-queries but i didn't get the required o/p. Column 'Code' is not unique in both tables.

Table 'X'----------------------------------------------------- Table 'Y'
ID.... Code ----------------------------------------------Code....Name
1...... abc ----------------------------------------------- abc..... Brown
2...... abc ------------------------------------------------abc..... Brown
3...... cdv ------------------------------------------------cdc..... ZXCVB
4...... cbc ------------------------------------------------onm..... lkmjnk
5...... onm

I wrote down below query to get data:

" select Y.Name,X.Code
FROM X
left join Y
on X.Code = E.Code
WHERE X.Code in (SELECT distinct Code FROM Y) "

I think by using ROW_NUMBER() function we can get this. Please help me.
Thanks.

Posted 27-Jan-15 19:20pm

Member 11407500

Add a Solution

3 solutions

Solution 1

Hello ,
I think you need a simple left outer join to get the desired out put . According to your requirement you need all rows from X.

Try this

select distinct x.id,x.code,y.name from X left outer join Y on  x.code=y.code

thanks
animesh

Posted 27-Jan-15 19:46pm

Animesh Datta

Comments

Member 11407500 28-Jan-15 2:14am

Hi Animesh,

I did this earlier, As my actual table X contains row count 188550 and Y table 40708. I am getting total records 196852. As both tables contains repeated 'code' value, that's why total rows i am getting are more. I want only 188550 rows as my final o/p along with Name from Y table. That's why i wrote sub-query in SQL. I try this using ROW_NUMBER Function too but i got more rows. I read somewhere to get data from one table, we have to use row_number() function along with sub-query or derived table. I tried but i didn't get required o/p.

Thanks,
Nitin

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)

Umer Akram · Accepted Answer · 2015-01-27T20:08:00

Solution 2

If there are different names in Table 'Y' against a single code then this will would not work

SQL

--- Query1
select distinct x.id,x.code,y.name from X left outer join Y on  x.code=y.code

you will have to change it something like this to get 100 rows each time:

SQL

--- Query2 (updated)
select x.code,MAX(y.name) as name
from X 
left outer join Y on  x.code=y.code
Group by x.code

If you are certern that name would not be changed in Table 'Y' which i doubt then you the Query1 else use the Query2.

Hope it helps

Posted 27-Jan-15 20:08pm

Umer Akram

Updated 27-Jan-15 20:28pm

v2

Comments

Member 11407500 28-Jan-15 2:26am

Thanks Umer. It is working as i expected.

Member 11407500 28-Jan-15 2:32am

Hi umer,

Your second solution is correct one. But my first Table X contains so many columns and i want to add 'Name' column from Y table to X view. To apply above condition all columns must be placed in Group by clause. I want different solution using row_number and Sub-Query. Please help me to do this.

Thanks,
Nitin

Umer Akram 28-Jan-15 4:40am

you have to share your tables structure and with sample of actual data so understand the problem. the solution was as per the provided information. more detail is required.

Member 11407500 · Accepted Answer · 2015-01-28T19:00:00

Hi all,

I have solved the problem. Thanks for your help. The query is like below:

SQL

SELECT E.CODE,DD.Name
From X
Left join
(SELECT CODE, Name FROM (SELECT ROW_NUMBER() OVER(partition by CODE Order by CODE) as rownum, CODE,Name FROM Y) as Y
where Y.rownum = 1) as DD
on E.CODE= DD.CODE

Thank you,
Nitin