How do I limit the number of rows returned by this LEFT JOIN to one?

Question

So I think I've seen a solution to this however they are all very complicated queries. I'm in oracle 11g for reference.

What I have is a simple one to many join which works great however I don't need the many. I just want the left table (the one) to just join any 1 row which meets the join criteria...not many rows.

I need to do this because the query is in a rollup which COUNTS so if I do the normal left join I get 5 rows where I only should be getting 1.

So example data is as follows:

TABLE 1:
-------------
TICKET_ID      ASSIGNMENT
5              team1
6              team2

TABLE 2:
-------------
MANAGER_NAME   ASSIGNMENT_GROUP  USER
joe            team1             sally
joe            team1             stephen
joe            team1             louis
harry          team2             ted
harry          team2             thelma

what I need to do is join these two tables on ASSIGNMENT=ASSIGNMENT_GROUP but only have 1 row returned.

when I do a left join I get three rows returned beaucse that is the nature of hte left join

Answer 1

You could do something like this.

SELECT t1.ticket_id, 
       t1.assignment,
       t2.manager_name,
       t2.user
  FROM table1 t1
       LEFT OUTER JOIN (SELECT manager_name,
                               assignment_group,
                               user,
                               row_number() over (partition by assignment_group
                                                    --order by <<something>>
                                                 ) rnk
                          FROM table2) t2
                     ON (    t1.assignment = t2.assignment_group
                         AND t2.rnk = 1 )

This partitions the data in table2 by assignment_group and then arbitrarily ranks them to pull one arbitrary row per assignment_group. If you care which row is returned (or if you want to make the row returned deterministic) you could add an ORDER BY clause to the analytic function.

Answer 2

If oracle supports row number (partition by) you can create a sub query selecting where row equals 1.

SELECT * FROM table1
LEFT JOIN
(SELECT *
FROM   (SELECT *,
           ROW_NUMBER()
             OVER(PARTITION BY assignmentgroup ORDER BY assignmentgroup) AS Seq
    FROM  table2) a
WHERE  Seq = 1) v
ON assignmet = v.assignmentgroup