RC Maples RC Maples - 5 months ago 6
SQL Question

Select data in single table with a 1:Many relationship

I have a table with 67 columns and around 36,000 rows, I need to run a SELECT based on two of those columns.

The important columns are FINANCIAL_ACCOUNT_ID, EVENT_DESCRIPTION.

The FINANCIAL_ACCOUNT_ID is a unique number per account, but may not be unique in the table. Event Description is not unique. Event Description can contain 'Event 1' 'Event 2' 'Event 3' and so on.

So the table could look like this:

TABLE
+----------------------+-------------------+-------------+
| FINANCIAL_ACCOUNT_ID | EVENT_DESCRIPTION | OTHER_STUFF |
+----------------------+-------------------+-------------+
| 1121 | Event 1 | n/a |
| 107401 | Event 2 | n/a |
| 256483 | Event 1 | n/a |
| 1121 | Event 3 | n/a |
| 107401 | Event 1 | n/a |
| 363586 | Event 1 | n/a |
| 546875 | Event 1 | n/a |
| 546875 | Event 2 | n/a |
+----------------------+-------------------+-------------+


RESULT
+----------------------+-------------------+-------------+
| FINANCIAL_ACCOUNT_ID | EVENT_DESCRIPTION | OTHER_STUFF |
+----------------------+-------------------+-------------+
| 107401 | Event 2 | n/a |
| 107401 | Event 1 | n/a |
| 546875 | Event 1 | n/a |
| 546875 | Event 2 | n/a |
+----------------------+-------------------+-------------+


What I need to get returned is the entire row IF there is a financial account ID with BOTH 'Event 1' and 'Event 2'.

I've tried WITH clauses combined with inner joins... In my latest attempt, I've added a count(financial_account_id) such as:

select FINANCIAL_ACCOUNT_ID, EVENT_DESCRIPTION, COUNT(FINANCIAL_ACCOUNT_ID)
FROM master
group by FINANCIAL_ACCOUNT_ID
HAVING COUNT(FINANCIAL_ACCOUNT_ID) >1;


However, here are the results I'm seeing with that select statement:

Financial_Account_ID | Event_Description | COUNT(Financial_Account_ID)
107401 | Event 2 | 2
546875 | Event 1 | 2


(I say I need the entire row, but once I get the proper results returned, I can filter out the extraneous columns I don't need.)

Any help with what a SQL Query like this should look like?
Details: mariadb 10, databasename: JuneEvents, table name: master
I've also got a copy in a MS-SQL 2012 Express db

EDIT: @Tim - Sorry for that, I need to match each account to both 'event 1' and 'event 2' tossing event 3 and any others that may exist. Does that clarify?

Answer

To get the accounts with both events, use:

SELECT financial_account_id
FROM master t
WHERE event_description IN ('Event 1', 'Event 2')
GROUP BY financial_account_id
HAVING COUNT(DISTINCT event_description) = 2;

Then to get the original rows, use JOIN:

SELECT t.*
FROM master t JOIN
     (SELECT financial_account_id
      FROM master t
      WHERE event_description IN ('Event 1', 'Event 2')
      GROUP BY financial_account_id
      HAVING COUNT(DISTINCT event_description) = 2
     ) tt
     ON tt.financial_account_id = t.financial_account_id;
Comments