SQL Help

[lesstjm]

I have two tables

Table A
column1 varchar2(1)

Table B

column1 varchar2(1)


Table A has values

X

Table B has values

X
X
X


When I do a minus I get no row returned because the X from one table minuses out all the X from the other table

select * from table a
minus
select * from table b;

no row returned


Is there a way in SQL to minus out one row from table B ans then print out the two remaining rows???

[padders]

There is no MINUS ALL (or whatever) equivalent for UNION ALL. To avoid the SORT UNIQUE you would have to make the rows not unique. One way would be to give them a number for each occurrence.

Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SELECT column_name
  2  FROM  (SELECT column_name, 
  3                ROW_NUMBER () OVER (
  4                  PARTITION BY column_name 
  5                  ORDER BY NULL)
  6         FROM   table_b
  7         MINUS
  8         SELECT column_name, 
  9                ROW_NUMBER () OVER (
 10                  PARTITION BY column_name 
 11                  ORDER BY NULL)
 12         FROM   table_a);

C
-
X
X

SQL>

Incidentally in 10g the MULTISET EXCEPT collection operator exhibits the behaviour you are looking for by default although you would have to get your rows into collections beforehand.

SQL> CREATE OR REPLACE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2 (1);
  2  /

Type created.

SQL> SELECT column_value
  2  FROM   TABLE 
  3       ((SELECT (SELECT CAST (COLLECT (column_name) 
  4                          AS VARCHAR2_TABLE)
  5                 FROM   table_b) 
  6                 MULTISET EXCEPT 
  7                (SELECT CAST (COLLECT (column_name) 
  8                          AS VARCHAR2_TABLE) 
  9                 FROM   table_a) 
 10         FROM dual)); 

C
-
X
X

SQL>