help with a query
-
Hi there,
I'm stuck on how to get some information from a query on a multiple join where the one of the joins is on a piece of data that doesn't exist in the primary table.
Portfolio colums
name varchar
active tinyint
inconversion tinyint
DECLARE @tblAccountType TABLE(
AccountCode varchar(255),
TraderName varchar(255))
DECLARE
@tblAccountCode TABLE
( AccountCode VARCHAR(255)
)
IF
@vchAccount_Code IS NULL OR @vchAccount_Code = ''
BEGIN
INSERT @tblAccountCode
SELECT AccountCode
FROM @tblAllAccount
END
INSERT @tblAccountType
SELECT a.AccountCode ,a.TraderName
FROM @tblAllAccount a
INNER JOIN Portfolio p
ON a.Active = p.Active
INNER JOIN @tblAccountCode c
ON a.AccountCode = c.AccountCode
WHERE p.InConversion = 1
AND a.AccountCode LIKE '%Conversion%'
@tblAccountType is populated with all of the AccountCode's from @tblAllAccount
the problem is that I have a record that exists in Portfolio but that DOESN'T exist in either of the other 2 tables.
Is there any way that I can even know that the record exists? I'm thinking that I probably have to do two distinct statements one for records that DO exist and another reworked ones for oddballs.
Thoughts?
Thanks,
Chris
-
Look at LEFT JOIN and RIGHT JOIN syntax in BOL. If the key value for connecting the database is null in the joined table (not the primary table) then no match was found.
-
I actually had switched the inners to left/right perspectively but ran it in the context of the whole script where there was another statement further down taking the results of the above insert and comparing it to yet another table, where the data did not exist so I didn't think it did anything.
All in all, it's an orphaned record that probably needs to be cleared up but the joins STILL needed to be changed anyways, so thank you for the nudge in the proper direction
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply