New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
Web Design and Hosting Creating sites, scripting, and hosting discussions.

SQL: HAVING question


Post Reply New Thread Subscribe

 
Thread Tools
MattSharp

 
Member Since: Oct 24, 2006
Posts: 95
MattSharp is an unknown at this point

MattSharp is offline
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?
QUOTE Thanks
xstep

 
xstep's Avatar
 
Member Since: Jun 25, 2005
Location: On the road
Posts: 3,231
xstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to all
Mac Specs: 2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid

xstep is offline
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.
QUOTE Thanks
centered effect

 
Member Since: Dec 31, 2005
Posts: 24
centered effect is on a distinguished road

centered effect is offline
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
QUOTE Thanks
xstep

 
xstep's Avatar
 
Member Since: Jun 25, 2005
Location: On the road
Posts: 3,231
xstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to all
Mac Specs: 2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid

xstep is offline
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.
QUOTE Thanks
centered effect

 
Member Since: Dec 31, 2005
Posts: 24
centered effect is on a distinguished road

centered effect is offline
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.

PowerBook G4 15" 1.5Ghz | 2GB PC2700 | 80GB HDD | OSX 10.4.11
QUOTE Thanks
Thyamine

 
Thyamine's Avatar
 
Member Since: Apr 23, 2007
Location: Coatesville, PA
Posts: 377
Thyamine will become famous soon enough
Mac Specs: MBP 15", 2.33 GHz, 2Gb

Thyamine is offline
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.
QUOTE Thanks
xstep

 
xstep's Avatar
 
Member Since: Jun 25, 2005
Location: On the road
Posts: 3,231
xstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to allxstep is a name known to all
Mac Specs: 2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid

xstep is offline
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.
QUOTE Thanks
Thyamine

 
Thyamine's Avatar
 
Member Since: Apr 23, 2007
Location: Coatesville, PA
Posts: 377
Thyamine will become famous soon enough
Mac Specs: MBP 15", 2.33 GHz, 2Gb

Thyamine is offline
Ahh, I see. I didn't see that the one ID has both a Y and a M.
QUOTE Thanks

Post Reply New Thread Subscribe


« Saving iWeb sites | How to add contact form on iweb? »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

Similar Threads
Thread
Thread Starter
Forum
Replies
Last Post
The QUESTION Game ToddG Schweb's Lounge 159 08-15-2007 05:25 PM
Boot-Up Question Mark Folder Impending Doom Question. bingwalker OS X - Operating System 3 07-10-2007 05:19 AM
Ask 'me' a question... eric Schweb's Lounge 22 01-12-2007 06:33 AM
Video production question macinsandiego Movies and Video 4 11-17-2004 07:39 AM
Postscript question battleangel444 OS X - Operating System 2 07-09-2004 12:45 PM

All times are GMT -4. The time now is 09:09 AM.

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

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?