SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    531
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    WHERE Clause Question

    i have a database that is kinda set up like this...

    Code:
     	 Level1	 |	 Level2	 |	 Level3
     ------------------------------------------
     		 B				  1				 1
     		 B				  1				 2
     		 B				  2 			    1
     		 B				  2 			    2
     		 C 				 1				 1
     		 B				  1				 2
    etc...

    my dilema comes when i want to select ALL records with a Level3 value of 1, but i want to leave out records that are B.4.1, H.1.1, H.2.1, I.1.1, I.1.2, J.2.1.

    right now, i'm attempting what i thought was a valiant first effort at getting only these certain records using a query such as:

    Code:
     SELECT Level1, Level2, Level3
     FROM tblMyTable
     WHERE Level3 = 1 AND Level1 <> 'H' AND Level1 <> 'I' AND (Level1 <> 'B' AND Level2 <> 4) AND (Level1 <> 'J' AND Level2 <> 2)
     ORDER BY Level1, Level2
    any ideas would be appreciated...

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    that's the place to use some ORs:

    Code:
    WHERE
      Level3 = 1
      AND NOT ((Level1 = 'B' AND Level2 = '4')
      OR (Level1 = 'H' AND Level2 = '1')
      OR (Level1 = 'H' AND Level2 = '2')
      OR (Level1 = 'I' AND Level2 = '1')
      OR (Level1 = 'I' AND Level2 = '2')
      OR (Level1 = 'J' AND Level2 = '2'))
    assuming that it was I.2.1 you wanted to leave out and not I.1.2
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


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
  •