Joining a table to validate a condition
Greetings,
I am working on a project where I need to build a text file that will be consumed by a mainframe process, but I only want the rows from the *Quote_AUXL_A* table if it meets the criteria in the *Quote_HOAppToMainframe* Table(please see query below)
I believe my problem to be in my join operation, but have tried many variations of the join operator, but continue to get an incorrect result set. I know this because I can take the second half of the query and run it and it brings back 4 rows, if I run the entire query, it brings back 23 rows. I am sure the solution is staring me right in the face, but my head hurts right now so I was hoping someone could apply a little of their own cerebral elbow grease to show me where I am missing this.
> *This is a SQL Server 2000 instance (I am working on upgrading the environment)*
`select Distinct AA.[PolicyType],AA.[AOR],AA.[AgencyNum],AA.[QuoteDate],AA.[QuoteTime],AA.[PolicyNum],AA.[CountyCode]
from [AgentAccess].[dbo].[Quote_AUXL_A] AA
join [AgentAccess].[dbo].[Quote_HOAppToMainframe] MF
on AA.[PolicyType] = MF.[PolicyType]
where MF.ReadyForMF = 'Y' and MF.SentToMF is Null `
I appreciate your support
Best Answer
The two possibilities I can think of are:
1) There are 23 rows in the left-hand table that have PolicyTypes that are in the right. That would result in 23 rows even if there are only 4 rows in the right-hand table
2) There are duplicated PolicyType values in both tables. Then when the two tables are join each row in the left table would join once for each matching PolicyType in the right table. To show what I mean here's a quick example.
The query below creates and populates 2 tables - the left hand table has 5 rows and the right has 4 rows. The join though results in 8 rows as the 3 "A"s in the left hand table join to both "A"s in the right table (giving 6 rows) and then the "B" and "C" join once (giving the remaining 2 rows).
CREATE TABLE #demoA
(
PolicyType VARCHAR(1)
)
INSERT INTO #demoA
VALUES ('A')
INSERT INTO #demoA
VALUES ('A')
INSERT INTO #demoA
VALUES ('A')
INSERT INTO #demoA
VALUES ('B')
INSERT INTO #demoA
VALUES ('C')
CREATE TABLE #demoB
(
PolicyType VARCHAR(1)
)
INSERT INTO #demoB
VALUES ('A')
INSERT INTO #demoB
VALUES ('A')
INSERT INTO #demoB
VALUES ('B')
INSERT INTO #demoB
VALUES ('C')
SELECT *
FROM [#demoA] AS da
INNER JOIN [#demoB] AS db
ON [da].[PolicyType] = [db].[PolicyType]
So, in essence, it is the column I am joining on and not the join itself.
