Question about join data

Transact-SQL
1

Dear all,

I have a question about join data.Give me an advice , thank you so much !
I have table A , table B
Table A:
ProductID(PK),DataA1,DataA2,DataA3
Table B:
ImageID,ProID(FK),DataB1,DataB2,DataB3

My sql statement : select A.ProductID,A.DataA1,A.DataA2,A.DataA3,B.DataB1,B.DataB2,B.DataB3 where A.ProductID=B.ProID

My problem:
In case,table B does not have any records with ProductID respectively (mean that we only have records in table A).i want to replicate data in each field as following :
A.DataA1 -> B.DataB1
A.DataA2 -> B.DataB2
A.DataA3 -> B.DataB3

if table B have records, we can get data normally !
Give me an advice or show me the way to solve problem !

Thank you so much !

2

hi i am trying to do this
this is very simple and easy !!!

i have first created sample data .. is this what data looks like for you ????
what i have done .. is it what you want !!

please click arrow to the left for drop create sample data 
drop table Table_A
go 

create table Table_A
(
ProductID int IDENTITY(1,1) NOT NULL,
DataA1 varchar(10),
DataA2 varchar(10),
DataA3 varchar(10),
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([ProductID] ASC)
)
go 


drop table Table_B
go 

create table Table_B
(
ImageID int IDENTITY(1,1) NOT NULL,
ProdID int ,
DataB1 varchar(10),
DataB2 varchar(10),
DataB3 varchar(10),
CONSTRAINT [PK_ProdId] PRIMARY KEY CLUSTERED (ProdID ASC)
)
go 

ALTER TABLE [dbo].[Table_A]     
ADD CONSTRAINT FK_Product_ProductID FOREIGN KEY (ProductID)     
    REFERENCES [dbo].Table_B(ProdID)     
GO 

ALTER TABLE [Table_A] NOCHECK CONSTRAINT FK_Product_ProductID;
go 

insert into Table_A select 'okfine','pyaar','dede'
insert into Table_A select 'nowwhat','nam','keen'
insert into Table_A select 'puk','lan','nm'
go 

Insert into Table_B select 1,null,null,null
Insert into Table_B select 2,'kkkk','jjjj','ssss'
Insert into Table_B select 3,null,null,null
go 

ALTER TABLE [Table_A] CHECK CONSTRAINT FK_Product_ProductID;
go 

select 'Table A Sample Data',* from Table_A 
select 'Table B Sample Data',* from Table_B 
go

image
image1211×1015 12.8 KB

please click arrow to the left for SQL 
SELECT 'SQL Output',
       a.dataa1, 
       a.dataa2, 
       a.dataa3, 
       Isnull(b.datab1, a.dataa1) AS datab1, 
       Isnull(b.datab2, a.dataa2) AS datab2, 
       Isnull(b.datab3, a.dataa3) AS datab3
FROM   table_a a 
       JOIN table_b b 
         ON a.productid = b.prodid

image
image1228×269 5.47 KB

1 Like
3

Thank you so much for sampling data !
The final result is 100% which I wish !
But in Table B ,sometime we will not have record which "ProID" field with data is 3 !

Thank you again !

4

Thank you so much ! I did it ! ^^

5

When you know there will be occasions where there won't be a corresponding record in table B, you must use a LEFT OUTER JOIN, as in:

SELECT 'SQL Output',
   a.dataa1, 
   a.dataa2, 
   a.dataa3, 
   Isnull(b.datab1, a.dataa1) AS datab1, 
   Isnull(b.datab2, a.dataa2) AS datab2, 
   Isnull(b.datab3, a.dataa3) AS datab3
FROM   table_a a 
   LEFT OUTER JOIN table_b b 
     ON a.productid = b.prodid
1 Like