Having on 'id' column won't work?? - Dev Shed

Forums: » Register « | User CP | Games | Calendar | Members | FAQs | Sitemap | Support |

User Name:

Password:

Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
		Dev Shed Forums > Databases > MySQL Help
Having on 'id' column won't work?? Discuss Having on 'id' column won't work?? in the MySQL Help forum on Dev Shed. Having on 'id' column won't work?? MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).

Reply

Add This Thread To:

Del.icio.us

Digg

Google

Spurl

Blink

Furl

Simpy

Y! MyWeb

« Previous Thread | Next Thread »

Search this Thread

Rate Thread

Unread

Dev Shed Forums Sponsor:

#1

Old

November 27th, 2012, 02:33 PM

Registered User

Dev Shed Newbie (0 - 499 posts)

Join Date: Nov 2012

Posts: 7

Time spent in forums: 1 h 53 m 10 sec
Reputation Power: 0

Having on 'id' column won't work??

I have the following sql query which works fine:

Code:

select
	 l.title
	,l.id
from
	sales s
left outer join
	stufflinks as l
	on s.lid = l.id
having
	( CONVERT( id USING utf8mb4) LIKE '%192%' AND CONVERT( id USING utf8mb4) REGEXP '(^|[[:<:]])192([[:>:]]|$)' )

SENCO Air-Powered Finishing Gun	192
SENCO Air-Powered Finishing Gun	192
SENCO Air-Powered Finishing Gun	192

But as soon as I add the 'group by s.id' to it:

Code:

select
	 l.title
	,l.id
from
	sales s
left outer join
	stufflinks as l
	on s.lid = l.id
group by
	s.id
having
	( CONVERT( id USING utf8mb4) LIKE '%192%' AND CONVERT( id USING utf8mb4) REGEXP '(^|[[:<:]])192([[:>:]]|$)' )

0 results

the query returns 0 results.

If I keep the group by clause and change having id = 192 to having l.id = 192 then it works fine. I don't get why it isn't working with the plain 'id' column name though - the having works when I try that with ANY other column BUT the 'id' column.

Can anyone explain why??

Thanks!

#2

Old

November 27th, 2012, 05:11 PM

Lord of the Dance

Dev Shed Loyal (3000 - 3499 posts)

Join Date: Oct 2003

Posts: 3,130

Time spent in forums: 2 Months 2 Weeks 23 h 46 m 38 sec
Reputation Power: 1736

In the first query, it looks like you should place the condition with the WHERE clause instead of HAVING.
HAVING is used when you have one of the GROUP BY (Aggregate) Functions in you select. You don't use this in the query.

In you second query, if you have a group by statement, then all fields in the select, which is not part of one of those functions, should be included in the group by list.

It is not clear what you really want as result and your problem as you stated in the last post.
Why cant use use a simply where statement like:

Code:

WHERE id = 192

What happens if you try this code:

Code:

select
	 l.title
	,l.id
from
	sales s
left outer join
	stufflinks as l
	on s.lid = l.id
WHERE l.id = 192

does it return any errors? wrong result?

#3

Old

November 27th, 2012, 06:26 PM

Registered User

Dev Shed Newbie (0 - 499 posts)

Join Date: Nov 2012

Posts: 7

Time spent in forums: 1 h 53 m 10 sec
Reputation Power: 0

Hi MrFujin,

To better explain - I'm splitting a query between the 'where' and 'having' clauses to enable searching on both standard and aggregated fields with the same query.

So in my 'where' clause I'm doing a like '%X%' to build a superset of results, and then I'm using the 'having' clause to filter that superset with a regex against the returned columns. They all work ok except for having on the 'id' column - unless I explicitly change it to table.column name in the having clause.

Here's a generated set to show you what I mean. From what you've said I should be including - 'items.id' and 'items.title' in the group by clause (or just 'id', 'title'?) ?

Do you know why this one doesn't return any results??

EDIT: If you change 'id' to 'items.id' in the having clause... it works. Why?

Thanks!

Code:

select
	 items.id
	,items.title
	,count(comments.id) as cmtcnt
from
	(
		select '1' as id, '192' as lid
		union
		select '2' as id, '192' as lid
		union
		select '3' as id, '192' as lid
	)
	as sales
left outer join
	(
		select '192' as id, 'test 1' as title
	)
	as items
	on sales.lid = items.id
left outer join
	(
		select '1' as id, '192' as lid
		union
		select '2' as id, '192' as lid
	)
	as comments
	on items.id = comments.lid
where
	(
		CONVERT( items.id USING utf8mb4) LIKE '%192%'
		OR
		CONVERT( items.title USING utf8mb4) LIKE '%192%'
	)
group by
	 sales.id
having
	(
		( CONVERT( id USING utf8mb4) REGEXP '(^|[[:<:]])192([[:>:]]|$)' )
		OR
		( CONVERT( title USING utf8mb4) REGEXP '(^|[[:<:]])192([[:>:]]|$)' )	
		OR
		( CONVERT( cmtcnt USING utf8mb4) REGEXP '(^|[[:<:]])192([[:>:]]|$)' )	
	)

#4

Old

November 28th, 2012, 04:01 AM

Lord of the Dance

Dev Shed Loyal (3000 - 3499 posts)

Join Date: Oct 2003

Posts: 3,130

Time spent in forums: 2 Months 2 Weeks 23 h 46 m 38 sec
Reputation Power: 1736

You have multiple "table" select containing the id column, you have to specify which of tables you want to get the id from.

For curiosity, you are saying that you don't get the correct result if you remove the line with the id condition in the HAVAING clause? So it is like this:

Code:

having
	(
		( CONVERT( title USING utf8mb4) REGEXP '(^|[[:<:]])192([[:>:]]|$)' )	
		OR
		( CONVERT( cmtcnt USING utf8mb4) REGEXP '(^|[[:<:]])192([[:>:]]|$)' )	
	)

Have you tried this?

#5

Old

November 28th, 2012, 04:11 AM

Registered User

Dev Shed Newbie (0 - 499 posts)

Join Date: Nov 2012

Posts: 7

Time spent in forums: 1 h 53 m 10 sec
Reputation Power: 0

Quote:

Originally Posted by MrFujin

Have you tried this?

Removing id from the having clause returns no results, because it then only returns results which have title or cmtcnt = 192 and there aren't any.

If you run that code 'as-is' I'm getting no results, but if I just change the 'id' in the having clause to 'items.id' then it works fine. But I don't understand why...

Reply

Viewing: Dev Shed Forums > Databases > MySQL Help > Having on 'id' column won't work??

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Forums: » Register « | User CP | Games | Calendar | Members | FAQs | Sitemap | Support |

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap