CODEWITHSUNDEEP
mysql
Adam Baranyai
Adam Baranyai

Reputation: 3867

Querying from multiple tables when the second table has multiple conditions that should match

I am trying to write a MySQL query, which fetches data from two different connected tables. I already stumbled upon a simmilar situation some times ago, and some Stackoverflower helped me out with it, but now, trying to start from his solution, I can't figure out how should I modify the query for my current problem.

Consider that I have these two tables:

Transactions

-------------------------------------
| TransactionId | Currency | Amount |
-------------------------------------
| 1             | EUR      | 15.00  |
| 2             | EUR      | 39.50  |
| 3             | USD      | 10.00  |
-------------------------------------

and

Transaction_Paths

---------------------------------------------------------------------------------
| TPathId | TPathType | TPathDirection | TPathLink | transactions_TransactionId |
---------------------------------------------------------------------------------
| 1       | 'bank'    | 'to'           | 1         | 1                          |
| 2       | 'bank'    | 'from'         | 2         | 1                          |
| 3       | 'account' | 'from'         | 1         | 1                          |
| 4       | 'account' | 'to'           | 2         | 1                          |
| 5       | 'bank'    | 'to'           | 3         | 2                          |
| 6       | 'bank'    | 'from'         | 2         | 2                          |
| 7       | 'bank'    | 'to'           | 4         | 3                          |
| 8       | 'account' | 'from'         | 1         | 3                          |
---------------------------------------------------------------------------------

Basically, these two tables are storing transactions happening on my site, with information about those transactions in the Transaction_Paths table. Each line in the Transactions table may have multiple attached lines in the Transaction_Paths table, via the Transaction_paths.transactions_TransactionId field.

How could I write a query, which queries out the exact transaction which is coming 'from' the account with the id of 1, and going to the bank with the id of 4?

Based on the previous question that I asked, and the answer to it, I should be using HAVING, but how can I use having, when I have multiple conditions that should match on the second table?

EDIT: The expected result should be to return only the third line from the Transactions table, as in the line, with the TransactionId of 3.

Upvotes: 1

Views: 29

Answers (2)

Adam Baranyai
Adam Baranyai

Reputation: 3867

In the meantime, I totally forgot about M H Rasel's answer, and found another way to do it. For the effort put in by the answerer, I will mark his answer as the accepted one, and in the meanwhile, someone can comment on my answer if this will indeed work in every situation? My tests, altough limited, provided the correc results:

SELECT Transactions.* 
FROM Transactions 
INNER JOIN Transaction_Paths 
   ON Transaction_Paths.transactions_TransactionId = Transactions.TransactionId 
   WHERE Transaction.Currency = 'USD' AND
         ((TransactionPaths.TPathDirection = 'from' AND TransactionPaths.TPathLink = '1')     
          OR (TransactionPaths.TPathDiretion= 'to' AND TransactionPaths.TPathLink = '4')) 
GROUP BY Transactions.TransactionId 
HAVING COUNT(distinct Transaction_Paths.TPathId) = 2

Upvotes: 0

Md Monjur Ul Hasan
Md Monjur Ul Hasan

Reputation: 1791

To achieve the desire output you need to make a three cascading left join Transaction_Paths Left join Transaction_Paths left join Transactions. The first left join (the self join Transaction_Paths Left join Transaction_Paths ) will be with no condition , but the second left join will be on (Transaction_Paths.transactions_TransactionId = Transaction_Paths.TransactionId).

I have made a pseudo query for this to give you an idea as following:

SELECT c.*
FROM Transaction_Paths a
LEFT JOIN Transaction_Paths b 
LEFT JOIN Transactions c ON (a.transactions_TransactionId = c.TransactionId)
WHERE a.TPathDirection  = "from" AND a.TPathLink = 1 AND b.TPathDirection  = "to" AND b.TPathLink = 4

Hope you can come up with the appropriate query using your favorite query console. Or share your schema with a fiddle (e.g., SQL fiddle) and I will try to build the exact query and edit this post.

Upvotes: 2

Related Questions