AnsweredAssumed Answered

SQL Question from a SQL Noob...

Question asked by Brian Dalton on Feb 8, 2013
Latest reply on Feb 12, 2013 by Shawn Kelly

I'm getting my feet wet on using SQL to query the PDM database, and I need to get a list of files and several of the variables attached to them.  My code works fine on those files where each of the required variables actually exists, but there are a number of older files which were created before some of these variables were implemented, so these older files have no value at all for particular variables.

 

I'd like them to show up but with that space just left empty, instead the records with missing variables just don't get included in the query results.  I'm a ham-fisted noob on this stuff, so please don't laugh (well, OK. Go ahead) at my clumsy code.  Could someone explain to me how to fix it so I can still get records where some of the fields have no values?

 

Here goes...

 

SELECT XD.Filename AS 'Component', VVRev.ValueText AS 'Rev', VVDesc.ValueText AS 'Description',

   X.RefCount AS 'Qty', VVVend.ValueText AS 'Vendor', VVVNum.ValueText AS 'Vendor P/N'

FROM XRefs X, Documents D, Documents XD, VariableValue VVRev, VariableValue VVDesc,

   VariableValue VVVend, VariableValue VVVNum

WHERE D.DocumentID = 76503 AND

      X.DocumentID = D.DocumentID AND

      X.RevNr = D.LatestRevisionNo AND

      XD.DocumentID = X.XRefDocument AND

      VVRev.DocumentID = XD.DocumentID AND

      VVRev.VariableID = 46 AND --Revision

      VVRev.RevisionNo = (SELECT MAX(VVR1.RevisionNo)

                          FROM VariableValue VVR1

                          WHERE VVR1.DocumentID = XD.DocumentID AND

                                VVR1.VariableID = VVRev.VariableID) AND                         

      VVRev.ConfigurationID = (SELECT MAX(VVR2.ConfigurationID)

                               FROM VariableValue VVR2

                               WHERE VVR2.DocumentID = XD.DocumentID AND

                                     VVR2.VariableID = VVRev.VariableID AND

                                     VVR2.RevisionNo = VVRev.RevisionNo) AND

      VVDesc.DocumentID = XD.DocumentID AND

      VVDesc.VariableID = 45 AND --Description

      VVDesc.RevisionNo = (SELECT MAX(VVD1.RevisionNo)

                           FROM VariableValue VVD1

                           WHERE VVD1.DocumentID = XD.DocumentID AND

                                 VVD1.VariableID = VVDesc.VariableID) AND

      VVDesc.ConfigurationID = (SELECT MAX(VVD2.ConfigurationID)

                               FROM VariableValue VVD2

                               WHERE VVD2.DocumentID = XD.DocumentID AND

                                     VVD2.VariableID = VVDesc.VariableID AND

                                     VVD2.RevisionNo = VVDesc.RevisionNo) AND

      VVVend.DocumentID = XD.DocumentID AND

      VVVend.VariableID = 49 AND --Vendor

      VVVend.RevisionNo = (SELECT MAX(VVV1.RevisionNo)

                           FROM VariableValue VVV1

                           WHERE VVV1.DocumentID = XD.DocumentID AND

                                 VVV1.VariableID = VVVend.VariableID) AND

      VVVend.ConfigurationID = (SELECT MAX(VVV2.ConfigurationID)

                               FROM VariableValue VVV2

                               WHERE VVV2.DocumentID = XD.DocumentID AND

                                     VVV2.VariableID = VVVend.VariableID AND

                                     VVV2.RevisionNo = VVVend.RevisionNo) AND                                    

      VVVNum.DocumentID = XD.DocumentID AND

      VVVNum.VariableID = 56 AND --Vendor P/N

      VVVNum.RevisionNo = (SELECT MAX(VVVN1.RevisionNo)

                           FROM VariableValue VVVN1

                           WHERE VVVN1.DocumentID = XD.DocumentID AND

                                 VVVN1.VariableID = VVVNum.VariableID) AND

      VVVNum.ConfigurationID = (SELECT MAX(VVVN2.ConfigurationID)

                               FROM VariableValue VVVN2

                               WHERE VVVN2.DocumentID = XD.DocumentID AND

                                     VVVN2.VariableID = VVVNum.VariableID AND

                                     VVVN2.RevisionNo = VVVNum.RevisionNo)

 

See, I told you I was a noob 

Outcomes