May 14, 2012 at 9:28 am
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'.
May 14, 2012 at 9:31 am
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/
May 14, 2012 at 10:01 am
Just to clarify, here is a quote from BOL (emphasis added).
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
May 15, 2012 at 9:25 am
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.
May 15, 2012 at 9:32 am
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'
May 15, 2012 at 1:19 pm
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/
May 15, 2012 at 1:48 pm
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.
May 15, 2012 at 1:59 pm
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
May 15, 2012 at 2:02 pm
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/
May 15, 2012 at 2:03 pm
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
May 16, 2012 at 5:41 am
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
May 16, 2012 at 2:38 pm
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.
May 17, 2012 at 8:51 am
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?
May 17, 2012 at 8:59 am
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;
May 17, 2012 at 9:20 am
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. 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply