Compare two tables and store result in third table

Question

1.00/5 (1 vote)

See more:

so i have 3 table 1.StfnStbl 2.StfnStbl_Temp 3.StfnStbl_Only_Diff , my program first day write StfnStbl and copy it self StfnStbl_Temp, the second day cancel StfnStbl and recreate with new data , i want at this time compare StfnStbl and StfnStbl_Temp and difference put inside of third table StfnStbl_Only_Diff

here you find 3 csv dumped of my mysql i change last record 199 UNIET (in 1 table) and in second table is 199 UNIETH
Hi guys i have 2 table in Mysql like this

first table have name StfnStbl

idt	Symbol
1	ETHBTC
2	BNBBTC
3	QTUMETH
4	GASBTC
5	BNBETH
6	WTCBTC
7	QTUMBTC
8	ZRXBTC
9	KNCBTC
10	MTLBTC
11	EOSBTC
12	ETCETH
13	ETCBTC
14	DNTBTC
15	OAXBTC
16	BTGBTC
17	REQBTC
18	VIBBTC
19	TRXBTC
20	TRXETH
21	POWRBTC
22	ARKBTC
23	STORJBTC
24	KMDBTC
25	NULSBTC
26	XMRBTC
27	XMRETH
28	BATBTC
29	LSKBTC
30	GTOBTC
31	NEBLBTC
32	NAVBTC
33	PIVXBTC
34	STEEMBTC
35	STEEMETH
36	BLZBTC
37	ZILBTC
38	LOOMBTC
39	LOOMETH
40	REPBTC
41	CVCBTC
42	QKCBTC
43	TRXBNB
44	ETCUSDT
45	ETCBNB
46	KEYETH
47	ARDRBTC
48	DOCKBTC
49	POLYBTC
50	RVNBTC
51	MITHBTC
52	MITHBNB
53	TRXXRP
54	ONGBTC
55	MATICBNB
56	MATICBTC
57	PHBBTC
58	PERLBTC
59	CHZBTC
60	BEAMBTC
61	ETCBUSD
62	ARPABTC
63	CTXCBTC
64	VITEBTC
65	FTTBTC
66	BNBTRY
67	BUSDTRY
68	USDTTRY
69	DREPBTC
70	TCTBTC
71	STPTBTC
72	USDTIDRT
73	HIVEBTC
74	HIVEUSDT
75	MDTBTC
76	STMXBTC
77	PNTBTC
78	USDTUAH
79	BUSDBIDR
80	USDTBIDR
81	SNXBTC
82	MKRBTC
83	AVABTC
84	YFIBTC
85	USDTDAI
86	BUSDDAI
87	CRVBTC
88	UNIETH


second table  StfnStbl_temp
idt	Symbol
1	ETHBTC
2	BNBBTC
3	QTUMETH
4	GASBTC
5	BNBETH
6	WTCBTC
7	QTUMBTC
8	ZRXBTC
9	KNCBTC
10	MTLBTC
11	EOSBTC
12	ETCETH
13	ETCBTC
14	DNTBTC
15	OAXBTC
16	BTGBTC
17	REQBTC
18	VIBBTC
19	TRXBTC
20	TRXETH
21	POWRBTC
22	ARKBTC
23	STORJBTC
24	KMDBTC
25	NULSBTC
26	XMRBTC
27	XMRETH
28	BATBTC
29	LSKBTC
30	GTOBTC
31	NEBLBTC
32	NAVBTC
33	PIVXBTC
34	STEEMBTC
35	STEEMETH
36	BLZBTC
37	ZILBTC
38	LOOMBTC
39	LOOMETH
40	REPBTC
41	CVCBTC
42	QKCBTC
43	TRXBNB
44	ETCUSDT
45	ETCBNB
46	KEYETH
47	ARDRBTC
48	DOCKBTC
49	POLYBTC
50	RVNBTC
51	MITHBTC
52	MITHBNB
53	TRXXRP
54	ONGBTC
55	MATICBNB
56	MATICBTC
57	PHBBTC
58	PERLBTC
59	CHZBTC
60	BEAMBTC
61	ETCBUSD
62	ARPABTC
63	CTXCBTC
64	VITEBTC
65	FTTBTC
66	BNBTRY
67	BUSDTRY
68	USDTTRY
69	DREPBTC
70	TCTBTC
71	STPTBTC
72	USDTIDRT
73	HIVEBTC
74	HIVEUSDT
75	MDTBTC
76	STMXBTC
77	PNTBTC
78	USDTUAH
79	BUSDBIDR
80	USDTBIDR
81	SNXBTC
82	MKRBTC
83	AVABTC
84	YFIBTC
85	USDTDAI
86	BUSDDAI
87	CRVBTC
88	UNIETH


third table  StfnStbl_Only_Diff ( what i aspect )
idt	Symbol
88	UNIET

i want  store  difference of first table in third  
example in this case  first table have at position 2 etcbnb and in table 2 etcusdt  is difference i want store in third table etcbnb , how is possible do that ??

What I have tried:

i tryed  to 
insert into StfnStbl_Only_Diff (ODif)
select StfnStbl.ODif from StfnStbl 
inner join StfnStbl_temp on StfnStbl.symbol = StfnStbl.symbol 

but in this mode i cant sotre in third table

Posted 3-Aug-22 4:56am

armanvaneld

Updated 4-Aug-22 6:49am

v3

Add a Solution

Comments

[no name] 3-Aug-22 11:48am

Select and change the sign (difference) on one table; then group (sum) it with the other into a third.

Member 15627495 3-Aug-22 12:31pm

hello !

look at UNION and INTERSECT statements in SQL.

as UNION can merge two queries results ,
INTERSECT will bring only differences between the two tables.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-08-03T06:50:00

Solution 1

If you were using MSSQL it would be simple

SQL

select id, symbol
from StfnStbl
except
select id, symbol
from StfnStbl_temp;

but according to this site - MySQL: INTERSECT Operator[^] - you can't do that in MySQL

You could try something like this for your simple example

SQL

select a.id, a.symbol, b.id, b.symbol
into #thirdtable
from StfnStbl a
inner join StfnStbl_temp b on a.id = b.id
where a.symbol<>b.symbol

If you want to know what is missing from the second table then you would probably want to use a LEFT OUTER JOIN and also test for b.id is null

Posted 3-Aug-22 6:50am

CHill60

Comments

[no name] 3-Aug-22 13:10pm

Hi i use Mysql i tryed to modify to but not work return me Undeclared variable: (id not interest) StfnStbl_Only_Diff
o_O
SELECT StfnStbl.Symbol , StfnStbl_Temp.Symbol
into StfnStbl_Only_Diff
FROM StfnStbl
INNER JOIN StfnStbl_Temp on StfnStbl.Symbol = StfnStbl_Temp.Symbol
WHERE StfnStbl.Symbol<>StfnStbl_Temp.Symbol

CHill60 4-Aug-22 3:34am

You implied that the name of the column in the tables was "id" and the way you described the problem was that id=2 was the row you wanted to insert into a third table. Your amended query will get the same results - but by accident rather than by design.
To get accurate solutions you must provide accurate information - that should include the correct names of columns in your tables, some sample data (more than you have given here - I would suggest at least 2 mismatching rows in this case, but not so much it becomes onerous to type). You should also include expected results for the sample data given.

[no name] 4-Aug-22 4:52am

ok one moment i attach .csv

[no name] 4-Aug-22 5:11am

so i have 3 table 1.StfnStbl 2.StfnStbl_Temp 3.StfnStbl_Only_Diff , my program first day write StfnStbl and copy it self StfnStbl_Temp, the second day cancel StfnStbl and recreate with new data , i want at this time compare StfnStbl and StfnStbl_Temp and difference put inside of third table StfnStbl_Only_Diff

here you find 3 csv dumped of my mysql i change last record 199 UNIET (in 1 table) and in second table is 199 UNIETH

StfnStbl_202208041053.csv
https://mega.nz/file/a4NTibgL#WM-riEGfhQl3zAZAirw0GTAiY7JoWGJTsLxrKRSdzGM

StfnStbl_Only_Diff_202208041054.csv
https://mega.nz/file/790wHYbB#ZTNx2Nny41NTNiRE4PDAulH13xIIRu8EBRBV1Buv_I4

StfnStbl_Temp_202208041053.csv
https://mega.nz/file/CpczzaxR#fccfK6E8a04M-8lv-dj6gvMz7-iARCrrPoQEiDezKkI

CHill60 4-Aug-22 11:43am

Sorry, I'm not opening unknown files. All you had to do was provide the information in your question

[no name] 4-Aug-22 12:49pm

i update the question with content of file and what i aspect arrive in third file thanks again

CHill60 8-Aug-22 5:15am

Those two files that you have now posted are identical. The query I posted 4 days ago is the solution to your problem if you use idt where I have used id
The query you posted in the comments here is wrong - you cannot join the tables on Symbol

Maciej Los 3-Aug-22 13:39pm

5ed!