12-24-2008, 02:09 PM

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:

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:

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?

12-24-2008, 07:27 PM

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.

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.

02-01-2009, 10:50 AM

xstep, at first I thought nice solution but then why not go simpler?

I hope I have the tables right:

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:

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:

name
-------
Bill
Tom
Steve

02-01-2009, 08:47 PM

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.

02-03-2009, 06:45 AM

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.

02-06-2009, 01:38 PM

You could also do something like:

select * from tableA where ID in (select ID from tableB where status = 'Y')

or similiarly

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.

02-06-2009, 02:53 PM

Thyamine,

Try those queries against a real database to see your results. You'll find you don't get the result the OP wanted.

02-06-2009, 03:17 PM

Ahh, I see. I didn't see that the one ID has both a Y and a M.