Home » SQL & PL/SQL » SQL & PL/SQL » SUBQUERY QUESTION
SUBQUERY QUESTION [message #212304] Thu, 04 January 2007 11:01 Go to next message
abeiko
Messages: 29
Registered: March 2005
Junior Member
I am trying to write a query that will give me the rows that don't have a duplicate row in a table. I have a column called link_id. If something is "matched" there will be 2 or more rows in the table with the same link_id (number) value.
I need a query that gives me the rows that only have 1 row with the same link_id value.

All the subqueries I have tried don't return anything.
Below is some sample code:

SELECT * FROM tmp_match_name_changes t1
WHERE t1.link_id IS NOT NULL
AND session_id = 2696328
AND (t1.link_id
,t1.gas_day
,t1.from_party_id
,t1.to_party_id
,t1.from_trading_location_id
,NVL(t1.from_transfer_eba_id,-1)
,NVL(t1.to_transfer_eba_id,-1))
NOT IN
( SELECT DISTINCT t2.link_id
,t2.gas_day
,t2.from_party_id
,t2.to_party_id
,t2.from_trading_location_id
,NVL(t2.from_transfer_eba_id,-1)
,NVL(t2.to_transfer_eba_id,-1)
FROM tmp_match_name_changes t2
GROUP BY t2.gas_day
,t2.link_id
,t2.from_party_id
,t2.to_party_id
,t2.from_trading_location_id
,NVL(t2.from_transfer_eba_id,-1)
,NVL(t2.to_transfer_eba_id,-1))

[Updated on: Thu, 04 January 2007 11:02]

Report message to a moderator

Re: SUBQUERY QUESTION [message #212309 is a reply to message #212304] Thu, 04 January 2007 11:15 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you correctly, you might check this example and apply such a solution to your problem.
SQL> CREATE TABLE TEST (link_id NUMBER);

Table created.

SQL> INSERT ALL
  2    INTO TEST VALUES (10)
  3    INTO TEST VALUES (10)
  4    INTO TEST VALUES (20)  -- 20 doesn't have duplicates
  5    INTO TEST VALUES (30)  
  6    INTO TEST VALUES (30)
  7    INTO TEST VALUES (40)  -- 40 doesn't have duplicates
  8  SELECT * FROM dual;

6 rows created.

SQL> SELECT link_id
  2    FROM TEST
  3    GROUP BY link_id
  4    HAVING COUNT(*) = 1;

   LINK_ID
----------
        20
        40

SQL>
Re: SUBQUERY QUESTION [message #212331 is a reply to message #212309] Thu, 04 January 2007 14:11 Go to previous message
abeiko
Messages: 29
Registered: March 2005
Junior Member
Thanks.
That worked.

[Updated on: Thu, 04 January 2007 14:12]

Report message to a moderator

Previous Topic: group by problem, another one...
Next Topic: Query Help... need some ideas
Goto Forum:
  


Current Time: Tue Dec 06 02:25:50 CST 2016

Total time taken to generate the page: 0.14678 seconds