>CREATE TABLE Contacts > (RecordId INT IDENTITY NOT NULL, -- PK of the row > ContactName VARCHAR(150) NULL, > Title VARCHAR(30) NULL, > CompanyName VARCHAR(50) NULL, > BirthDate DATETIME NULL, > IsGoogle TINYINT NULL, > IsConstant TINYINT NULL, > IsSkype TINYINT NULL, > PictureName VARCHAR(150) NULL, > GoogleId VARCHAR(250) NULL, > SkypeId VARCHAR(100) NULL, > Notes TEXT) > > >CREATE TABLE ContactCategories > (RecordId INT IDENTITY NOT NULL, -- PK of the row > ContactId INT NOT NULL, -- FK into Contacts.RecordId > CategoryId INT NOT NULL) -- FK into Caategories.RecordId > >CREATE TABLE Categories > (RecordId INT IDENTITY NOT NULL, -- PK of the row > Caption VARCHAR(50) NOT NULL) -- Caption of the Categorie > >>
>CREATE PROCEDURE lb_GetContactReportData2 > @ContactName VARCHAR(150) = NULL, > @Title VARCHAR(30) = NULL, > @CompanyName VARCHAR(50) = NULL, > @Category VARCHAR(30) = NULL > >AS > > SELECT c.ContactName, c.Title, c.CompanyName, c.BirthDate, > cat.Caption AS Category, > a.Street1, a.Street2, a.City, a.State, a.ZipCode, > e.EmailAddr, > p.PhoneNum, > w.WebSiteURL > FROM Contacts c > FULL JOIN ContactCategories cc ON cc.ContactId = c.RecordId > FULL JOIN Categories cat ON cat.RecordId = cc.CategoryId > FULL JOIN Addresses a ON a.ContactId = c.RecordId AND a.IsPrimary = 1 > FULL JOIN Phones p ON p.ContactId = c.RecordId AND p.IsPrimary = 1 > FULL JOIN Emails e ON e.ContactId = c.RecordId AND e.IsPrimary = 1 > FULL JOIN WebSites w ON w.ContactId = c.RecordId AND w.IsPrimary = 1 > WHERE c.ContactName LIKE '%' + @ContactName + '%' OR > c.Title LIKE '%' + @Title + '%' OR > c.CompanyName LIKE '%' + @CompanyName + '%' > ORDER BY ContactName, Title, CompanyName > >1. Why do you need FULL JOIN and not LEFT JOIN?