CODEWITHSUNDEEP
sqlinner-joinlimitsybase
GarfieldKlon
GarfieldKlon

Reputation: 12188

Limit result of inner join to 1

I have this schema:

create table ord(id int);
create table orderpos(id int, orderid int, descr varchar(255));

insert into ord(id) values (1);
insert into ord(id) values (2);
insert into orderpos(id, orderid, descr) values(1,1,'huba');
insert into orderpos(id, orderid, descr) values(2,1,'blub');
insert into orderpos(id, orderid, descr) values(3,2,'foo');

So the data in ord looks like:

id
1
2

and in orderpos:

id  orderid descr
1   1       huba
2   1       blub
3   2       foo

I'd like to have:

oId opId    orderid   descr
1   1       1         huba
2   3       2         foo

but with this query:

select o.id as oId, op.id as opId, op.orderid, op.descr from ord o
inner join orderpos op on op.orderid = o.id;

I get:

oId opId    orderid   descr
1   1       1         huba
1   2       1         blub
2   3       2         foo

So somehow I have to limit the innerjoin, but how? I tried severel approaches found via google, but nothing seems to work.

This seems to work in SQL-Editor of W3-Schools:

select o.id as oId, op.id as opId, op.orderid, op.descr 
from ord o
inner join (select * from orderpos op2 group by op2.orderid) op on op.orderid = o.id;

And it seems logical to me that it doesn't work as long as you have different values in 'descr'. No clue why this works in the SQL-Editor on W3Schools.

Sybase version: Adaptive Server Enterprise/15.5/EBF 19902 SMP ESD#5.1/P/x86_64/Enterprise Linux/asear155/2594/64-bit/FBO/Wed Jun 6 01:20:27 2012

Upvotes: 1

Views: 3324

Answers (4)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14097

That's what CROSS APPLY is used for:

SELECT o.id as oId, op.id as opId, op.orderid, op.descr 
FROM ord AS o
CROSS APPLY (   SELECT TOP (1) *
                FROM orderpos AS op
                WHERE op.orderid = o.id
                ORDER BY op.id) AS op;

It's a mix of a sub-query and a join. It let's you return multiple columns and rows for a row based condition.

EDIT. Noticed that it's not a request for SQL Server, therefore CROSS APPLY will not work, but this is normally being called a LATERAL JOIN, which seems to be supported by Sybase: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbreference/from-statement.html

Documentation has following syntax:

SELECT *
FROM A, LATERAL( SELECT * FROM B WHERE A.x = B.x ) LDT;

So perhaps this should work:

SELECT *
FROM ord, LATERAL( SELECT TOP 1 * FROM orderpos WHERE orderpos.orderid = ord.id) LDT;

Upvotes: 0

markp-fuso
markp-fuso

Reputation: 35451

Since no logic has been provided for determining which row is the 'first record' (see @HoneyBadger's comments), and you've commented that ordering is not important for your business case, we'll use max() to extract a single row per unique orderid:

select  o.id        as oId,
        op.id       as opId,
        op.orderid,
        op.descr

from    ord      o,
        orderpos op,

        (select op2.orderid,
                max(op2.descr) as descr
        from    orderpos op2
        group by op2.orderid
        ) dt

where   op.orderid = o.id
and     op.orderid = dt.orderid
and     op.descr   = dt.descr
order by 1,2
go

 oId     opId    orderid  descr
 ------- ------- -------- ----------
       1       1        1 huba
       2       3        2 foo

Your join criteria between ord(o) and orderpos(op) remains the same; the addition of the derived table (dt) allows us to further limit the rows that are of interest from orderpos(op).

In this case it's just a coincidence that our use of max() generated the output you're looking for. [HINT: replace max() with min() to display '2/blub' instead of '1/huba'.]

Same idea but using a correlated sub-query instead of a derived table:

select  o.id        as oId,
        op.id       as opId,
        op.orderid,
        op.descr

from    ord      o,
        orderpos op

where   op.orderid = o.id
and     op.descr = (select      max(op2.descr)
                        from    orderpos op2
                        where   op2.orderid = op.orderid)
order by 1,2
go

 oId     opId    orderid  descr
 ------- ------- -------- ----------
       1       1        1 huba
       2       3        2 foo

Alternatively we could have replaced max(op2.descr) with max(op2.id).

The key issue being to pick some method ... any method in this case ... that allows us to pick a single row from op2 for a given orderid (via the group by op2.orderid).

NOTE: The proposed solutions work as long a given descr value is unique for a given orderid (eg, you can't have 2x rows in orderpos with the same orderid and descr). If this is an invalid assumption then we'll need more sample data and/or a better description of the data in the orderpos table (eg, pk contraint, unique index, etc).

Upvotes: 2

Okdel
Okdel

Reputation: 183

You can use exists

select
  o.id as oId,
  op.id as opId,
  op.orderid,
  op.descr
from
  ord o inner join
  orderpos op 
on
  op.orderid = o.id and
  not exists
    (select 1 from orderpos op2 where op2.orderid = o.id and op2.id < op.id)

Upvotes: 0

Ven
Ven

Reputation: 2014

You can select first row or one row by row_number fucntion

WITH x
AS (
    SELECT o.id AS oId
        ,op.id AS opId
        ,op.orderid
        ,op.descr
        ,row_number() OVER (
            PARTITION BY orderid ORDER BY orderid
            ) rows
    FROM ord o
    INNER JOIN orderpos op
        ON op.orderid = o.id
    )
SELECT otd
    ,opid
    ,orderid
    ,DESC
FROM x
WHERE row = 1

Upvotes: 0

Related Questions