January 30, 2009 at 3:07 pm
SELECT PART.ID AS PART_ID, UDFDESCRIPTION.STRING_VAL As [DESCRIPTION]
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDFDESCRIPTION ON PART.ID = UDFDESCRIPTION.DOCUMENT_ID
WHERE UDFDESCRIPTION.ID = 'UDF-0000035'
I am trying to combine the select statements below into one statement, but I am not having much luck. Any help would be appreciated.
SELECT PART.ID AS PART_ID, UDFCURRENT.STRING_VAL AS [CURRENT]
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDFCURRENT ON PART.ID = UDFCURRENT.DOCUMENT_ID
WHERE UDFCURRENT.ID = 'UDF-0000035'
SELECT PART.ID AS PART_ID, UDFVOLTAGE.STRING_VAL As [VOLTAGE]
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDFVOLTAGE ON PART.ID = UDFVOLTAGE.DOCUMENT_ID
WHERE UDFVOLTAGE.ID = 'UDF-0000034'
SELECT PART.ID AS PART_ID, UDFMOD.STRING_VAL As [MOD]
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDFMOD ON PART.ID = UDFMOD.DOCUMENT_ID
WHERE UDFMOD.ID = 'UDF-0000030'
January 30, 2009 at 3:20 pm
Well first of all, when you OUTER JOIN tables, you should not put conditions for the Nullable table into the WHERE clause (unless that include a test for NULL), because this effectively turns them into INNER JOINs. Add these conditions to your ON clauses.
Secondly, the simple solution is to just UNION ALL them together:
SELECT PART.ID AS PART_ID, UDFCURRENT.STRING_VAL AS [Description]
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDFCURRENT ON PART.ID = UDFCURRENT.DOCUMENT_ID
And UDFCURRENT.ID = 'UDF-0000035'
UNION ALL
SELECT PART.ID AS PART_ID, UDFVOLTAGE.STRING_VAL
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDFVOLTAGE ON PART.ID = UDFVOLTAGE.DOCUMENT_ID
And UDFVOLTAGE.ID = 'UDF-0000034'
UNION ALL
SELECT PART.ID AS PART_ID, UDFMOD.STRING_VAL
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDFMOD ON PART.ID = UDFMOD.DOCUMENT_ID
And UDFMOD.ID = 'UDF-0000030'
Though this is not the most performant way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 30, 2009 at 3:24 pm
Got it! Thanks for the help. It is appreciated.
January 30, 2009 at 3:25 pm
This should do the same thing and probably will perform better:
SELECT PART.ID AS PART_ID, UDFCURRENT.STRING_VAL AS [Description]
FROM PART
LEFT OUTER JOIN USER_DEF_FIELDS UDF ON PART.ID = UDF.DOCUMENT_ID
And UDF.ID In('UDF-0000030','UDF-0000034','UDF-0000035')
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 30, 2009 at 3:27 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply