Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    27

    Unanswered: What's wrong with this union query

    I don't want the records where the first 4 digits inpartnumber = first 4 digits of part.

    SELECT EntryID, left(Part, 4) as Parts , createtime, Revision FROM ResinCard WHERE newestrev=True UNION select 0 as entryid, left(partnumber,4) as Parts, "" as createtime, "" as revision from partnumber
    where left(partnumber,4) <> left(part,4)
    ORDER BY Parts, revision;

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Does this work for you?

    Code:
    SELECT EntryID, left(Part, 4) as Parts , createtime, Revision FROM ResinCard WHERE newestrev=True 
    UNION 
    select 0 as entryid, left(partnumber,4) as Parts, "" as createtime, "" as revision from partnumber 
    where left(partnumber,4) NOT IN 
    (SELECT DISTINCT LEFT(PART,4) FROM RESINCARD
       WHERE NEWESTREV=TRUE)
    ORDER BY Parts, revision;
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2006
    Posts
    27
    Hell yeah, it did. NOT IN, huh? Never heard of it. I'm a VB.net programmer forced to use Access at my new job and I'm trying ro learn the VBA magic words. Thanks a lot. I owe you a beer.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    IN and it's brother EXISTS are both very helpful in SQL statements.
    If you're starting to do more database work, it would be wise to read up on them.

    I owe you a beer.
    Now I'm getting thirsty... almost time to hit the Legion hall.
    Inspiration Through Fermentation

Posting Permissions

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