How to write SQL query to pick one record from two table which are having different column values in SQL Server

vkc1a9a2 于 2023-06-21 发布在 SQL Server

关注(0)|答案(2)|浏览(156)

I have following 2 tables TableA and TableB need to find the difference and get the result from TableA if there is any changes in any of the Cost columns. Ignore the same records from TableA and TableB I have tried with EXCEPT which is yielding the wrong result.

TABLEA
| JobNo | Brand | Cost1 | Cost2 | Cost3 | Cost4 | Cost5 |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 175196 | Vintage | 216347 | 217429 | 218516 | 219609 | 220707 |
| 200330 | Vintage | 427169 | 429305 | 431451 | 433608 | 435776 |
| 211746 | Modern | 285621 | 287049 | 288484 | 289927 | 291376 |

TABLEB

JobNo	Brand	Cost1	Cost2	Cost3	Cost4	Cost5
175196	Vintage	227030	228165	229306	230453	231605
200330	Vintage	444551	446774	449007	451252	453509
211746	Modern	285621	287049	288484	289927	291376

Result Expected

JobNo	Brand	Cost1	Cost2	Cost3	Cost4	Cost5
175196	Vintage	216347	217429	218516	219609	220707
200330	Vintage	427169	429305	431451	433608	435776

SELECT JobNo,Brand,Cost1,Cost2,Cost3,Cost4,Cost5 FROM TableA(NOLOCK)
EXCEPT
SELECT JobNo,Brand,Cost1,Cost2,Cost3,Cost4,Cost5 FROM TableB(NOLOCK)

sql-server

来源：https://stackoverflow.com/questions/76515042/how-to-write-sql-query-to-pick-one-record-from-two-table-which-are-having-differ

2条答案

按热度按时间

enyaitl31#

find the differences between TableA and TableB based on the Cost columns and retrieve the results from TableA if there are any changes

You can use this:

SELECT JobNo, Brand, Cost1, Cost2, Cost3, Cost4, Cost5
FROM (
  SELECT *
  FROM TableA

  EXCEPT

  SELECT *
  FROM TableB
) AS diff;

赞(0）回复(0）举报 2023-06-21

kxe2p93d2#

The EXCEPT is right, you can see it here:

http://sqlfiddle.com/#!18/f6fc2/2

赞(0）回复(0）举报 2023-06-21

我来回答

How to write SQL query to pick one record from two table which are having different column values in SQL Server

2条答案

相关问题

热门标签

最新问答