Select between two tables, two columns
Clash Royale CLAN TAG#URR8PPP
Select between two tables, two columns
I am trying to find all records in TableA which are not in TableB by comparing 2x columns in each table. I have tried all sorts of queries but I cannot figure it. Any help would be much appreciated.
TableA Has ColumnA and ColumnDate
TableB Has ColumnA and ColumnDate
So I only want to see which records in TableA do not match TableB for both columns.
Also, TableA may have several matching fields so I need to group them to have only 1 entry into TableB for each match.
Yes I want to insert the new records into TableB.
It appears harder than expected.
Something like this:
select ColumnDate, ColumnA
from TableA
where
(( ColumnDate not in (select ColumnDate from TableB) )
and ( ColumnA not in (select ColumnA from TableB) ))
group by ColumnA, ColumnDate;
Thank you Strawberry. I have searched for an answer, explained what I'm looking for, what the expected output is and left a sqlfiddle. What else should I be doing to get some help?
– Jamie T
Aug 8 at 3:37
How come lukass gets a fiddle, but we don't? Edit your question in line with the accepted answer at the linked question- or don't
– Strawberry
Aug 8 at 6:47
Here is a SQLFIDDLE. Example, ColumnA has multiple entries for '1027'. But the reults should only show 2x results for 1027 because 1027 is already in TableB for the other dates. Should only show 11 & 12th Feb for 1027: sqlfiddle.com/#!9/d70c9a – Jamie T 2 days ago
– Jamie T
Aug 9 at 2:07
1 Answer
1
There are two approaches to this, If I want to just sync two tables I use straight INSERT SELECT
.
INSERT SELECT
INSERT INTO TableB (ColumnA, ColumnDate)
SELECT FROM TableA (ColumnA, ColumnDate)
ON DUPLICATE KEY UPDATE ColumnDate = ColumnDate
But be careful, you need to have Primary Key or Unique index on TableB, so the engine will only insert new rows based on uniqueness/pk of the receiving table.
Thanks Lukas. This isn't quite what I am loking for. TableA will have multiples of the same ColumnA and ColumnDate which are identical. I want to group the (into 1 record per match, and insert into TableB if there is no match in TableB.
– Jamie T
Aug 6 at 5:50
Here is a SQLFIDDLE. Example, ColumnA has multiple entries for '1027'. But the reults should only show 2x results for 1027 because 1027 is already in TableB for the other dates. Should only show 11 & 12th Feb for 1027: sqlfiddle.com/#!9/d70c9a
– Jamie T
Aug 6 at 6:41
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Aug 6 at 6:56