Subquery in where clause returning more than 1 row

[dand11]

My subquery in the where clause is returning more than one record. I'm trying to parse out the first name out of a field that has both the first and last name in the field called "FULLNAME".

Select 0, m.LINK 
			from MNAMES m
			Left Join GEOBASE g on g.LINK = m.LINK
			Left Join PHONES p on m.LINK = p.LINK 
			Left Join Vehicles v on m.LINK = v.OWNER
Where (@FNAME = '-1' OR (Select LTRIM(RTRIM(UPPER(SUBSTRING(FULLNAME,1,LENGTH(@FNAME))))) from mnames where LINK = m.LINK) LIKE @FNAME)

 

[dand11]

Oh, I forgot to mention that the "LINK" field is unique.

 

[SQLSister]

Don't use subqueries. Use a join instead. it will be faster and will not have the more than one record returned problem.

"NOTHING is more important in a database than integrity." ESquared

 

[memdiscount]

Hi;

You don't have to use sub query here. You can use like this:

Select 0, m.LINK
from MNAMES m
Left Join GEOBASE g on g.LINK = m.LINK
Left Join PHONES p on m.LINK = p.LINK
Left Join Vehicles v on m.LINK = v.OWNER
Where @FNAME = '-1'
OR
LTRIM(RTRIM(UPPER(SUBSTRING(FULLNAME,1,LENGTH(@FNAME))))) LIKE @FNAME

You might need to use matching criteria in LIKE e.g. '%' or something.

Thanks

 

[bborissov]

There is no need of all SUBSTR() function also:

Select 0, m.LINK
            from MNAMES m
            Left Join GEOBASE g on g.LINK = m.LINK
            Left Join PHONES p on m.LINK = p.LINK
            Left Join Vehicles v on m.LINK = v.OWNER
Where (@FNAME = '-1' OR
       LTRIM(FULLNAME) LIKE @FNAME+'%')

I don't see any reasons for JOINs also, you didn't select any fields from these tables and they didn't get involved in WHERE clause also. SO I think this is enough:

Select 0, m.LINK
       from MNAMES m
Where (@FNAME = '-1' OR
       LTRIM(FULLNAME) LIKE @FNAME+'%')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.