Subquery returned more than 1 value 3

[mmetze]

I am trying to select values from one table which meet the criteria of a range of values found in another table. Perhaps displaying the SQL would provide a better illustration...

Below is the SQL statement that I currently have that returns an error: &quot;Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.&quot;

select * from History where Upper(Filename) like
(select FileNameMask from FileInfo where EmailAddress = 'jdoe@aol.com')

In the above SQL statement, FileNameMask contains a wild-card string, such as &quot;%.MAK.%&quot;. The problem is in handling the sub-select which returns multiple rows.


Any help getting this to work would be greatly appreciated!!!

Mark

 

[balves]

If you're running at least V7...

select * from History where Upper(Filename) like
(select TOP 1 FileNameMask from FileInfo where EmailAddress =

 

[michaelkrauklis]

try this:
select * from history inner join (select FileNameMask from FileInfo where EmailAddress='jdoe@aol.com') fnm on Upper(History.Filename) like fnm.FileNameMask

I'm a little rusty on my sql but I think this should work. As a rule whenever you're trying to compare more than one value you should use a join instead of a where. Hope this helps. MYenigmaSELF
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com

 

[mmetze]

Thanks for the help! I never thought about joining the 2 tables with a LIKE condition, as I have always joined where a primary key of one table equalled a foreign key of another table. Does joining tables using a LIKE have performance implications?

 

[tlbroadbent]

Generally, use of the Like predicate will prevent the use of indexes so performance will be impaired. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[michaelkrauklis]

Whenever you use 'like' instead of '=' it degrades performance. Seeing how you had used it in your original example I didn't think that was a big issue. Good luck. MYenigmaSELF
myenigmaself@yahoo.com

http://myenigmaself.gaiden.com