Select in Select using Uniqueindentifier as the key

I have 2 tables, adxprofile is the main table, adxProfileValues is the child table.
They are joined by a field called adxProfileGuid.

I want to query send the query the userkey which is the username.
The query then in turn needs to return all the child rows with the matching uniqueindentifier

I have the following statement

select adxProfileGuid
from adxprofile
where userkey = 'john'
and adxProfileGuid =
(select PropertyName
from adxProfileValues)

When I use = I get this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

When I use IN instead of = I get this error
Syntax error converting from a character string to uniqueidentifier.
LVL 3
gspronychAsked:
Who is Participating?
 
Anthony PerkinsCommented:
This will return all the rows in adxProfileValues that match adxprofile and where userkey = "john'':

select v.*
from adxprofile p
         Inner Join adxProfileValues v On p.adxProfileGuid = v.adxProfileGuid
where p.userkey = 'john'
0
 
danrosenthalCommented:
Use IN and then use the CONVERT function on PropertyName
0
 
gspronychAuthor Commented:
I get this error
Syntax error converting from a character string to uniqueidentifier.

It seems to me that using the IN statement coverts the GUID into a string.
I have tried adding convert statements to the adxProfileGuid with the same results.
0
 
Brian CroweDatabase AdministratorCommented:
Could you please translate the sentence below...

"I want to query send the query the userkey which is the username."
0
 
gspronychAuthor Commented:
Sorry for the bad gramar

I want the first query to use the GUID field in adxProfileGuid from adxprofile
__
select adxProfileGuid
from adxprofile
where userkey = 'john'
____

And return all the matching rows from adxProfileValues.
adxProfileValues has the same adxProfileGuid field with the same GUID.

Thanks for your assistance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.