SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,211
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    all the records which has no relation with the key

    Code:
    query1
    
    select t
    from tag
    
    result1
    
    1
    2
    3
    4
    5
    7
    9
    The query1 above produces the result1 above.
    The query2 below produces the result2 below.
    Code:
    query2
    
    select t1,t2
    from relate
    
    result2
    
    (t1) (t2)
    (1)  (2)
    (4)  (5)
    (7)  (4)
    I like to produce like the following.

    when key is (1),
    it produces all the records which has no relation with the key (1) in relate table.
    It will be (3),(4),(5),(7),(9).

    when key is (2),
    it produces all the records which have no relation with the key (2) in relate table.
    It will be (3),(4),(5),(7),(9).

    when key is (3),
    it produces all the records which has no relation with the key (3) in relate table.
    It will be (1),(2),(4),(5),(7),(9).

    when key is (4),
    it produces all the records which has no relation with the key (4) in relate table.
    It will be (1),(2),(3),(9).

    when key is (5),
    it produces all the records which has no relation with the key (5) in relate table.
    It will be (1),(2),(3),(7),(9).

    when key is (7),
    it produces all the records which has no relation with the key (7) in relate table.
    It will be (1),(2),(3),(5),(9).

    when key is (9),
    it produces all the records which has no relation with the key (9) in relate table.
    It will be (1),(2),(3),(5),(6),(7).

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can'r think of a simpler way

    Code SQL:
    SELECT t1.t FROM table1 t1
     WHERE (t1.t NOT IN (SELECT r.t1 FROM relate r WHERE r.t2 = N))
       AND (t1.t NOT IN (SELECT r.t2 FROM relate r WHERE r.t1 = N))
       AND (t1.t <> N)

    Where N is the value of the key.

    Your values for key=9 do not seem right.


Bookmarks

Posting Permissions

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