Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

VB.net SQL Joining two tables based on three criteria

Hi

I have two tables [Bank] and [Payments]
They both have three matching columns: [DATE], [LINK ID] and [AMOUNT]
I need to pull a query where all three of these columns have the same data
and then a query where this is not the case

What SQL code would I use to achieve these two scenarios?
0
Murray Brown
Asked:
Murray Brown
3 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Select [DATE], [LINK ID], [AMOUNT]
FROM [Bank]

INTERSECT

Select [DATE], [LINK ID], [AMOUNT]
FROM [Payments]

Open in new window


and

Select [DATE], [LINK ID], [AMOUNT]
FROM [Bank]

EXCEPT

Select [DATE], [LINK ID], [AMOUNT]
FROM [Payments]

Open in new window

0
 
nebb-tgrCommented:
SELECT * FROM Bank AS A 
WHERE
EXISTS (
SELECT * FROM Payments WHERE Date = A.Date AND [LINK ID] = A.[LINK ID] AND Amount = A.Amount
)

Open in new window


SELECT * FROM Bank AS A 
WHERE
NOT EXISTS (
SELECT * FROM Payments WHERE Date = A.Date AND [LINK ID] = A.[LINK ID] AND Amount = A.Amount
)

Open in new window

0
 
PortletPaulCommented:
Mmmm, I'd normally expect a one (Bank) to many (Payments)

So, for something completely different:

SELECT
      Bank.DATE
    , Bank.[LINK ID]
    , Bank.AMOUNT
    , SUM(Payments.AMOUNT)
FROM Bank
INNER JOIN Payments ON Bank.DATE = Payments.DATE and Bank.[LINK ID] = Payments.[LINK ID]
GROUP BY
      Bank.DATE
    , Bank.[LINK ID]
    , Bank.AMOUNT
;

Open in new window

SELECT
      Bank.DATE
    , Bank.[LINK ID]
    , Bank.AMOUNT
FROM Bank
LEFT JOIN Payments ON Bank.DATE = Payments.DATE and Bank.[LINK ID] = Payments.[LINK ID]
WHERE
      Payments.DATE IS NULL
;

Open in new window

0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you all for such comprehensive answers. Pity I can't allocate full points to a each of you
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now