CODEWITHSUNDEEP
sqlsql-server
Vinoth
Vinoth

Reputation: 2439

Getting data from 2 tables when they dont have any primary-foregin key relationship

I have two tables which are data sources say A,B. I want to fetch data for 4 columns from these tables. The 4 columns are present in the both the tables. 'A' table may or may not have values for the one or all the 4 columns. But for sure B table has values for all of them. So if it is present in A i have to select from them or else i have select from B. Say Col1, Col2 are present in A and col3 and col4 are not in A table, then it should fetch col1, col2 from A and col 3, col4 from B table.

I am not good in SQL. Can someone help me with this? Hope my question is clear. If not please let me know

Upvotes: 0

Views: 760

Answers (3)

Manatherin
Manatherin

Reputation: 4187

You can do joins without a relationship between tables. The example below shows what i think you are aiming for.

DECLARE @tableone TABLE(id int,FirstName varchar(50),Secondname varchar(50))
insert into @tableone values(1,null, null);
insert into @tableone values(2,null, 'Dev');
insert into @tableone values(3,'Arjun', null);
insert into @tableone values(4,'Jack', 'Bilalo');

DECLARE @tabletwo TABLE(id int,FirstName varchar(50),Secondname varchar(50))
insert into @tabletwo values(1,'John', 'cena');
insert into @tabletwo values(2,'Michel', 'Dev');
insert into @tabletwo values(3,'Arjun', 'Ramn');
insert into @tabletwo values(4,'Jack', 'Bilalo');

SELECT A.Id, COALESCE(A.FirstName,B.FirstName) As Firstname, COALESCE(A.Secondname,B.Secondname) As Secondname
FROM @tableone A
    JOIN @tabletwo B
        ON A.id = B.id

Upvotes: 1

Satinder singh
Satinder singh

Reputation: 10198

Live Demo

http://sqlfiddle.com/#!3/dbcfe/1

Sample code 

create table tableone(id int,FirstName varchar(50),Secondname varchar(50))
insert into tableone values(1,'John', 'cena');
insert into tableone values(2,'Michel', 'Dev');
insert into tableone values(3,'Arjun', 'Ramn');
insert into tableone values(4,'Jack', 'Bilalo');

create table tabletwo(id_tb1 int,"address" varchar(150))
insert into tabletwo values('1','Uk');
insert into tabletwo values('2','USA');
insert into tabletwo values('3','China');
insert into tabletwo values('4','India');

select a.FirstName,a.SecondName,b.address as location 
from tableone a inner join tabletwo b
on a.id=b.id_tb1

Upvotes: 0

Geoff Patterson
Geoff Patterson

Reputation: 211

You can use a LEFT OUTER JOIN to find a matching in A if it exists. If a matching row does not exist, the row from B will still remain in the query. In cases where A has a NULL value for one of the columns, COALESCE() can be used to fall back on B's value for that column.

I wasn't clear from your question on which criteria you would like to use to match rows between A and B, but maybe a query like the following will help if you fill in that detail.

SELECT COALESCE(A.col1,B.col1)
        , COALESCE(A.col2,B.col2)
        , COALESCE(A.col3,B.col3)
        , COALESCE(A.col4,B.col4)
FROM B
LEFT OUTER JOIN A
    ON A.id = B.id

Upvotes: 3

Related Questions