TSQL - vertical Table Comparsion

Hi Experts,

I am attempting to put this Query together:
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 Where (BO.Line1 Is Not Null) And (C.Contact =
(Select Contact1 From [fncUtlFolderSingle]('BIO') BI))

Open in new window


But I get this error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So I tried:
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 Where (BO.Line1 Is Not Null) Left Join
Select Contact1 From [fncUtlFolderSingle]('BIO') BI
On C.Contact = BI.Contact1

Open in new window


But syntax errors.


What I think I want is:
Under 'Booth'
Return all records without the NULL in Line1

Under 'BIO'
Return all records

Please help and thanks.
Amour22015Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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
Where (BO.Line1 Is Not Null)
  And Exists(Select 1 From [fncUtlFolderSingle]('BIO') BI Where C.Contact = BI.Contact1)
Vitor MontalvãoMSSQL Senior EngineerCommented:
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))

Open 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)

Open in new window

Amour22015Author Commented:
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) 

Open 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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
We need to understand your data. What for is that WHERE clause? What the SELECT returns if you run it without the WHERE clause?
Amour22015Author Commented:
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) 

Open 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

Open 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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
Amour22015Author Commented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try 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 (BI.Memo1 Is Not Null)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
Ok, great thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.