Click here to Skip to main content
14,265,797 members
Rate this:
Please Sign up or sign in to vote.
See more:
facing problem in appending table1 with the columns of table2

Table User (id, name, Gender)
Table Qualification(id, name)
many to many relationship

Lets Say User Table is as Below
id, name, gender
1, john, male
2, smith, male
3,catherine, female

Qualification Table
User_id, Degree
1, Msc
1,BSC
3,Msc

What i want is.. to get a resultant table as

id, name, gender, Degree
1,john,male,Msc
1,john,male,Msc
2,smith,male,null/or remains empty
3,catherine,female,Msc

What I have tried:

i've tried
Select u.id, u.name, u.gender, q.degree
FROM user as u
INNER JOIN qualification as q
ON u.id=q.User_id


I know em doing it wrong way.. :( but couldn't find any solution
Posted
Updated 5-Mar-16 5:13am
Rate this:
Please Sign up or sign in to vote.

Solution 1

Change from INNER JOIN to LEFT OUTER JOIN

[EDIT]
You may find this article useful Visual Representation of SQL Joins[^]

And as an aside avoid using reserved words as table names or column names and if you really must then surround them with square brackets e.g. [user]
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

you'd have to change the order of query also as well as the type of join

select *
from qualifications a left outer join [user] b
on a.user_id = b.id
where b.id is not null
   
v3
Comments
CHill60 5-Mar-16 11:21am
   
That does not produce the results that the OP said they wanted. They specifically want the row where qualification is null, therefore the tables were in the right order and the Where clause (check your spelling) is not required

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100