We help IT Professionals succeed at work.

At a casual glance is there a better way to improve this query?

  
  	SELECT 
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=101) as SDWT,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=100) as SummerDraft,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=70) as LOA,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=74) as Beam,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=50) as Built
	FROM 
		(SELECT TOP 1 VesselID 
		    FROM Q88.vwVesselIMO 
		    WHERE (LEN('9351452')=7 AND IMO='9351452') OR 
		          (LEN('9351452')<>7 AND VesselName='Hellespont Protector')) a

Open in new window

Comment
Watch Question

looks pretty clean
is there a performance issue ?
Top Expert 2015

Commented:
You can use this to get the same result...

SELECT TOP 1 tb1.VesselID,
Case When tb2.QuestionID=101 then tb2.answer end as SDWT,
Case When tb2.QuestionID=100 then tb2.answer end as SummerDraft,
Case When tb2.QuestionID=70 then tb2.answer end as LOA,
Case When tb2.QuestionID=74 then tb2.answer end as Beam,
Case When tb2.QuestionID=50 then tb2.answer end as Built
FROM Q88.vwVesselIMO tb1
inner join Q88.QuestionAnswer tb2 on tb1.VesselID=tb2.VesselID
WHERE (LEN('9351452')=7 AND IMO='9351452') OR (LEN('9351452')<>7 AND VesselName='Hellespont Protector')

Open in new window


**Edited Comment**

Saurabh...
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
First, I wouldn't use TOP 1 but MAX, since you don't have an ORDER BY it can return any row and with MAX it will always return the higher ID and also should be a boost for the performance if the table has millions of records:
  
  	SELECT 
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=101) as SDWT,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=100) as SummerDraft,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=70) as LOA,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=74) as Beam,
		(SELECT Answer FROM Q88.QuestionAnswer WHERE VesselID=a.VesselID AND QuestionID=50) as Built
	FROM 
		(SELECT MAX(VesselID) VesselID
		    FROM Q88.vwVesselIMO 
		    WHERE (LEN('9351452')=7 AND IMO='9351452') OR 
		          (LEN('9351452')<>7 AND VesselName='Hellespont Protector')) a

Open in new window


Those subselects looks very ugly but I think who did it like that it was because they need to have a single row to be returned, right?
Paul MaurielloSoftware Programmer Developer Analyst Engineer

Author

Commented:
They are ugly, Ultimately I want a single row returned. This query crosses databases so I want it to be as efficient as possible. I just wanted to see if there was any SQL trickery to get around it.
IT Engineer
Distinguished Expert 2017
Commented:
A Pivot Table will resolve that. Without any sample data it's hard to test this but you can do it by yourself:
SELECT 
	b.[101] as SDWT,
	b.[100] as SummerDraft,
	b.[70] as LOA,
	b.[74] as Beam,
	b.[50] as Built
FROM 
	(SELECT MAX(VesselID) VesselID
	    FROM Q88.vwVesselIMO 
	    WHERE (LEN('9351452')=7 AND IMO='9351452') OR 
	          (LEN('9351452')<>7 AND VesselName='Hellespont Protector')) a
INNER JOIN 
	(SELECT *
	FROM (SELECT VesselID, Answer, QuestionID
		FROM Q88.QuestionAnswer 
		WHERE QuestionID IN (50, 70, 74, 100, 101)) T
	PIVOT 
		(MAX(Answer) FOR QuestionID IN ([50], [70], [74], [100], [101]))
		AS PivotTable) b
	ON b.VesselID=a.VesselID

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
SELECT
    MAX(CASE WHEN q.QuestionID=101 THEN q.Answer ELSE '' END) AS SDWT,
    MAX(CASE WHEN q.QuestionID=100 THEN q.Answer ELSE '' END) AS SummerDraft,
    MAX(CASE WHEN q.QuestionID=70  THEN q.Answer ELSE '' END) AS LOA,
    MAX(CASE WHEN q.QuestionID=74  THEN q.Answer ELSE '' END) AS Beam,
    MAX(CASE WHEN q.QuestionID=50  THEN q.Answer ELSE '' END) AS Built
FROM
      (SELECT TOP 1 VesselID
          FROM Q88.vwVesselIMO
          WHERE (LEN('9351452')=7 AND IMO='9351452') OR
                        (LEN('9351452')<>7 AND VesselName='Hellespont Protector')
      ) AS v
CROSS APPLY (
    SELECT q2.Answer
    FROM Q88.QuestionAnswer q2
    WHERE
        q2.VesselID = v.VesselID AND
        q2.QuestionID IN (50, 70, 74, 100, 101)
) AS q
Paul MaurielloSoftware Programmer Developer Analyst Engineer

Author

Commented:
Thank you guys for your input I appreciate it