SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A "negative" join query

    Hi,

    I have the following problem with the logic for a table join and hope someone here will be able to push me into the right direction.

    I have table A with several records and table B with 0-n rows for each record in table A (basically a dynamic list of attributes for each entry in A).
    Now I would like to fetch all records from A which do not have a defined list of entries in B (basically "give me all records which do not have all required attributes").

    I believe that the following query would provide me with all matches but as I mentioned I'd be looking for the opposite.
    Code:
    SELECT * FROM A
      LEFT JOIN B ON p1.fk=c.id
      GROUP BY B.attr_name, A.id
      HAVING B.attr_name IN (SELECT 'ATTR1' AS attr UNION ALL SELECT 'ATTR2' UNION ALL SELECT 'ATTR3')
    Anyone who can shed a bit of light into this?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT * 
      FROM A
    LEFT OUTER
      JOIN B 
        ON B.fk = A.id
       AND B.attr_name IN ( 'ATTR1' , 'ATTR2' , 'ATTR3' )    
    GROUP 
        BY A.id
    HAVING COUNT(B.attr_name) < 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy, that appears to work just perfectly! Even though I lost you with the IN() and COUNT() clauses .

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The IN clause replaces a list of ORs

    WHERE a=5 OR a=7 OR a=9
    can be replaced with
    WHERE a IN (5,7,9)

    makes for easier typing as your list grows.

    The COUNT clause, you were searching for three different attributes and you told us you wanted those that didn't have all attributes, so therefore the COUNT has to be less than the total number of attributes.

    Clear as mud?

  5. #5
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot guelphdad, I didnt express myself properly. Actually I am aware of the meaning of IN and COUNT. It just took me a while to figure out the logic of this particular query.

    Eventually I realised the key to the task is counting all "attribute" rows for each entry and selecting only those with a count lower than the total. Initially I had a similar approach but without grouping and counting and this led to incorrect results so I hit a wall and lost sight of the forest for the trees

    Thanks again to both of you!


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
  •