#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep 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. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    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?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep 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([[:>:]]|$)' )	
    	)
  6. #4
  7. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,663
    Rep Power
    1945
    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?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep Power
    0
    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...

IMN logo majestic logo threadwatch logo seochat tools logo