-1

I need to build a query with the following requirements.

The two tables to use are

MASTER_ARCHIVE and
REP_PROFILE

As of now we are only interested in reps at the wirehouses: Wells Fargo, Morgan Stanley, UBS, Merrill Lynch To get reps from only these firms, I need to filter the Rep Profile table by Firm ID (The Firm IDs can be found in Firm table), and can filter the Master Archive table on FIRM_CRD

What we need is 2 sets of data: 1) A list of wirehouse reps that are in the Master Archive table, but not in the Rep Profile table 2) A list of wirehouse reps that are in the Rep Profile Table, but not in the Master Archive table

Does anyone have an idea of what type of Joins and filter conditions that I would use to get the data that I'm looking for?

This is what I currently came up with!!!!

SELECT *
FROM MASTER_ARCHIVE E 
Left JOIN REP_PROFILE R 
ON E.REP_CRD = R.CRD_NUMBER
WHERE E.FIRM_ID IN ('F206','F443','F474','F458')

MINUS

SELECT *
FROM MASTER_ARCHIVE E 
JOIN REP_PROFILE R 
ON E.REP_CRD = R.CRD_NUMBER
WHERE E.FIRM_ID IN ('F206','F443','F474','F458')
--ORDER BY NAME Name
Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
ace123
  • 35
  • 1
  • 7
  • Kind of flying blind here without knowing the fields in your table, sample data, or how the two tables are related to one another. Have you tried anything yet, or did you just get the requirements and head to SO? – JNevill Aug 13 '15 at 21:22
  • @JNevill Thanks for the responce. I've edited the original post with my current query that I came up with. I think I'm on the right path however I still cant get the data that I'm looking for. – ace123 Aug 13 '15 at 23:56

1 Answers1

0

i don't understand so much, but try with this

SELECT *
FROM MASTER_ARCHIVE E 
LEFT JOIN REP_PROFILE R 
ON E.REP_CRD = R.CRD_NUMBER
WHERE E.FIRM_ID IN ('F206','F443','F474','F458')
AND R.CRD_NUMBER IS NULL
Mattia Caputo
  • 959
  • 1
  • 8
  • 17