sql query help (joins?)

[dd3123]

This has me a bit stumped, as I can't quite get it. I managed to come close with a subquery, but I'm going to be going through tens of thousands (and soon hundreds of thousands) of records, and the subquery just wont work as its godawfully ineffecient.

I've got 2 tables, one called "users" and the other "transactions"

In users, my index is user.id (clever huh?) which of course, matches with transactions.user_id. In the table transactions, I've also got "date" and "tran_type". Date is the record date, and tran_type is a number 1-9 that flags what kind of transaction it is.

Now then, what I want to do is pull all the records from the users and the first date (and ONLY the first date as there are multiple records for each user) of their first transaction that matches either tran_type 1 or 2. If a user doesn't have any matching tran types, I'd like something like a null to be returned for the date row.

So my final result might look like

1 Bob ... 8/2/2004
2 Jen ... 4/2/2003
3 JJ ... null
4 Mia ... 5/6/2004

(note that if Bob has multiple matches records, I'm only wanting his first, not all of them)

Thanks!

[lilleman]

Hi,

Try something like this.

SELECT u.id, u.name, IF(t.user_id IS NULL, NULL, MIN(t.date)) AS date
FROM users_table as u
  LEFT JOIN transactions_table as t
    ON t.user_id = u.id AND t.tran_type IN(1,2)
GROUP BY u.id

Yours, Erik.

[dd3123]

nm I got it 🙂 Thanks!