Conditional Outer Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional Outer Join

Author

Topic

rosshughes
Starting Member

16 Posts

Posted - 2011-09-13 : 10:47:39

Hi,

I am trying to create query that lets me return null values (outer join) when run without parameters, and eliminates null values (inner join) when run with a parameter. Can somebody point me in the right direction? It is the CUSTOMER table that I am working with and the @CustID value.

Code Below:
------------------------
IF @CustID Is Null
BEGIN
SET @CustID = '%'
END

-- declare the initial end part dataset
DECLARE @CustomerParts TABLE
(
counter int identity(1,1) PRIMARY KEY,
company varchar(12),
partnum varchar(20),
partclass varchar(50),
customerpart varchar(50),
customerpartdesc varchar(100),
customerpartrev varchar(20),
custnum int,
custid varchar(12),
customername varchar(100)
)
Insert INTO @CustomerParts
SELECT part.company, part.partnum, partclass.description, xpartnum, part.partdescription, xrevisionnum, custxprt.custnum, customer.custid, customer.name
FROM part
INNER JOIN partclass ON part.company = partclass.company AND part.classid = partclass.classid AND partclass.description LIKE 'FG%'
LEFT OUTER JOIN custxprt ON part.company = custxprt.company AND part.partnum = custxprt.partnum
LEFT OUTER JOIN customer ON custxprt.company = customer.company and custxprt.custnum = customer.custnum AND customer.custid LIKE @CustID
WHERE part.company = @CompanyID

SELECT * FROM @CustomerParts
-------------------------

Thanks in advance!
Ross

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 11:11:06

make


LEFT OUTER JOIN customer ON custxprt.company = customer.company and custxprt.custnum = customer.custnum AND customer.custid LIKE @CustID


...
LEFT OUTER JOIN customer ON custxprt.company = customer.company and custxprt.custnum = customer.custnum 
WHERE part.company = @CompanyID
AND (customer.custid LIKE @CustID OR @CustID ='%')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rosshughes
Starting Member

16 Posts

Posted - 2011-09-13 : 11:18:20

Wow, amazing. Thanks for the quick response. It works perfectly.

Ross

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 11:19:43

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources