Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2013
    Posts
    4

    Unanswered: Comparing rows in a 6-table inner join

    I have inner joined 6 tables to pull specific data. I get results just fine, but I'd like to add a condition where if birth_dates of rows are equal, but the person_ids are different, then the results are shown. Else, they're filtered out. When I try to union all, I get an error that I'm missing a right paren. I'm sure there are other issues as well. Any help is appreciated!

    Code:
    select t3.field1, t6.field2, t6.field3, t3.field4, t3.field5 
    from (select * from table1 t1
        inner join table2 t2 on t1.field = t2.field
        inner join table3 t3 on t3.field = t1.field
        inner join table4 t4 on t4.field = t1.field
        inner join table5 t5 on t5.field = t4.field
        inner join table6 t6 on t6.field = t3.field)
          where (t1.field = 'xxx' 
          and t2.field = 'xxx' 
          and t2.field is null 
          and t4.field = 'xxx'
          and t5.field = 'xxx' 
          and t1.field ='xxx' 
          and t5.field is null) AS one
    
    union all
    
    select t3.field1, t6.field2, t6.field3, t3.field4, t3.field5 
    from (select * from table1 t1
        inner join table2 t2 on t1.field = t2.field
        inner join table3 t3 on t3.field = t1.field
        inner join table4 t4 on t4.field = t1.field
        inner join table5 t5 on t5.field = t4.field
        inner join table6 t6 on t6.field = t3.field)
          where (t1.field = 'xxx' 
          and t2.field = 'xxx' 
          and t2.field is null 
          and t4.field = 'xxx'
          and t5.field = 'xxx' 
          and t1.field ='xxx' 
          and t5.field is null) AS two
    
    where one.birth_date = two.birth_date
    and one.person_id < two.person_id
    group by p.person_id
    order by cb.case_id ASC;
    Last edited by Twitchie; 11-08-13 at 17:01.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    syntax appears incorrect
    Code:
    [oracle@localhost ~]$ sqlplus scott/tiger
    
    SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 8 12:36:49 2013
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select sysdate from dual@orcl;
    
    SYSDATE
    ---------
    08-NOV-13
    why does period exist before at sign as in "t1.@matching"?
    why not log onto correct DB & schema & avoid SQL*Net in the query altogether?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2013
    Posts
    4
    The @matching is just what I used to say that field of that table matches on the field of the next table.

  4. #4
    Join Date
    Nov 2013
    Posts
    4
    Updated example.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The @matching is just what I used to say that field of that table matches on the field of the next table.

    when you insist on making up your own syntax, you should expect errors.
    use only valid syntax & no error gets thrown
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A CTE is probably a much better way to do whatever you are trying to accomplish, but if I guessed right about all of your intentions then you could use:
    Code:
    SELECT * 
       FROM 
          (  
             SELECT t3.field1 AS s1f1, t6.field2 AS s1f2
    ,           t6.field3 AS s1f3, t3.field4 AS s1f4, t3.field5 AS s1f5
                FROM table1 t1
                INNER JOIN table2 t2 ON t1.@matching = t2.@matching
                INNER JOIN table3 t3 ON t3.@matching = t1.@matching
                INNER JOIN table4 t4 ON t4.@matching = t1.@matching
                INNER JOIN table5 t5 ON t5.@matching = t4.@matching
                INNER JOIN table6 t6 ON t6.@matching = t3.@matching)
                WHERE (t1.field = 'xxx' 
                   AND t2.field = 'xxx' 
                   AND t2.field IS NULL 
                   AND t4.field = 'xxx'
                   AND t5.field = 'xxx' 
                   AND t1.field = 'xxx' 
                   AND t5.field IS NULL)
          ) AS one
       INNER JOIN
          (
             SELECT t3.field1 AS s2f1, t6.field2 AS s2f2
    ,           t6.field3 AS s2f3, t3.field4 AS s2f4, t3.field5 AS s2f5
                FROM table1 t1
                INNER JOIN table2 t2 ON t1.@matching = t2.@matching
                INNER JOIN table3 t3 ON t3.@matching = t1.@matching
                INNER JOIN table4 t4 ON t4.@matching = t1.@matching
                INNER JOIN table5 t5 ON t5.@matching = t4.@matching
                INNER JOIN table6 t6 ON t6.@matching = t3.@matching)
                WHERE (t1.field = 'xxx' 
                   AND t2.field = 'xxx' 
                   AND t2.field IS NULL 
                   AND t4.field = 'xxx'
                   AND t5.field = 'xxx' 
                   AND t1.field = 'xxx' 
                   AND t5.field IS NULL
          ) AS two
          ON one.birth_date = two.birth_date
       WHERE  one.person_id  < two.person_id
       GROUP BY one.person_id
       ORDER BY one.case_id ASC;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A possible better solution would be:
    Code:
    Floom:
    
    Delgrog...
    
    Exples, delzel!
    All you need to do is figure out what I meant by those substitutions. They work just like your matching example!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from SQL below
    Code:
    SELECT Count(*) 
    FROM   table1 t1 
           inner join table2 t2 
                   ON t1.field = t2.field 
           inner join table3 t3 
                   ON t3.field = t1.field 
           inner join table4 t4 
                   ON t4.field = t1.field 
           inner join table5 t5 
                   ON t5.field = t4.field 
           inner join table6 t6 
                   ON t6.field = t3.field 
    WHERE  ( t1.field = 'xxx' 
             AND t2.field = 'xxx' 
             AND t2.field IS NULL 
             AND t4.field = 'xxx' 
             AND t5.field = 'xxx' 
             AND t1.field = 'xxx' 
             AND t5.field IS NULL )
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Nov 2013
    Posts
    4
    Count is 165086

    Quote Originally Posted by anacedent View Post
    post results from SQL below
    Code:
    SELECT Count(*) 
    FROM   table1 t1 
           inner join table2 t2 
                   ON t1.field = t2.field 
           inner join table3 t3 
                   ON t3.field = t1.field 
           inner join table4 t4 
                   ON t4.field = t1.field 
           inner join table5 t5 
                   ON t5.field = t4.field 
           inner join table6 t6 
                   ON t6.field = t3.field 
    WHERE  ( t1.field = 'xxx' 
             AND t2.field = 'xxx' 
             AND t2.field IS NULL 
             AND t4.field = 'xxx' 
             AND t5.field = 'xxx' 
             AND t1.field = 'xxx' 
             AND t5.field IS NULL )

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •