Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Query two tables where columns are not equal

    I have set up a content recordset and a links recordset. A link name is included in the links recordset. It's easy to write a query that compares the list of possible link names to the content and displays all the names that have been used on the list by doing this:
    PHP Code:
    SELECT links.linkName
    FROM links
    content
    WHERE links
    .id content.linkID
    ORDER BY links
    .linkName ASC 
    What I want is a list of the names NOT available. Everything I try either results in duplicates of the all names duplicated by the number of unused names or no results. Here's query that gives the list of used names duplicated by the number of unused names:
    PHP Code:
    SELECT links.linkName
    FROM links
    content
    WHERE links
    .id <> content.linkID
    ORDER BY links
    .linkName ASC 

    I have tried all sorts of JOIN's and other logic. I must be missing something simple.
    Last edited by rgEffects; 01-26-2013 at 12:55 AM. Reason: Issue Resolved

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Code:
    SELECT links.linkName
    FROM links
    LEFT JOIN content ON links.id = content.linkID
    WHERE content.linkID IS NULL
    ORDER BY links.linkName ASC
    Try that. I'm terrible with null checking :P. I don't think MySQL support MINUS queries, but the above or using a subquery within the where should do the trick.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    rgEffects (01-26-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    That did it. Never would have guessed that I'd have to put in WHERE content.linkID IS NULL and I didn't find anything like that in 40 minutes of searching...

    Thanks again.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Yep. The concept is simple, if you drop the where clause and select the content.linkID as well, the left join guarantees all records from links, and only matching records from content. So only ones that have record in both would have content.linkID with a provided value. The rest would have null, and to remove the ones that are not null would give a list where content has no matching results to link.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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