TOP 1 from LEFT OUTER JOIN

Question

I have this litte SQL string for a MS SQL DB.

"SELECT" 
            " [Navision4].[dbo].[3S Company A_S$Item].[No_],[Navision4].[dbo].[3S Company A_S$Item].[Description] AS Description,[Navision4].[dbo].[3S Company A_S$Item].[Description 2], [Navision4].[dbo].[3S Company A_S$Item].[Item Status], [Navision4].[dbo].[3S Company A_S$Item].[New Sales Price DK], [Navision4].[dbo].[3S Company A_S$Item].[New List Price DK],[Navision4].[dbo].[3S Company A_S$Item].[New Sales Price No], [Navision4].[dbo].[3S Company A_S$Item].[New List Price No], [Navision4].[dbo].[3S Company A_S$Item].[New Sales Price S], [Navision4].[dbo].[3S Company A_S$Item].[New List Price S],[Navision4].[dbo].[3S Company A_S$Item Cross Reference].[Cross-Reference No_], [Navision4].[dbo].[3S disponibelt for export].[Disponibelt], [Navision4].[dbo].[3S Lagersaldo].[Quantity] AS Lagerantal, [Navision4].[dbo].[3S i_bestilling].[Expected Receipt Date] AS ToDispDate "
            "FROM [Navision4].[dbo].[3S Company A_S$Item] "


            "LEFT OUTER JOIN [Navision4].[dbo].[3S Company A_S$Item Cross Reference] ON [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].[dbo].[3S Company A_S$Item Cross Reference].[Item No_] AND [Navision4].[dbo].[3S Company A_S$Item Cross Reference].[Cross-Reference Type No_] = 'EAN'  "

            "LEFT OUTER JOIN [Navision4].[dbo].[3S disponibelt for export] ON [Navision4].[dbo].[3S disponibelt for export].[Item No_] = [Navision4].[dbo].[3S Company A_S$Item].[No_] " 
            "LEFT OUTER JOIN [Navision4].[dbo].[3S Lagersaldo] ON [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].[dbo].[3S Lagersaldo].[Item no_] " 
            "LEFT OUTER JOIN [Navision4].[dbo].[3S i_bestilling] ON [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].[dbo].[3S i_bestilling].[No_] " 
            "WHERE [Navision4].[dbo].[3S Company A_S$Item].[No_] <> ''  " & strWhereItemCategory & " " & strWhereItemVendor & " " & strWhereItemStatus & " " & strWhereItemC & " " & strWhereItemE & " " & strWhereQty & " " & strWhereMisc & "  " 
            "ORDER BY [Navision4].[dbo].[3S Company A_S$Item].[" & strOrderSort & "] " & strSort & ""

But I want to only SELECT TOP 1 from this OUTER LEFT JOIN "LEFT OUTER JOIN [Navision4].[dbo].[3S Company A_S$Item Cross Reference] ON [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].[dbo].[3S Company A_S$Item Cross Reference].[Item No_] AND [Navision4].[dbo].[3S Company A_S$Item Cross Reference].[Cross-Reference Type No_] = 'EAN' "

How can I do that?

Can you remove those " &_ for us since they have nothing to do with the sql query. Show the sql query without the VB.NET variables. — Tim Schmelter

Giorgi Nakeuri Giorgi Nakeuri · Accepted Answer · 2015-02-03T11:00:44

Instead of Left Join use Outer Apply like

 Outer Apply(select top 1 * from [Navision4].[dbo].[3S Company A_S$Item Cross
 Reference] WHERE [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].
[dbo].[3S Company A_S$Item Cross Reference].[Item No_] AND [Navision4].[dbo].[3S 
 Company A_S$Item Cross Reference].[Cross-Reference Type No_] = 'EAN' Order BY someValue) o

In Order BY someValue change someValue to appropriate columns. Also use aliases to get more readable statements.

The final statement:

SELECT  [Navision4].[dbo].[3S Company A_S$Item].[No_] ,
        [Navision4].[dbo].[3S Company A_S$Item].[Description] AS Description ,
        [Navision4].[dbo].[3S Company A_S$Item].[Description 2] ,
        [Navision4].[dbo].[3S Company A_S$Item].[Item Status] ,
        [Navision4].[dbo].[3S Company A_S$Item].[New Sales Price DK] ,
        [Navision4].[dbo].[3S Company A_S$Item].[New List Price DK] ,
        [Navision4].[dbo].[3S Company A_S$Item].[New Sales Price No] ,
        [Navision4].[dbo].[3S Company A_S$Item].[New List Price No] ,
        [Navision4].[dbo].[3S Company A_S$Item].[New Sales Price S] ,
        [Navision4].[dbo].[3S Company A_S$Item].[New List Price S] ,
        o.[Cross-Reference No_] ,
        [Navision4].[dbo].[3S disponibelt for export].[Disponibelt] ,
        [Navision4].[dbo].[3S Lagersaldo].[Quantity] AS Lagerantal ,
        [Navision4].[dbo].[3S i_bestilling].[Expected Receipt Date] AS ToDispDate
FROM    [Navision4].[dbo].[3S Company A_S$Item]
        OUTER APPLY ( SELECT TOP 1
                                *
                      FROM      [Navision4].[dbo].[3S Company A_S$Item Cross Reference]
                      WHERE     [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].[dbo].[3S Company A_S$Item Cross Reference].[Item No_]
                                AND [Navision4].[dbo].[3S Company A_S$Item Cross Reference].[Cross-Reference Type No_] = 'EAN'
                      ORDER BY  someValue
                    ) o
        LEFT OUTER JOIN [Navision4].[dbo].[3S disponibelt for export] ON [Navision4].[dbo].[3S disponibelt for export].[Item No_] = [Navision4].[dbo].[3S Company A_S$Item].[No_]
        LEFT OUTER JOIN [Navision4].[dbo].[3S Lagersaldo] ON [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].[dbo].[3S Lagersaldo].[Item no_]
        LEFT OUTER JOIN [Navision4].[dbo].[3S i_bestilling] ON [Navision4].[dbo].[3S Company A_S$Item].[No_] = [Navision4].[dbo].[3S i_bestilling].[No_]

TOP 1 from LEFT OUTER JOIN

1 Answers