CODEWITHSUNDEEP
sqlsql-server
Shaggy
Shaggy

Reputation: 5820

Match two Columns in SQL Join

I have following Table Structure :

Table: CashStatus (Can have duplicate entries for "ATM")

ATM  Cash   Device
A01  55     BASE
A01  1      IST
A02  1      BASE
A02  78     IST

Table : ATM (Unique Entry for ATM Column)

ATM   DEVICE
A01   BASE
A02   IST

I want to retreive only those rows from CashStatus table who's ATM & Device Column matches with as with ATM Table

I did some research and tried the following query:-

SELECT *
  FROM CashStatus 
 WHERE EXISTS (SELECT 'X'
                 FROM ATM
                WHERE ATM = TermId collate SQL_Latin1_General_CP1_CI_AS
               AND DeviceName = Devicetype collate SQL_Latin1_General_CP1_CI_AS);

But it gives error : Expression type int is invalid for COLLATE clause.

Upvotes: 1

Views: 11935

Answers (4)

HABO
HABO

Reputation: 15852

There have been several poists of essentially the same answer, each receiving a cryptic comment that the result is not as desired. Perhaps you could offer an example of the correct output. Following is a complete example to start with:

declare @CashStatus as Table ( ATM VarChar(3), Cash Int, Device VarChar(4) )
insert into @CashStatus ( ATM, Cash, Device ) values
  ( 'A01', 55, 'BASE' ),
  ( 'A01',  1, 'IST' ),
  ( 'A02',  1, 'BASE' ),
  ( 'A02', 78, 'IST' )

declare @ATM as Table ( ATM VarChar(3), Device VarChar(4) )
insert into @ATM ( ATM, Device ) values
  ( 'A01', 'BASE' ),
  ( 'A02', 'IST' )

select CS.ATM, CS.Cash, CS.Device
  from @CashStatus as CS inner join
    @ATM as A on A.ATM = CS.ATM and A.Device = CS.Device

Upvotes: 1

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

SELECT columnLists
FROM CashStatus cs JOIN ATM a ON cs.ATM = a.ATM AND cs.Device = a.Device

COLLATE is a keyword used to change language collations for columns; it's never necessary on numeric datatypes, and probably unnecessary here (since you're changing from one language to the same language).

Upvotes: 4

Syntax_Error
Syntax_Error

Reputation: 6230

SELECT *
FROM CashStatus
LEFT JOIN ATM ON CASHSTATUS.ATM=ATM.ATM and CASHSTATUS.DEVIDE=ATM.DEVICE

Upvotes: 1

Carlos Martinez T
Carlos Martinez T

Reputation: 6528

You should be able to join both tables using 2 columns:

SELECT a.*
FROM CashStatus a
INNER JOIN ATM b ON a.ATM = b.ATM AND a.DEVICE = b.DEVICE

Would this give you the result you wanted?

Upvotes: 2

Related Questions