Click here to Skip to main content
15,846,006 members
Please Sign up or sign in to vote.
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
Updated 4-Aug-22 7:49am
v3
Comments
Gerry Schmitz 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

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
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900