6 Replies Latest reply on May 17, 2012 4:17 AM by jay1980

    Joining question...

    bostonmacosx
      I asked this before but didn't get really an answer that works.

      from SMINCREQ
      left outer join SMOPERATOR on SMINCREQ.assignment=SMOPERATOR.primary_assignment_group

      id in the middle of my query. The way that joins work I can get back anywhere from 0-5 rows because the SMOPERATOR table can contain that.

      What I want to get back is ONE and only ONE row for each join. Is there a way to craft a subquery for the join which will limit this?

      Thanks
      ROb
        • 1. Re: Joining question...
          sb92075
          bostonmacosx wrote:
          I asked this before but didn't get really an answer that works.

          from SMINCREQ
          left outer join SMOPERATOR on SMINCREQ.assignment=SMOPERATOR.primary_assignment_group

          id in the middle of my query. The way that joins work I can get back anywhere from 0-5 rows because the SMOPERATOR table can contain that.

          What I want to get back is ONE and only ONE row for each join.
          Does it matter which ONE gets returned?
          • 2. Re: Joining question...
            Tubby
            bostonmacosx wrote:
            I asked this before but didn't get really an answer that works.

            from SMINCREQ
            left outer join SMOPERATOR on SMINCREQ.assignment=SMOPERATOR.primary_assignment_group

            id in the middle of my query. The way that joins work I can get back anywhere from 0-5 rows because the SMOPERATOR table can contain that.

            What I want to get back is ONE and only ONE row for each join. Is there a way to craft a subquery for the join which will limit this?

            Thanks
            ROb
            Join to something like
            select *
            from
            (
            select 
               <columns>, 
               row_number() over (partition by primary_assignment_group order by <pick something intelligent here>) as rn
            from SMOPERATOR
            )
            where rn = 1
            As mentioned, you'll need to find something "intelligent" to order the results by. Presumably you have some logic that dictates which row is "the first".

            Typically when someone says "i don't care which row" they don't understand the data model, or the business requirement (possibly both :)

            Cheers,
            • 3. Re: Joining question...
              Paulie
              Your post is cryptic. Read the FAQ and also the post by BluShadow at the top of the list (marked
              with *).

              Provide DDL (CREATE TABLE) and DML (INSERT INTO Table_name VALUES(...)

              Provide the result you want with the logic you used to get it.

              Use the [code   ] tags.

              Help us to help you.

              Paul...
              • 4. Re: Joining question...
                bostonmacosx
                I do not care what row gets returned from the joined table...I have where commands which limit what rows I will get back from the base table.

                I do not consider this a cryptic post as I'm just asking how in general to limit the amount of joined rows to 1 from some number greater then one. I was hoping for a "way" to do this and not necessarily a specific example against my data.

                Rob
                • 5. Re: Joining question...
                  sb92075
                  bostonmacosx wrote:
                  I do not care what row gets returned from the joined table...I have where commands which limit what rows I will get back from the base table.

                  I do not consider this a cryptic post as I'm just asking how in general to limit the amount of joined rows to 1 from some number greater then one. I was hoping for a "way" to do this and not necessarily a specific example against my data.

                  Rob
                  WHERE ROWNUM =1
                  • 6. Re: Joining question...
                    jay1980
                    Hi,

                    You can use an exist clause to get the information if one of the tables have only one row for each assignment group

                    SELECT * FROM TABLE A WHERE EXISTS (SELECT NULL FROM TABLE B WHERE A.KEY = B.KEY)

                    Exists will find the first matching row and exit, instead if you do a join you will get multiple rows and and then you need to do a distinct.

                    Also, there is the analytics function as mentioned in the previous post.

                    Thanks,
                    Jayadeep