Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subquery in where clause returning more than 1 row

Status
Not open for further replies.

dand11

Programmer
Jun 24, 2008
63
US
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".

Code:
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)
 
Oh, I forgot to mention that the "LINK" field is unique.
 
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
 
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

 
There is no need of all SUBSTR() function also:
Code:
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:
Code:
Select 0, m.LINK
       from MNAMES m
Where (@FNAME = '-1' OR
       LTRIM(FULLNAME) LIKE @FNAME+'%')


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top