CODEWITHSUNDEEP
mysql
user2465936
user2465936

Reputation: 1040

MySQL LEFT JOIN with DISTINCT or LIMIT. Need to LEFT JOIN only one value

MySQL table 18_8_ChartOfAccounts

AccountNumber  | VatReturnRowNumberForDebitTurnover
-------------------------------------------
 1             | 61
 2             | 55

MySQL table 2_1_journal

DebitAccount  | TransactionPartnerName | Amount
-----------------------------------------------
 1            |      Name1             | 55
 2            |      Name2             | 60

MySQL table 18_6_TransactionPartners

CompanyName
-------------------------------
 Name1
 Name2
 Name1

Query

SELECT 
AccountNumber,
DebitAccount, TransactionPartnerName, Amount,
CompanyName
FROM 18_8_ChartOfAccounts
LEFT JOIN 2_1_journal
ON AccountNumber = DebitAccount
LEFT JOIN 18_6_TransactionPartners
ON TransactionPartnerName = CompanyName
WHERE VatReturnRowNumberForDebitTurnover = '61' AND Amount = 55

In output necessary to get something like this

TransactionPartnerName | Amount
-----------------------------------------
  Name1                | 55

However as in table 18_6_TransactionPartners there are two names Name1 get

TransactionPartnerName | Amount
-----------------------------------------------
     Name1             | 55
     Name1             | 55

Instead of SELECT tried SELECT DISTINCT (the same result).

Instead of ON TransactionPartnerName = CompanyName tried ON TransactionPartnerName = CompanyName LIMIT 1 (get syntax error).

May be need GROUP BY CompanyName somewhere.

Please, advice...

Upvotes: 1

Views: 5664

Answers (4)

KarelG
KarelG

Reputation: 5244

You can use the group by-statement in the subquery at LEFT JOIN for TransactionPartners table.

SELECT 
    AccountNumber, DebitAccount, TransactionPartnerName, Amount, CompanyName
FROM 18_8_ChartOfAccounts
    LEFT JOIN 2_1_journal ON AccountNumber = DebitAccount
    LEFT JOIN (SELECT CompanyName FROM 18_6_TransactionPartners GROUP BY CompanyName) ON TransactionPartnerName = CompanyName
WHERE VatReturnRowNumberForDebitTurnover = '61' AND Amount = 55

edit1 : corrected column name.

Upvotes: 1

Diwi
Diwi

Reputation: 11

Try to make relations between tables using unique keys.

Change TransactionPartnerName to TransactionPartnerId and add new column PartnerId to 18_6_TransactionPartners table and then join these tables using this new column. 

SELECT AccountNumber, DebitAccount, TransactionPartnerName, Amount, CompanyName 
FROM 18_8_ChartOfAccounts 
    LEFT JOIN 2_1_journal ON AccountNumber = DebitAccount 
    LEFT JOIN 18_6_TransactionPartners ON TransactionPartnerId = PartnerId 
WHERE VatReturnRowNumberForDebitTurnover = '61' AND Amount = 55

Upvotes: 0

Jan Spurny
Jan Spurny

Reputation: 5527

DISTINCT doesn't limit number of rows in result - it just makes sure the rows are all unique.

The LIMIT solution is the right one. Only it must be placed at the end of the query. So the correct query should be:

SELECT 
    AccountNumber,
    DebitAccount, TransactionPartnerName, Amount,
    CompanyName
FROM 18_8_ChartOfAccounts
LEFT JOIN 2_1_journal
    ON AccountNumber = DebitAccount
LEFT JOIN 18_6_TransactionPartners
    ON TransactionPartnerName = CompanyName
WHERE VatReturnRowNumberForDebitTurnover = '61' AND Amount = 55
LIMIT 1;

Upvotes: 2

vicentazo
vicentazo

Reputation: 1799

Try something like this:

SELECT 
    TransactionPartnerName, Amount
FROM 18_8_ChartOfAccounts
    LEFT JOIN 2_1_journal ON AccountNumber = DebitAccount
    LEFT JOIN 18_6_TransactionPartners ON TransactionPartnerName = CompanyName
WHERE VatReturnRowNumberForDebitTurnover = '61' AND Amount = 55
GROUP BY TransactionPartnerName;

Upvotes: 1

Related Questions