Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Compare rows in a table

    Hi,

    I want to compare 2 rows in a table. I did an inner join, but got the result displayed in a single line. Is it possible to display the joins not in a single row but below each other? How can I only show the difference (hide attributes that are the same)?

    Thanks!

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Check out NULLIF to "display" only values that differs.

    NULLIF(x1, x2)
    is equivalent to
    CASE WHEN x1 = x2 THEN NULL ELSE x1 END
    (If x1 and x2 are equal, NULL is returned. If x1 and x2 are not equal x1 is returned.)

    How does your query look right now? How do you find those two rows to compare?

  3. #3
    Join Date
    Jul 2012
    Posts
    6
    Hi,

    thanks for answering. I am still at the beginning and not sure how to proceed.

    Code:
    SELECT * FROM PRODUCT a
    	inner JOIN PRODUCT b
    	ON a.GRP = b.GRP
    	WHERE a.ID='05536f7a'
    	AND b.ID='f1fabdfb'

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    See what happens if you try:
    SELECT nullif(a.col1,b.col1), nullif(a.col2, b.col2) etc
    FROM PRODUCT a
    inner JOIN PRODUCT b
    ON a.GRP = b.GRP
    WHERE (a.ID='05536f7a' AND b.ID='f1fabdfb')
    OR (b.ID='05536f7a' AND a.ID='f1fabdfb')

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If your DBMS supports OLAP operations,
    this query example may be another option.

    Mimer SQL Developers - Mimer SQL-2003 Validator

    Mimer SQL-2003 Validator

    Check your SQL against the SQL-2003 standard using the Mimer SQL Validator 2003.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    SELECT id , grp
         , CASE min_col1 WHEN max_col1 THEN NULL ELSE col1 END AS col1
         , CASE min_col2 WHEN max_col2 THEN NULL ELSE col2 END AS col2
        /* additional columns */
     FROM  (SELECT p.*
                 , MIN(col1) OVER() AS min_col1
                 , MAX(col1) OVER() AS max_col1
                 , MIN(col2) OVER() AS min_col2
                 , MAX(col2) OVER() AS max_col2
                /* additional columns */
             FROM  product p
             WHERE id IN ('05536f7a' , 'f1fabdfb')
           ) s
     ORDER BY
           id
    ;
    Code:
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    T611, "Elementary OLAP operations"
    F591, "Derived tables"
    T351, "Bracketed SQL comments (/*...*/ comments)"
    Last edited by tonkuma; 08-24-12 at 14:06. Reason: Edit display format of quoted documens.

Posting Permissions

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