QUERY with LEFT JOIN for multiple tables

Hello SQL Experts,

I have a set of table as shown in this JPG file.  Basically, it is our inventory of devices for which it shows our customer code / name, Device status, device connection, interface and interface type.

All devices must have customer code / name and status => I figure the normal query would work (SELECT...FROM...WHERE)
Not all devices will have Connection.  When there is a connection, there will always be Interface and Interface type. => Here, I figure there is a LEFT JOIN between DEVICE - CONNECTION

Now, I want to have a query that will be able to pull me the following information

DEVICE_NAME, CUST_CODE, CUST_NAME, STATUS, INT_NAME

Must have: It should be able to list all devices for me, even if there is no CONNECTION for those devices.
Option1:     If I need to pull just for a set of CUS_CODE only , where should I put the criteria in the query
Option2:     If I need to pull just a set of DEVICE_NAME only

Thank you in advance for you helpList of table names and its joints
Paul_ATLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WebberCommented:
SELECT 
	d.DEVICE_NAME,
	c.CUST_CODE,
	c.CUST_NAME,
	ps.STATUS,
	it.INT_NAME
FROM
	Device d
	LEFT JOIN
		Customer c
		ON c.CUS_PK = d.D_CUS_PK
	LEFT JOIN 
		Plan1 p
		ON p.PL1_PK = d.D_PL1_PK
	LEFT JOIN 
		PlanStatus ps
		ON ps.PS_PK = p.PL1_PS_PK
	LEFT JOIN 
		CONX cx
		ON cx.CONX_PK = d.D_PK
	LEFT JOIN
		`INT` i
		ON i.INT_PK = cx.CONX_INT_PK
	LEFT JOIN 
		`INT_TYPE` it
		ON it.TYPE_PK = i.INT_TYPE_PK

Open in new window

0
Brian CroweDatabase AdministratorCommented:
I am using nested INNER JOIN's of INT and INT_TYPE to CONX but you could also use LEFT JOIN's to INT and INT_TYPE.

DECLARE @CUST_CODE VARCHAR(50) = NULL, --guessing on datatype
   @DEVICE_NAME VARCHAR(50) = NULL --guessing on datatype

SELECT D.DEVICE_NAME, C.CUST_CODE, C.CUST_NAME
FROM Device AS D
INNER JOIN Customer AS C
   ON D.D_CUS_PK = C.CUS_PK
INNER JOIN Plan1 AS P
   ON D.D_PL1_PK = P.PL1_PK
INNER JOIN PlanStatus AS PS
   ON P.PL1_PS_PK = PS.PS_PK
LEFT OUTER JOIN CONX
   INNER JOIN INT
      ON CONX.CONX_INT_PK = INT.INT_PK
   INNER JOIN INT_TYPE
      ON INT.INT_TYPE_PK = INT_TYPE.TYP_PK
   ON D.D_PK = CONX.CONX_PK
WHERE (@DEVICE_NAME IS NULL OR D.DEVICE_NAME = @DEVICE_NAME)
   AND (@CUST_CODE IS NULL OR C.CUST_CODE = @CUST_CODE)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_TAD_Commented:
I think you will want to use an inline view.

Select * from Table1  
   inner join
      (Select col1, col2, coly, colz from TABLE2 inner join TABLE3 ON table2.col1=table3.coly) InlineView on inlineView. Col2 = Table1.col1


---This is the first time I'm writing a SQL statement without being able to test it, but here goes...

Select *
From DEVICE A
 Inner Join CUSTOMER B On A.D_Cus_PK = B.Cus_PK
 Inner Join PLAN1 C on A.D_PL1_PK = C.PL1_PK
   Inner Join PLANSTATUS CC on C.PL1_PS_PK=CC.PS_PK
 LEFT OUTER JOIN
     (Select x.CONX_PK, y.INT_TYPE_PK, z.INT_NAME from CONX x inner join INT y on x.conx_int_pk = y.int_pk inner join INT_TYPE z on y.INT_TYPE_PK = z.TYPE_PK) InLineView on A.D_PK = InLineView CONX_PK
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Paul_ATLAuthor Commented:
thank you experts,

What about putting the criteria to list just specific CUST_CODE or DEVICE_NAME.  Where would I put this

AND DEVICE_NAME IN ('name1', name2',...) AND CUST_CODE IN (code1, code2, ...)
0
Paul_ATLAuthor Commented:
Ooops. sorry, Brian you already indicated that in there. testing yours now
0
WebberCommented:
	WHERE
		d.DEVICE_NAME IN ('name1', 'name2') AND
		c.CUST_CODE IN ('code1', 'code2')

Open in new window

0
Paul_ATLAuthor Commented:
Hello Experts, thank you for your help sirs. Really appreicated.

I choose Brian's answer as the solution as it works the first time (I mean copy-paste - run -> works !!)
0
PortletPaulfreelancerCommented:
"Nested joins" - which Brian has used - are allowed and do work (as you have seen)
However they remain relatively "unusual" and they can get very confusing.

FROM Device AS D
    INNER JOIN Customer AS C ON D.D_CUS_PK = C.CUS_PK
    INNER JOIN Plan1 AS P ON D.D_PL1_PK = P.PL1_PK
    INNER JOIN PlanStatus AS PS ON P.PL1_PS_PK = PS.PS_PK
    LEFT OUTER JOIN CONX
               INNER JOIN INT N CONX.CONX_INT_PK = INT.INT_PK
               INNER JOIN INT_TYPE ON INT.INT_TYPE_PK = INT_TYPE.TYP_PK
       ON D.D_PK = CONX.CONX_PK
 
A more commonly used approach is to continue the LEFT OUTER JOIN as a "chain", like this

FROM Device AS D
    INNER JOIN Customer AS C ON D.D_CUS_PK = C.CUS_PK
    INNER JOIN Plan1 AS P ON D.D_PL1_PK = P.PL1_PK
    INNER JOIN PlanStatus AS PS ON P.PL1_PS_PK = PS.PS_PK
    LEFT OUTER JOIN CONX ON D.D_PK = CONX.CONX_PK
    LEFT OUTER JOIN INT N CONX.CONX_INT_PK = INT.INT_PK
    LEFT OUTER JOIN INT_TYPE ON INT.INT_TYPE_PK = INT_TYPE.TYP_PK

Brian referred to this second approach in his answer
but you could also use LEFT JOIN's to INT and INT_TYPE.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.