Search code examples
mysqlqsqlquery

Select the same pairs of values from two tables

I have 4 MySQL tables in my database:

  1. Users (uid, name)
  2. Shops (shopId, shopName)
  3. Reviews (ReviewId, text, userId, shopId, rate, date)
  4. TransactionHistory (transactionId, userId, shopId, Amount, date)

Users write their comments to shops in Reviews table. And user payment history in some shops is saved in the TransactionHistory table.

I need to select all users' reviews for some period in time if that users made payments in that shop at the same period in time.

  1. Select userId, shopId, rate, text, date from Review where date between "01.01.2019" and "01.02.2019" where shopId in (select distinct(shopId) from Shops)

  2. Select userId, shopId, date from TransactionHistory where date between "01.01.2019" and "01.02.2019"

So I have two result sets and some records have the same pair (userId, shopId) - that is what I want to get: all records from 1 SQL request, which pairs (userId, shopId) is present in 2 SQL query.

Solution

  • If I understood that right, all you need is a join statement like this one:

       SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

    Here is the resource https://dev.mysql.com/doc/refman/8.0/en/join.html

    On your case inside the on statement would be what you want to be equal.

    Select userId, shopId, rate, text, date from Review r join TransactionHistory th on (r.userId == th.userId and r.shopId == th.shopId) where r.date between "01.01.2019" and "01.02.2019" where r.shopId in (select distinct(shopId) from Shops)