TSQL - vertical Table Comparsion

As the error messages says, when you use '=' operator it can compare only with one value, meaning that your select is returning more than one row, so or you use Scott's solution or you change the '=' operator to 'IN' operator to compare with a list of values:

(C.Contact IN (Select Contact1 From [fncUtlFolderSingle]('BIO') BI))

Select allOpen in new window

About your second try I think you should use the function as you used for BOOTH:

Select Contact,
BO.Affiliation,
BO.Line1,
BO.EventID,
Bo.Contact2,
BO.Memo1
From tblNAsContacts C 
Left Join [fncUtlFolderSingle]('BOOTH') BO On C.Contact = BO.Contact1 
Left Join [fncUtlFolderSingle]('BIO') BI On C.Contact = BI.Contact1 
Where (BO.Line1 Is Not Null)

Select allOpen in new window

So on this:

Select Contact,
BO.Affiliation,
BO.Line1,
BO.EventID,
Bo.Contact2,
BI.Memo1
From tblNAsContacts C 
Left Join [fncUtlFolderSingle]('BOOTH') BO On C.Contact = BO.Contact1 
Left Join [fncUtlFolderSingle]('BIO') BI On C.Contact = BI.Contact1 
Where (BO.Line1 Is Not Null) 

Select allOpen in new window

I only get records from:
[fncUtlFolderSingle]('BOOTH')
But it does not look like I am getting any records from:
[fncUtlFolderSingle]('BIO')

Note that I had to change:
BO.Memo1
To:
BI.Memo1

I need all the Memo1 from:
[fncUtlFolderSingle]('BIO')

Please help and thanks.

We need to understand your data. What for is that WHERE clause? What the SELECT returns if you run it without the WHERE clause?

Ok,

This part:

Select Contact,
BO.Affiliation,
BO.Line1,
BO.EventID,
Bo.Contact2,
From tblNAsContacts C 
Left Join [fncUtlFolderSingle]('BOOTH') BO On C.Contact = BO.Contact1 
Where (BO.Line1 Is Not Null) 

Select allOpen in new window

Returns all the Booth records without Null for Line1.
And yes I need them all.
This part:

Select BI.Memo1
From tblNAsContacts C 
Left Join [fncUtlFolderSingle]('BIO') BI On C.Contact = BI.Contact1
Where Memo1 Is Not Null  --I need to also add this part in

Select allOpen in new window

Would give me all the records from BIO where Memo1 is not null.
And yes I need all them also.

Please help and thanks

Sample data can make miracles. If you try to explain with sample data it will be more easy for you and us.

Like, why in your main question you only had BO.Memo1 and now you changed it to BI.Memo1? Shouldn't you have both?
Do you really need the LEFT JOINs? Since they may creating the NULL values that you need to exclude in the WHERE clause, you can replace those LEFT JOINs with INNER JOINs and get rid of the WHERE clause.

Ok I will try the sample data:

Here is for matching with BIO:
Memo1
About Severn Trent Services

And that is only one record/Column.  There is a lot of data with about 111 records that show up.

Here is match for Booth:
Contact      Affiliation      Line1      EventID      Contact2      Memo1
130044      BOOTH      GE Water & Process Technologies      3      NULL      NULL
132871      BOOTH      ArcelorMittal/Industeel      3      NULL      NULL

That is for one 2 records, there is about 11 records that show.

Q.)Like, why in your main question you only had BO.Memo1 and now you changed it to BI.Memo1?
A.) My mistake, the Memo1 data that is needed comes from BI not BO.

So in the end I will need All Booth: (11 records) And All BIO (111 records)


Please help and thanks.

Ok, great thanks