CODEWITHSUNDEEP
mysqlsqldatabasejoin
Ali
Ali

Reputation: 508

join two table to replace new value from 2th table if exist

I have two table like belew

Table1: 
CId -- Name -- Price -- MId
1       A      100   -- 1
2       B      110   -- 1
3       C      120   -- 1
4       D      120   -- 2

Table2:
Id -- UserId -- CId -- Price
1     1         2      200
1     2         2      200

I want to get data from Table one But if there is a record in Table2 that refrenced to Table1 CId then Price of Table2 replace with Price of Table1.

For example my UserId is 1 AND MId is 1 if I get data by mentioned senario I should get in result;

1       A      100  
2       B      200
3       C      120

Upvotes: 2

Views: 99

Answers (3)

Vahid Heydarinezhad
Vahid Heydarinezhad

Reputation: 126

CREATE TABLE table1(
    [cid] [int] NULL,
    [Name] [nvarchar](50) NULL,
    [price] [bigint] NULL,
    [MID] [int] NULL
)

CREATE TABLE [table2](
    [id] [int] NULL,
    [userid] [int] NULL,
    [CId] [int] NULL,
    [price] [bigint] NULL
)

GO
INSERT [dbo].[table1] ([cid], [Name], [price], [MID]) VALUES (1, N'A', 100, 1)
GO
INSERT [dbo].[table1] ([cid], [Name], [price], [MID]) VALUES (2, N'B', 110, 1)
GO
INSERT [dbo].[table1] ([cid], [Name], [price], [MID]) VALUES (3, N'C', 120, 1)
GO
INSERT [dbo].[table1] ([cid], [Name], [price], [MID]) VALUES (4, N'D', 120, 2)
GO
INSERT [dbo].[table2] ([id], [userid], [CId], [price]) VALUES (1, 1, 2, 200)
GO
INSERT [dbo].[table2] ([id], [userid], [CId], [price]) VALUES (1, 2, 2, 200)
GO


and  Query-----------------------------------

SELECT        t1.cid, t1.Name,
              case when t1.cid=t2.cid then t2.price else t1.price end as Price
FROM          table1  t1
INNER JOIN  table2 t2 ON t1.MID = t2.userid
where t2.userid=1

Upvotes: 0

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

SQL FIDDLE

try this

select t1.cid,t1.name,
case when t2.cid is null
then t1.price 
else t2.price 
end as Price
from table1 t1 left join table2 t2 on t1.cid =t2.cid
where t1.mid =1 AND (t2.UserId = 1 OR t2.UserId IS NULL);

Upvotes: 2

Vipin Jain
Vipin Jain

Reputation: 3756

You can get by left join where you check null value in second table. if second price is null then use first table's price.

SELECT t1.CId, t1.name 
CASE WHEN t2.price IS NULL 
THEN t1.price 
ELSE t2.price END AS Price 
FROM table1 t1 
LEFT JOIN table2 t2 
ON t1.CId = t2.CId 
WHERE WHERE t1.MId = 1 
AND (t2.UserId = 1 OR t2.UserId IS NULL);

Try This Hopeful this will work.

Upvotes: 1

Related Questions