SELECT Query Not Working

#1

I have the following SELECT statement, which works:

SELECT FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ', first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE ‘%SMITH%’ AND (GENDER=‘M’ OR GENDER IS NULL) ) AND ( DOB_SQL=‘1959-05-16’ AND FIRST_C=‘T’ )

However, when I modify it as follows:

SELECT FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ', first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE ‘%SMITH%’ AND (GENDER=‘M’ OR GENDER IS NULL) ) AND ( DOB_SQL=‘1959-05-16’ AND FIRST_C=‘T’ AND MIDDLE_C=‘A’)

it yields no results. I know there is a record that matches the criteria specified. What am I doing wrong?

If I list out the field values, I see the matching value in the MIDDLE_C named field.

Thanks.

#2

MIDDLE_C only exists in the select part of the query and not in the where part of the query.

SELECT
FIRST_C,
FIRST_MIDDLE_NAME,
FULL_NAME,
DOB_SQL,
if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ', first_middle_name)+1, 1)) as MIDDLE_C
FROM
offenders
WHERE
(
LAST_NAME LIKE ‘%SMITH%’
AND
(GENDER=‘M’ OR GENDER IS NULL)
)
AND
(
DOB_SQL=‘1959-05-16’
AND
FIRST_C=‘T’
AND
MIDDLE_C=‘A’
)

You would need to do:

SELECT
	FIRST_C,
	FIRST_MIDDLE_NAME,
	FULL_NAME,
	DOB_SQL,
	if(LOCATE(' ', first_middle_name)=0, '', substr(first_middle_name, LOCATE(' ', first_middle_name)+1, 1)) as MIDDLE_C
FROM
	offenders
WHERE
	(
			LAST_NAME LIKE '%SMITH%'
		AND
			(GENDER='M' OR GENDER IS NULL) 
	)
AND
	(
			DOB_SQL='1959-05-16'
		AND
			FIRST_C='T'
		AND
			if(LOCATE(' ', first_middle_name)=0, '', substr(first_middle_name, LOCATE(' ', first_middle_name)+1, 1))='A'
	)

or possibly:

SELECT
	FIRST_C,
	FIRST_MIDDLE_NAME,
	FULL_NAME,
	DOB_SQL,
	if(LOCATE(' ', first_middle_name)=0, '', substr(first_middle_name, LOCATE(' ', first_middle_name)+1, 1)) as MIDDLE_C
FROM
	offenders
WHERE
	(
			LAST_NAME LIKE '%SMITH%'
		AND
			(GENDER='M' OR GENDER IS NULL) 
	)
AND
	(
			DOB_SQL='1959-05-16'
		AND
			FIRST_C='T'
		AND
			first_middle_name LIKE 'A%'
	)
#3

I changed the MIDDLE_C from being referenced in the WHERE clause to being referenced in a HAVING clause and now the SELECT query works. However, now I am having another problem. I now have a compound HAVING clause, basically my SELECT statement is now:

SELECT * FROM offenders WHERE ‘some criteria’ HAVING ( (‘criteria a’) OR (‘criteria b’) )

WIthout ‘criteria b’ I get 2 records in my result set. If I add in ‘criteria b’ I now get 0 records – but it’s part of an OR condition. That doesn’t make any sense.

#4

Can you post the query?

#5

SELECT SOR_ID, FIRST_C, FIRST_MIDDLE_NAME, FULL_NAME, DOB_SQL, if(LOCATE(’ ‘, first_middle_name)=0, first_middle_name, substr(first_middle_name, 1, LOCATE(’ ‘, first_middle_name))) as FIRST_NAME, if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ‘, first_middle_name)+1)) as MIDDLE_NAME, if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ‘, first_middle_name)+1, 1)) as MIDDLE_C FROM offenders WHERE (LAST_NAME LIKE ‘%SMITH%’ AND (GENDER=‘M’ OR GENDER IS NULL) ) HAVING (( DOB_SQL=‘1959-05-16’ AND FIRST_C=‘T’ AND MIDDLE_C=‘A’ AND MIDDLE_C<>’’ ) OR ( (DOB_YEAR=‘1959’ AND FIRST_NAME=‘TIMOTHY’ AND MIDDLE_NAME=‘ALAN’ ))

#6

From the SQL you posted you’re missing a ) off the end. Also MIDDLE_C=‘A’ AND MIDDLE_C<>’’ you don’t need the AND MIDDLE_C<>’’ since MIDDLE_C has to be A.

Other than that I can’t see anything that would cause an error. It might be worth trying to simplify your SQL though.

#7

Actually, this SQL statement is going to get much more complicated, not simpler, but I need to build it up and test it piece by piece. I corrected the unbalanced parentheses, but I am still not getting any results when I add in the DOB_SQL criteria. I’m thinking of changing this to a UNION instead of the OR. Thoughts?

#8

Are you running the querys straight from PHP? if so try adding or die(mysql_error());

[php]$query = mysql_query($sql) or die(mysql_error());[/php]
That way you can be sure there is no errors.

Do you really need to do:
if(LOCATE(’ ‘, first_middle_name)=0, first_middle_name, substr(first_middle_name, 1, LOCATE(’ ‘, first_middle_name))) as FIRST_NAME,
if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ‘, first_middle_name)+1)) as MIDDLE_NAME,
if(LOCATE(’ ‘, first_middle_name)=0, ‘’, substr(first_middle_name, LOCATE(’ ', first_middle_name)+1, 1)) as MIDDLE_C

Doing that on every column will slow the query down. If you made a FirstName and MiddleName column then it would avoid the need for all of that and mean you could put the criteria into the where clause.

All of that would simplify your query a lot. Then when you need to make it more complicated it will be far easier to do and understand.

Sponsor our Newsletter | Privacy Policy | Terms of Service