Results 1 to 8 of 8
  1. #1
    SQL: HAVING question

    Member Since
    Oct 24, 2006
    Posts
    95
    SQL: HAVING question
    I have two tables, Table A and Table B. Table B has a foreign key to Table A. Table B has one other column, it's a single character. There can be multiple rows in Table B tied to Table A

    So you get something like this:

    Code:
    TABLE A
    ----------
    ID  NAME
    ----------
    1   Bill
    2   Tom
    3   Steve
    
    TABLE B
    ---------
    ID STATUS
    ---------
    1  Y
    2  Y
    2  M
    2  N
    3  N
    3  N
    3  Y
    I want to end up getting just 'Y' but not 'M'. So my query should return Bill and Steve.

    I thought I could do something like this:

    Code:
    SELECT NAME FROM TABLEA
    INNER JOIN TABLEB ON TABLEA.ID=TABLEB.ID 
    GROUP BY NAME
    HAVING NOT STATUS='M'
    That doesn't work though. I am thinking there is a way to do this with the HAVING but I can't figure it out. Any ideas?

  2. #2
    SQL: HAVING question
    xstep's Avatar
    Member Since
    Jun 25, 2005
    Location
    On the road
    Posts
    3,231
    Specs:
    2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid
    Here is one idea.

    SELECT NAME FROM TABLEA AS A1
    INNER JOIN TABLEB AS B1 ON TABLEA.ID=B1.ID
    AND B1.STATUS='Y'
    LEFT OUTER JOIN TABLEB AS B2 ON B1.ID=B2.ID
    AND B2.STATUS='M'
    WHERE B2.STATUS IS NULL
    GROUP BY NAME


    Before the where clause, the query is effectively returning the following record complex.

    Code:
    A1.NAME   B1.STATUS   B2.STATUS
    -------   ---------   ------------
    Bill      Y           NULL
    Tom       Y           M
    Steve     Y           NULL
    The where clause filters on those NULL values of B2.STATUS thereby excluding the status which is M.
    CameraTime - Time lapse photography for novice and advanced users.

    When asking questions, post the version of your software. You'll receive better answers.

    Please post your results to the thread as it is good feedback.

  3. #3
    SQL: HAVING question

    Member Since
    Dec 31, 2005
    Posts
    24
    xstep, at first I thought nice solution but then why not go simpler?

    I hope I have the tables right:
    Code:
    CREATE TABLE `A` (
      `id` tinyint(2) NOT NULL auto_increment,
      `name` varchar(50) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
    
    INSERT INTO `A` VALUES(1, 'Bill');
    INSERT INTO `A` VALUES(2, 'Tom');
    INSERT INTO `A` VALUES(3, 'Steve');
    
    CREATE TABLE `B` (
      `id` tinyint(2) NOT NULL,
      `status` varchar(100) NOT NULL,
      KEY `id` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    INSERT INTO `B` VALUES(1, 'Y');
    INSERT INTO `B` VALUES(2, 'Y');
    INSERT INTO `B` VALUES(2, 'M');
    INSERT INTO `B` VALUES(2, 'N');
    INSERT INTO `B` VALUES(3, 'N');
    INSERT INTO `B` VALUES(3, 'N');
    INSERT INTO `B` VALUES(3, 'Y');
    
    ALTER TABLE `B`
      ADD CONSTRAINT `B_ibfk_1` FOREIGN KEY (`id`) REFERENCES `A` (`id`);
    Now, why not do this:
    Code:
    SELECT name
    FROM A
    LEFT OUTER JOIN B ON B.id = A.id
    WHERE B.status != 'M'
    AND B.status = 'Y'
    GROUP BY A.name
    Result being:
    Code:
    name
    -------
    Bill
    Tom
    Steve
    PowerBook G4 15" 1.5Ghz | 2GB PC2700 | 80GB HDD | OSX 10.4.11

  4. #4
    SQL: HAVING question
    xstep's Avatar
    Member Since
    Jun 25, 2005
    Location
    On the road
    Posts
    3,231
    Specs:
    2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid
    centered effect,

    You got Tom in your result. Re-read the original post. The key is that the OP didn't want Tom in the result.

    To do this you need to reference table B a second time.
    CameraTime - Time lapse photography for novice and advanced users.

    When asking questions, post the version of your software. You'll receive better answers.

    Please post your results to the thread as it is good feedback.

  5. #5
    SQL: HAVING question

    Member Since
    Dec 31, 2005
    Posts
    24
    Thanks xstep. I read the first sentence and the second too quickly

    I want to end up getting just 'Y' but not 'M'. So my query should return Bill and Steve.
    PowerBook G4 15" 1.5Ghz | 2GB PC2700 | 80GB HDD | OSX 10.4.11

  6. #6
    SQL: HAVING question
    Thyamine's Avatar
    Member Since
    Apr 23, 2007
    Location
    Coatesville, PA
    Posts
    377
    Specs:
    MBP 15", 2.33 GHz, 2Gb
    You could also do something like:

    Code:
    select * from tableA where ID in (select ID from tableB where status = 'Y')
    or similiarly

    Code:
    select * from tableA where ID in (select ID from tableB where status in ('Y', 'N', 'M')
    where you adjust the criteria for what ID's to return.

  7. #7
    SQL: HAVING question
    xstep's Avatar
    Member Since
    Jun 25, 2005
    Location
    On the road
    Posts
    3,231
    Specs:
    2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid
    Thyamine,

    Try those queries against a real database to see your results. You'll find you don't get the result the OP wanted.
    CameraTime - Time lapse photography for novice and advanced users.

    When asking questions, post the version of your software. You'll receive better answers.

    Please post your results to the thread as it is good feedback.

  8. #8
    SQL: HAVING question
    Thyamine's Avatar
    Member Since
    Apr 23, 2007
    Location
    Coatesville, PA
    Posts
    377
    Specs:
    MBP 15", 2.33 GHz, 2Gb
    Ahh, I see. I didn't see that the one ID has both a Y and a M.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. SQL Fiddle
    By OneMoreThing... in forum Apple Rumors and Reports
    Replies: 0
    Last Post: 09-18-2012, 12:40 PM
  2. How do I use SQL on my Mac?
    By djschoo in forum OS X - Apps and Games
    Replies: 0
    Last Post: 07-12-2011, 11:59 PM
  3. SQL Developer
    By pattyz in forum OS X - Apps and Games
    Replies: 1
    Last Post: 11-08-2009, 01:42 AM
  4. SQL Frontend
    By Richie_Gecko in forum OS X - Apps and Games
    Replies: 0
    Last Post: 09-28-2005, 01:40 PM
  5. Mac and SQL
    By ghenrytaylor in forum Running Windows (or anything else) on your Mac
    Replies: 3
    Last Post: 12-16-2004, 05:18 AM

Posting Permissions

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