SQLTeam.com | Weblogs | Forums

Question about join data

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 !

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

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

1 Like

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 !

Thank you so much ! I did it ! ^^

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