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

Solution 1

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

Solution 2

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.

Solution 3

I think what you need is to use GROUP BY on the ASSIGNMENT_GROUP field.

http://www.w3schools.com/sql/sql_groupby.asp

Solution 4

In Oracle, if you want 1 result, you can use the ROWNUM statement to get the first N values of a query e.g.:

SELECT *
FROM TABLEX
WHERE
ROWNUM = 1 --gets the first value of the result

The problem with this single query is that Oracle never returns the data in the same order. So, you must oder your data before use rownum:

SELECT *
FROM
    (SELECT * FROM TABLEX ORDER BY COL1)
WHERE
ROWNUM = 1

For your case, looks like you only need 1 result, so your query should look like:

SELECT *
FROM
    TABLE1 T1
    LEFT JOIN 
    (SELECT *
    FROM TABLE2 T2 WHERE T1.ASSIGNMENT = T2.ASSIGNMENT_GROUP
    AND
    ROWNUM = 1) T3 ON T1.ASSIGNMENT = T3.ASSIGNMENT_GROUP

Solution 5

In MySQL you could just GROUP BY ASSIGNMENT and be done. Oracle is more strict and refuses to just choose (in an undefined way) which values of the three rows to choose. That means all returned columns need to be part of GROUP BY or be subject to an aggregat function (COUNT, MIN, MAX...)

You can of course choose to just don't care and use some aggregat function on the returned columns.

select TICKET_ID, ASSIGNMENT, MAX(MANAGER_NAME), MAX(USER)
from T1
left join T2 on T1.ASSIGNMENT=T2.ASSIGNMENT_GROUP
group by TICKET_ID, ASSIGNMENT

If you do that I would seriously doubt that you need the JOIN in the first place.

MySQL could also help with GROUP_CONCAT in the case that you want a string concatenation of group values for a column (humans often like that), but with Oracle that is staggeringly complex.

Using a subquery as already suggested is an option, look here for an example. It also allows you to sort the subquery before selecting the top row.

View more solutions