SQL: Join question

[LesStockton]

I don't do SQL on a regular basis and don't really know much about JOINs. Can someone help me form a proper SQL statement to achieve what I need.

I have to do a search on any or all of:
Lastname, firstname, active , contacttype, organization

I know there will be multiple sql statements, but I figured if I could get one, I could figure out the rest.

Table A.Firstname, Lastname, ContactID, ActiveIndicator
Table B.ID, ContactID, ContactTypeID
Table C.ContactTypeID, Description
Table D.ContactID, OrgID
Table E.OrgID, OrgName

So in my first search, what if I have the ContactID from Table A. Now what?
In this case, I know the contact is Active because I have that in Table A. However, I don't have ContactType, or the Organization name. I guess it's possible that there could be more than one.

 

[mrdenny]

You can do this in one query if you are slick about it. I'm working under the assumption that you want to do this via a stored procedure.

create procedure usp_Search
    @LastName varchar(50) = null,
    @FirstName varchar(50) = null,
    @ActiveIndicator bit = null,
    @ContactType varchar(50) = null,
    @OrgName varchar(50) = null
as
select {WhatEverColumnsYouWant}
from
TableA
join TableB on TableA.ContactID = TableB.ContactID
join TableC on TableB.ContactTypeID = TableC.ContactTypeID
join TableD on TableA.ContactID = TableD.ContactID
join TableE on TableD.OrgID = TableE.OrgID
where (@LastName is not null and TableA.LastName = @LastName)
or (@FirstName is not null and TableA.FirstName = @FirstName)
or (@ActiveIndicator is not null and TableA.ActiveIndicator = @ActiveIndicator)
or (@ContactType is not null and TableC.Description = @ContactType)
or (@OrgName is not null and TableE.OrgName = @OrgName)

Any of those can be changed from equal to like by adding %'s to the value before (or after) passing it to the stored procedure.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LesStockton]

thanx so much. Yes, I plan on a stored procedure. I'll try this out, but it looks good. Thanks so much. I'm glad to find experts that watch this forum.

 

[mrdenny]

no problem. The code may need some tweaking to fit your needs (as all code does).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[LesStockton]

Curious what the SQL would be if I had E.OrganizationID and wanted to get the C.ContactDescriptions and C.ContactTypeIDs, as well as Active A.Firstname and lastname. A. has ContactID and an activeindicator.

 

[LesStockton]

Okay. I've gotten a little further and am now trying to build the query based upon what's being passed in.
It doesn't like it when I'm trying to compare on the boolean ActiveInd near the end. Originally, I was passing in @ActiveIndicator as a boolean, defaulted to null, then then couldn't seem to compare on that. If there's a way to check to first see if @ActiveIndicator is null, I'll go back to making it a boolean, but it looked like it wouldn't allow that.
Next, I'm trying to check to see if the value is 1 or 0, but it doesn't like this. It says:
"AND (T_Contact.ActiveInd = ' to data type int."



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter procedure [dbo].[ud_Tester]
@LastName varchar(50) = null,
@FirstName varchar(50) = null,
@ActiveIndicator int = -1,
@ContactType int = 0,
@OrgName varchar(50) = null,
@CompanyID int = 0
as
BEGIN
DECLARE @SQLstmt varchar(8000)

SET @SQLstmt='
select LastName, FirstName, T_Contact.ActiveInd, ContactTypeDescription,
T_ContactType.ContactTypeID, T_Organization.OrganizationID, OrganizationName
from
T_Contact
join T_ContactTypeContact on T_Contact.ContactID = T_ContactTypeContact.ContactID
join T_ContactType on T_ContactTypeContact.ContactTypeID = T_ContactType.ContactTypeID
join T_OrganizationContact on T_Contact.ContactID = T_OrganizationContact.ContactID
join T_Organization on T_OrganizationContact.OrganizationID = T_Organization.OrganizationID
where 1=1'

IF @LastName is not null
BEGIN
SET @SQLstmt = @SQLstmt+'
AND (T_Contact.LastName LIKE ''' + @LastName + '%'') '
END

IF @FirstName is not null
BEGIN
SET @SQLstmt = @SQLstmt+'
AND (T_Contact.FirstName LIKE ''' + @FirstName + '%'')'
END

If @ActiveIndicator > -1
BEGIN
If @ActiveIndicator = 1
BEGIN
SET @SQLstmt = @SQLstmt+'
AND (T_Contact.ActiveInd = ' + 1 + ')'
END
END

PRINT(@SQLstmt);
EXEC(@SQLstmt);
END
go

 

[mrdenny]

Change "SET @SQLstmt = @SQLstmt+'
AND (T_Contact.ActiveInd = ' + 1 + ')'" to "SET @SQLstmt = @SQLstmt+'
AND (T_Contact.ActiveInd = 1)'"

There is no need to come out of the dynamic SQL to get a static value. Hard code it within the dynamic SQL.

Keep in mind that Dynamic SQL can cause all sorts of security problems.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com