Can anyone help me to get this output.?

Question

5.00/5 (1 vote)

See more:

Hi All,

I have two tables as shown below,

Table1:

SQL

---------------------------------------
SNO| Name  | ID| 
----------------------------------
1  | Aj Ali  | A001 |
2  | DB Kola | A002 |  
3  | Raj Sir | A003 |
----------------------------------

Table2 :

SQL

----------------------------------
SNO| Name  | 
----------------------------------
10  | Ali Aj  | 
20  | DB Kola Mj|   
30  | Sir Raj | 
----------------------------------

Now i need output like this,

SQL

----------------------------------
SNO| Name  | ID |
----------------------------------
10  | Ali Aj  |  A001 |
20  | DB Kola |  A002 |     
30  | Sir Raj |  A003 |  
----------------------------------

Here the problem is the "name" column values are not in the same order (First Name and SurName).

Please help me on this.

What I have tried:

SQL

select Table2.SNO,Table2.Name,Table1.ID from Table1 inner join Tabl2 on Table1.Name=Table2.Name

--------------------
And my output is only this,

SQL

----------------------------------
SNO| Name  | ID |
----------------------------------
20  | DB Kola |  A002 |     
----------------------------------

Posted 9-Feb-17 4:25am

Raj 0203

Updated 9-Feb-17 6:53am

F-ES Sitecore

v2

Add a Solution

3 solutions

Solution 2

As it is, you cannot be certain of the correct linkage between these two table though it seems to suggest the name fields in the respective tables. But the name values are not identical. That is the result of poor database design. Name cannot be unique, just imagine how many people can have the same names and the different ways of writing a name. Rightfully, they should be linked by the ID fields like this:

table 1        table 2
SNO            SNO
ID  ---------- ID
Name

This is just one example, the actual table design will depend on the requirements. Start learning Introduction to database design[^] and 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^] and re-design your database.

Posted 9-Feb-17 4:48am

Peter Leow

Comments

Karthik_Mahalingam 9-Feb-17 23:11pm

5

Solution 3

There is no automatic way to ensure that DB Kola is DB Kola Mj or not, just like 2 people can have the same name.
That is why no one use a name as a key between tables, never.
As a database designer, it is your job to ensure that such situation never happen.

Posted 9-Feb-17 6:53am

Patrice T

Comments

Karthik_Mahalingam 9-Feb-17 23:11pm

5

Patrice T 10-Feb-17 3:08am

Thank you

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)

F-ES Sitecore · Accepted Answer · 2017-02-09T04:33:00

You need to re-design your tables to split the name into the relevant parts, so have a field for first name, surname, title etc. Secondly joining on text fields like name is very prone to error, so instead of storing the name in Table2 you should store the ID of table 1

SQL

----------------------------------
SNO| SNO_ID  | 
----------------------------------
10  | 1  | 
20  | 2 |   
30  | 3 | 
----------------------------------

That will let you do a query to get you the data you need

SQL

SELECT t2.SNO, t1.Name, T1.ID from Table1 t1 join Table1 t2 on t2.SNO_ID = t1.SNO