Select in Select using Uniqueindentifier as the key

Posted on 2006-04-20
Last Modified: 2008-01-09
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.
Question by:gspronych
    LVL 15

    Expert Comment

    Use IN and then use the CONVERT function on PropertyName
    LVL 3

    Author Comment

    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.
    LVL 34

    Expert Comment

    by:Brian Crowe
    Could you please translate the sentence below...

    "I want to query send the query the userkey which is the username."
    LVL 3

    Author Comment

    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
    LVL 75

    Accepted Solution

    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'

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now