How to get the first row from a join

How to get the first row from a join

  
Hi Outsystems supporters

I have a question on getting only one row from a joined table:

my  items tabel has a lin to many barcodes
in the list I want to show only the first occuring eancode

this is what i tested
Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 1451 StartFragment: 314 EndFragment: 1419 StartSelection: 314 EndSelection: 314
SELECT  {ShopItems}.*,
        {EANItem}.*

from {ShopItems}

inner join (select TOP 1 {EANItem}.* from {EANItem}) 
        on {ShopItems}.[Id]={EANItem}.[ShopItemId]
but it comes with errors

kind regards, dop hegger
Hello Dop,

I would recommend you to use a simple query, press the test button and watch the generated SQL code under the SQL tab in the query.

You should be able to see you're mistake :).

Kind regards,
Evert
Hello Evert,

Thanks for your reply.
The error message states: incorrect syntax near the keyword 'on'.
I'm not an expert on sql. I worked with pervasive in the past.
It allowed me to make this kind link very easy.


Evert van der zalm wrote
:
Hello Dop,

I would recommend you to use a simple query, press the test button and watch the generated SQL code under the SQL tab in the query.

You should be able to see you're mistake :).

Kind regards,
Evert
 
 
Hi Dop,
 
You can write the query to get the first record with something like this:
 
SELECT TOP 1 {ShopItems}.*, {EANItem}.*
from {ShopItems}
inner join {EANItem} on {ShopItems}.[Id]={EANItem}.[ShopItemId]
 
But you should use a simple query, check the image bellow.


 
Regards,
Nelson Baptista
Nelson Baptista wrote:
Dear nelson,

Your solution returns only one record from the whole list.

What I am looking for is a all the shopitems with or without a EANCode
see attached doc

 
Hi Dop,
 
My solution was to  "get the first row from a join", It was what I've understood from your question but based on your last comment I saw that I didn't understand well.
 
Now, I think that I understand what you want to achieve:
You want to get all shopitems where there is relation between the ShopItems and EANItem.
 
So,  you need to do an advance query to only get one shopItem even if there more than one EANItem related with.




Let us know if it works,
Regards,
Nelson Baptista
Hi Nelson,

There is a small detail on your TSQL statement that is causing the error.
You need to use what is called a derived table.
Something like this:

SELECT {Order_Line}.[Id]
FROM {Order_Line} INNER JOIN (SELECT TOP 1 [Id]
                              FROM {Order}) as DerivedTable ON ({Order_Line}.[OrderId] = DerivedTable.[Id])
I'm not correcting your code but the intended purpose you will get the point.
I hope this helps.

Regards.
Pedro Ávila
Dear Nelson

I wonder whether you received my latest reply with the solution I found
by  adding an action selecting the ean of the item and returning the first row.


Do you know the impact on the performance of this solution?

Kind regrads,
Dop hegger
I am a developer of pdf417 of .net applications.

I'll use a concrete, but hypothetical, example.

Each Order normally has only one line item:

Orders:

OrderGUID   OrderNumber
=========   ============
{FFB2...}   STL-7442-1      
{3EC6...}   MPT-9931-8A

LineItems:

LineItemGUID   Order ID Quantity   Description
============   ======== ========   =================================
{098FBE3...}   1        7          prefabulated amulite
{1609B09...}   2        32         spurving bearing

But occasionally there will be an order with two line items:

LineItemID   Order ID    Quantity   Description
==========   ========    ========   =================================
{A58A1...}   6,784,329   5          pentametric fan
{0E9BC...}   6,784,329   5          differential girdlespring 

Normally when showing the orders to the user:

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
    INNER JOIN LineItems 
    ON Orders.OrderID = LineItems.OrderID

I want to show the single item on the order. But with this occasional order containing two (or more) items, the orders would appear be duplicated:

OrderNumber   Quantity   Description
===========   ========   ====================
STL-7442-1    7          prefabulated amulite
MPT-9931-8A   32         differential girdlespring
KSG-0619-81   5          panametric fan
KSG-0619-81   5          differential girdlespring

What I really want is to have SQL Server just pick one, as it will be good enough:

OrderNumber   Quantity   Description
===========   ========   ====================
STL-7442-1    7          prefabulated amulite
MPT-9931-8A   32         differential girdlespring
KSG-0619-81   5          panametric fan

If I get adventurous, I might show the user, an ellipsis to indicate that there's more than one:

OrderNumber   Quantity   Description
===========   ========   ====================
STL-7442-1    7          prefabulated amulite
MPT-9931-8A   32         differential girdlespring
KSG-0619-81   5          panametric fan, ...

So the question is how to either

  • eliminate "duplicate" rows
  • only join to one of the rows, to avoid duplication

First attempt

My first naive attempt was to only join to the "TOP 1" line items:

SELECT Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM Orders
    INNER JOIN (
       SELECT TOP 1 LineItems.Quantity, LineItems.Description
       FROM LineItems
       WHERE LineItems.OrderID = Orders.OrderID) LineItems2
    ON 1=1

But that gives the error:

The column or prefix 'Orders' does not match with a table name or alias name used in the query.

Presumably because the inner select doesn't see the outer table.

Hi Jacob,

From the SQL statement you mention, seems that you didn't use the correct syntax of the OS Platform which is {Entity}.[Attribute]
That means {Orders}.[OrderNumber] and so on...

Regarding the SQL itself, I would try:
SELECT {Orders}.[OrderNumber], {LineItems}.[Quantity], {LineItems}.[Description]
FROM {Orders}
INNER JOIN {LineItems} ON {LineItems}.[OrderID] = {Orders}.[OrderID]
    AND {LineItems}.[OrderID] IN (
            SELECT TOP 1 LI.[OrderID]
            FROM {LineItems} LI
            WHERE LI.[OrderID] = {Orders}.[OrderID])
You are using SQL Server right? Because SELECT TOP 1 will not work on Oracle.
You can also try this (assuming you are running SQL Server):

SELECT O.[OrderNumber], OL.[Quantity], OL.[Description]
FROM {Orders} O OUTER APPLY
(
   SELECT TOP LI.[Quantity], LI.[Description]
   FROM {LineItems} LI 
   WHERE LI.[OrderID] = O.[OrderID]
) OL 
It should give you better performance on large data sets because it is a little less resource intensive than the previous suggested query.