For each row in a table, get any one linked row in another table

Question

Given the following (heavily simplified) tables:

create table Tags (
   TagId int Primary Key
)

create table OrderLines (
   Branch int,
   Station int,
   TransNo int,
   TagId int foreign key references Tags,
   primary key (Branch, Station, TransNo)
)

I need a list of Tags along with an OrderLine which references each Tag. I am expecting zero or one OrderLines to reference each Tag, but there is nothing in the database constraints to enforce this.

Given input like this:

 OrderLines                                     Tags
 Branch Station TransNo TagId                   TagId
 1      100     2345    1                       1
 1      100     2346    1                       2
 1      101     5223    2                       3
 3      100     6677    4                       4

I want to get output like this:

  TagId     Branch    Station    TransNo  
  1         1         100        2345     <-- it could list 2346 here, don't care
  2         1         101        5223     
  3         null      null       null
  4         3         100        6677

Note that although TagId 1 is referenced twice, my output only contains one of it. It doesn't matter which OrderLine is listed alongside it, but there must be only one instance of each tag in the output.

What is the most efficient way to do this?

I'm not able to alter the database schema.

Answer 1

SELECT Tags.TagID, Branch, Station, TransNo
    FROM Tags
    LEFT JOIN OrderLines ON Tags.TagID = OrderLines.TagID
    ORDER BY Tags.TagID

The left join will make sure that all tags get listed, even those that don't have any order line attached to them. The only flaw is that if more than one order line references a tag, the tag will be listed once for each order line. Some DBMS's (e.g. MySQL) allow you to solve this using a GROUP BY and still select columns you don't group by, but it's nonstandard and there is no guarantee which OrderLine you'll get. If you want this, you'll have to resort to subqueries, unions, temporary tables, or views (but since you can't change the schema, that last option is out).

Answer 2

   select
        T.tagid,
        O.branch,
        O.station,
        O.transno
    from orderlines O
    right join tags T on (t.tagid=O.tagid)
    group by t.tagid

Result:

  tagid branch  station transno
1   1   100 2345
2   1   101 5223
3   NULL    NULL    NULL
4   3   100 6677

Answer 3

You'd have to look at the execution plan to gauge efficiency

;WITH O AS
(
SELECT Branch, Station, TransNo, TagId,
ROW_NUMBER() OVER (PARTITION BY TagId ORDER BY TagId ) AS RN
FROM OrderLines
)
    SELECT T.TagID, O.Branch, O.Station, O.TransNo
        FROM Tags T
        LEFT JOIN O ON T.TagID = O.TagID and RN=1

Answer 4

select t.TagId, t.Station, t.Branch, t.TransNo
from (
SELECT Station, Branch, TransNo, TagId, ROW_NUMBER() OVER (partition by TagId order by TagId) r
From OrderLines) t
WHERE r = 1
UNION ALL
SELECT TagId, NULL, NULL, NULL
from Tags
WHERE NOT EXISTS (Select 1 from OrderLines ol Where ol.TagId = Tags.Id)