What is diffrent about using a RIGHT JOIN, A LEFT JOIN, or an INNER JOIN.
I know there is a result set diffreance, but I'm not sure what it is... my dag ate my SQL Ref.....
I always use an Inner Join and Use very strict where clauses.....
This is a boolean driven query I have. At first I thought I would have to use dynamic sql but after thought I came up with this.
Code:
ALTER PROCEDURE dbo.Contacts_GetUsers
(
@Employees bit,
@DReps bit,
@Mreps bit,
@Gen bit
)
AS
CREATE TABLE #TempTable(
ContactRowID int IDENTITY(1,1),
ContactKey char(36)
)
IF @Employees = 1
BEGIN
INSERT INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN Employees ON Employees.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
IF @DReps = 1
BEGIN
INSERT INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN DistributorsRepresentatives ON DistributorsRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
IF @MReps = 1
BEGIN
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN ManufacturersRepresentatives ON ManufacturersRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
IF @Gen = 1
BEGIN
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
WHERE Contacts.Type = 'Contact'
AND Contacts.EmailAddress != '[email protected]'
END
SELECT DISTINCT #TempTable.ContactKey AS contact_key, Contacts.LastName, Contacts.FirstName, Contacts.MiddleName, Contacts.EmailAddress FROM #TempTable
INNER JOIN Contacts ON Contacts.Guid = #TempTable.ContactKey
WHERE Contacts.EmailAddress != '[email protected]'
ORDER BY Contacts.LastName ASC, Contacts.FirstName ASC, Contacts.MiddleName ASC, Contacts.EmailAddress ASC
RETURN
this allows combo boxes to control what users are displayed. What is up with the editor it really messes with SQL.....
