SQL: HAVING question

Joined
Oct 24, 2006
Messages
95
Reaction score
1
Points
8
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?
 
Joined
Jun 25, 2005
Messages
3,231
Reaction score
112
Points
63
Location
On the road
Your Mac's 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.
 
Joined
Dec 31, 2005
Messages
24
Reaction score
0
Points
1
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
 
Joined
Jun 25, 2005
Messages
3,231
Reaction score
112
Points
63
Location
On the road
Your Mac's 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.
 
Joined
Apr 23, 2007
Messages
377
Reaction score
4
Points
18
Location
Coatesville, PA
Your Mac's 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.
 
Joined
Jun 25, 2005
Messages
3,231
Reaction score
112
Points
63
Location
On the road
Your Mac's 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.
 
Joined
Apr 23, 2007
Messages
377
Reaction score
4
Points
18
Location
Coatesville, PA
Your Mac's 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.
 

Shop Amazon


Shop for your Apple, Mac, iPhone and other computer products on Amazon.
We are a participant in the Amazon Services LLC Associates Program, an affiliate program designed to provide a means for us to earn fees by linking to Amazon and affiliated sites.
Top