Alias Exists Subquery

  • How do i alias a Exists subquery?

    select TOP 1000

    E.[Last Name]

    , E.[First Name]

    , E.[Department]

    , Support.[SupportLvl]

    FROM Employee E

    Where exists (select * from Support

    where S.[MID]=E.[MID] )

    and E.[MID] = '12345'

    Msg 4104, Level 16, State 1, Line 4

    The multi-part identifier "Support.SupportLvl" could not be bound.

    How do i bind it?

    I try to give the Exists query an alias, but i then get syntax error:

    select TOP 1000

    E.[Last Name]

    , E.[First Name]

    , E.[Department]

    , W.SupportLvl]

    FROM Employee E

    Where exists (select * from Support S

    where S.[MID]=E.[MID] ) W

    and E.[MID] = '12345'

    Msg 102, Level 15, State 1, Line 15

    Incorrect syntax near 'W'.

  • Hollyz (5/14/2012)


    How do i alias a Exists subquery?

    select TOP 1000

    E.[Last Name]

    , E.[First Name]

    , E.[Department]

    , Support.[SupportLvl]

    FROM Employee E

    Where exists (select * from Support

    where S.[MID]=E.[MID] )

    and E.[MID] = '12345'

    Msg 4104, Level 16, State 1, Line 4

    The multi-part identifier "Support.SupportLvl" could not be bound.

    How do i bind it?

    I try to give the Exists query an alias, but i then get syntax error:

    select TOP 1000

    E.[Last Name]

    , E.[First Name]

    , E.[Department]

    , W.SupportLvl]

    FROM Employee E

    Where exists (select * from Support S

    where S.[MID]=E.[MID] ) W

    and E.[MID] = '12345'

    Msg 102, Level 15, State 1, Line 15

    Incorrect syntax near 'W'.

    That is not at all how you would do that. You don't have Support in your query. I think you are not understanding how EXISTS works. In your case you want an inner join like this.

    select TOP 1000

    E.[Last Name]

    , E.[First Name]

    , E.[Department]

    , Support.[SupportLvl]

    FROM Employee E

    join Support s on S.[MID]=E.[MID]

    and E.[MID] = '12345'

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Just to clarify, here is a quote from BOL (emphasis added).

    Subqueries with EXISTS


    When a subquery is introduced with the keyword EXISTS, the subquery functions as an existence test. The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

    In other words, the EXISTS statement essentially ignores the SELECT portion of the subquery. There is no data returned, so there are no columns to include, and no point in aliasing the subquery, because you cannot refer to anything from the subquery in the main query.

    If you need to reference columns from your subquery in your main query, you are going to need to use a JOIN, as Sean already mentioned, or an APPLY. In this particular case, a JOIN is most likely to perform better.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thanks for the answers. I was trying to get around by not using the Distinct in the select statement. That is the reason i used the Where exist which would result in much faster performance.

    The join statement would not yield the same result as I am expecting.

  • Give this a try.

    SELECT TOP 1000

    E.[Last Name]

    , E.[First Name]

    , E.[Department]

    , Support.[SupportLvl]

    FROM

    dbo.Employee E

    cross apply (select top 1 S.SupportLvl from dbo.Support S where S.MID = E.MID) Support

    WHERE

    E.[MID] = '12345'

  • CELKO (5/15/2012)


    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    >> How do I alias an EXISTS subquery? <<

    There is no such thing as a an EXISTS subquery; it is a predicate. Why did you think that TOP without an ORDER BY makes an sense? Why do you violate ISO-11179 rules? Embedded blanks? “mid” as a data element name? Do you really have only one employee like you said? In a set-oriented language, we would deal with a set (Personnel) and not a single employee.

    Here is a guess at what you might have meant:

    SELECT P.last_name, P.first_name, P.department_nbr,

    S.support_lvl

    FROM Personnel AS P

    LEFT OUTER JOIN

    Support AS S

    ON S.m_id = P.m_id

    AND P.m_id = '12345';

    Yet more derogatory comments from you without even trying to help the poster. While I agree with your comments about table and column names as well as top without an order by, your attitude and content do not provide anything. You discuss ISO standards and do not provide a link to them. You criticize the way people think and don't offer a suggestion about how to improve.

    The idea of these forums is to help people with problems and to learn more about sql. You just continue to berate people and sound like a big pompous doofus. Yes we know you are smart, and if we forget you will remind us quickly that you are a member of Mensa. Big deal.

    Please change your attitude or stop posting useless diatribe about ISO this and ISO that and how inferior others are to your superior intellect. It is really tiring.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • CELKO (5/15/2012)


    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    >> How do I alias an EXISTS subquery? <<

    There is no such thing as a an EXISTS subquery; it is a predicate. Why did you think that TOP without an ORDER BY makes an sense? Why do you violate ISO-11179 rules? Embedded blanks? “mid” as a data element name? Do you really have only one employee like you said? In a set-oriented language, we would deal with a set (Personnel) and not a single employee.

    Here is a guess at what you might have meant:

    SELECT P.last_name, P.first_name, P.department_nbr,

    S.support_lvl

    FROM Personnel AS P

    LEFT OUTER JOIN

    Support AS S

    ON S.m_id = P.m_id

    AND P.m_id = '12345';

    I'll agree with the TOP 1, as that was my codeI provided to the OP, but not knowing anything about the tables it was simply something to try and see if it at least provided an answer closer to what the OP is trying to achieve.

    If you aren't going to actually try and help, please, just go away.

  • Why do you violate ISO-11179 rules?

    Hehehe... Because nobody learns them or cares to. I'll tell you a question I have never been asked at an interview... "Are you familiar with all of the ISO standards for coding and do you follow them?"

    Jared
    CE - Microsoft

  • SQLKnowItAll (5/15/2012)


    Why do you violate ISO-11179 rules?

    Hehehe... Because nobody learns them or cares to. I'll tell you a question I have never been asked at an interview... "Are you familiar with all of the ISO standards for coding and do you follow them?"

    Now that is a great question for the next interview I conduct. Of course I would no idea if they were telling the truth or not. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (5/15/2012)


    SQLKnowItAll (5/15/2012)


    Why do you violate ISO-11179 rules?

    Hehehe... Because nobody learns them or cares to. I'll tell you a question I have never been asked at an interview... "Are you familiar with all of the ISO standards for coding and do you follow them?"

    Now that is a great question for the next interview I conduct. Of course I would no idea if they were telling the truth or not. 😀

    Nor would it benefit you or your organization if you did... :w00t:

    Jared
    CE - Microsoft

  • CELKO (5/15/2012)


    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

    >> How do I alias an EXISTS subquery? <<

    There is no such thing as a an EXISTS subquery; it is a predicate. Why did you think that TOP without an ORDER BY makes an sense? Why do you violate ISO-11179 rules? Embedded blanks? “mid” as a data element name? Do you really have only one employee like you said? In a set-oriented language, we would deal with a set (Personnel) and not a single employee.

    Here is a guess at what you might have meant:

    SELECT P.last_name, P.first_name, P.department_nbr,

    S.support_lvl

    FROM Personnel AS P

    LEFT OUTER JOIN

    Support AS S

    ON S.m_id = P.m_id

    AND P.m_id = '12345';

    Sir,

    I believe that your posts are informative but it does beat the hell out of me if I think about asking you a question because I am scared of one things:

    I am just 5 years experienced guy and when I see your display image, I feel like a kitten.:-D

    I request you to soften a bit.

    Thanks

    Chandan

  • CELKO (5/15/2012)


    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is a Microsoft SQL Server forum.... local dialect pertaining to Microsoft SQL Server is fully justified. If you want Oracle help, try an Oracle forum, same for MySQL, DB2, PostgresSQL, etc.

    As most folks asking for help probably already have a created table, they're most likely to be using the create script for said table. Not following ISO-11179

    ISO-8601 breaks SQL when you try to stick it into a datetime column.

    DECLARE @dt DATETIME = '2012-05-16T18:11Z'

    DECLARE @dt DATETIME = 2012-05-16T18:11Z

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '11'.

    Please leave you aren't helping.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • CELKO (5/17/2012)


    >> ISO-8601 breaks SQL when you try to stick it into a datetime column. <<

    We picked one of ISO-8601 formats for SQL. This makes porting code much easier if all the strings come out one and only one way.

    We do not use the "T" in a date-time string. Or the week-within-year format. You have to write your own functions or calendar tables.

    Really? Here is the excerpt from SQL Servers BOL:

    - 126 (4) ISO8601 yyyy-mm-ddThh:mm:ss.mmm (no spaces)

    127(6) ISO8601 with time zone Z. yyyy-mm-ddThh:mm:ss.mmmZ (no spaces)

    Looks to me that ISO-8601 does use the "T" in a date-time string, or is this just MS version of ISO-8601?

  • mtassin (5/16/2012)


    CELKO (5/15/2012)


    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is a Microsoft SQL Server forum.... local dialect pertaining to Microsoft SQL Server is fully justified. If you want Oracle help, try an Oracle forum, same for MySQL, DB2, PostgresSQL, etc.

    As most folks asking for help probably already have a created table, they're most likely to be using the create script for said table. Not following ISO-11179

    ISO-8601 breaks SQL when you try to stick it into a datetime column.

    DECLARE @dt DATETIME = '2012-05-16T18:11Z'

    DECLARE @dt DATETIME = 2012-05-16T18:11Z

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '11'.

    Please leave you aren't helping.

    To convert a string version of an IS-8601 date string, two things need to be true. One, you have to have the complete date-time string (tried it with your shorter version, didn't work). Two, you need to use the CONVERT function with the appropriate date format code.

    DECLARE @dt1 DATETIME;

    SET @dt1 = CONVERT(DATETIME, '2012-05-16T18:11:00.000Z', 127);

    SELECT @dt1;

  • Lynn Pettis (5/17/2012)


    To convert a string version of an IS-8601 date string, two things need to be true. One, you have to have the complete date-time string (tried it with your shorter version, didn't work). Two, you need to use the CONVERT function with the appropriate date format code.

    DECLARE @dt1 DATETIME;

    SET @dt1 = CONVERT(DATETIME, '2012-05-16T18:11:00.000Z', 127);

    SELECT @dt1;

    Thanks Lynn, I was just using the first format I saw on wikipedia that included both dates and times fro ISO 8601.

    I still stand by the statement that throwing ISO standards at the average person begging for help here is way overboard.

    What they typically see when looking at Microsoft SQL Datetimes is "YYYY-MM-DD HH:MM:SS.nnn" Providing date time values in this format seems to be the simplist approach. And what I'd hope they'd give us. 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply