Mac Forums

Mac Forums (http://www.mac-forums.com/forums/)
-   Web Design and Hosting (http://www.mac-forums.com/forums/web-design-hosting/)
-   -   SQL: HAVING question (http://www.mac-forums.com/forums/web-design-hosting/134610-sql-having-question.html)

MattSharp 12-24-2008 02:09 PM

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?

xstep 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.

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.

centered effect 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:
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


xstep 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.

centered effect 02-03-2009 06:45 AM

Thanks xstep. I read the first sentence and the second too quickly

Quote:

I want to end up getting just 'Y' but not 'M'. So my query should return Bill and Steve.

Thyamine 02-06-2009 01:38 PM

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.

xstep 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.

Thyamine 02-06-2009 03:17 PM

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


All times are GMT -4. The time now is 02:14 PM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.