WHERE IN (SELECT) with multiple columns

A continuing annoyance is that I can do stuff like this in SQL Server:

SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y)

This is getting more complex if any of A or B can be NULL. Is there an 
alternative in SQLServer 2005+ to implement such predicates?

Pete 


0
Peter
11/13/2009 3:35:55 AM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
832 Views

Similar Articles

[PageSpeed] 31

Select * from dbo.Employee e where exists ( select null from 
dbo.SomeOtherTable sot where sot.StateID = e.StateID and sot.EmployeeID = 
e.EmployeeID )

You can start there and then experiment.

Throw in a " or e.StateID IS NULL "... or something like that.

You should post some DDL , some INSERTS and then desired results 
..................


"Peter" <peteATkapiti.co.nz> wrote in message 
news:eJSgqJBZKHA.1336@TK2MSFTNGP06.phx.gbl...
>A continuing annoyance is that I can do stuff like this in SQL Server:
>
> SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y)
>
> This is getting more complex if any of A or B can be NULL. Is there an 
> alternative in SQLServer 2005+ to implement such predicates?
>
> Pete
> 


0
sloan
11/13/2009 3:49:41 AM
You have discovered Standard SQL syntax, but missed the row
constructor notation:

 SELECT * FROM X WHERE (a,b)  IN (SELECT a, b FROM Y);

SQL Server is behind other products. You wind up using

 SELECT *
  FROM X
 WHERE EXISTS
              (SELECT *
                  FROM Y
                WHERE X.a = Y.a
                  AND X.b = Y.b);
0
CELKO
11/13/2009 5:17:33 PM
Yes you're right, I (accidentially) did missed the row constructor syntax in 
my original post.

Still, this "Standard SQL Syntax" works fine in Oracle and MySQL but NOT SQL 
Server 2005. The SELECT in the following SQL will give you a syntax error.

CREATE TABLE A (x int, y int )
CREATE TABLE B (x int, y int )
INSERT INTO A (x,y) VALUES (1,1)
INSERT INTO A (x,y) VALUES (1,3)
INSERT INTO A (x,y) VALUES (2,1)
INSERT INTO A (x,y) VALUES (2,3)
INSERT INTO B (x,y) VALUES (1,1)
INSERT INTO B (x,y) VALUES (1,2)
INSERT INTO B (x,y) VALUES (1,3)
INSERT INTO B (x,y) VALUES (2,1)
INSERT INTO B (x,y) VALUES (2,2)
INSERT INTO B (x,y) VALUES (2,3)
SELECT * FROM B WHERE (x,y) IN (SELECT x,y FROM A)
DROP TABLE B
DROP TABLE A


"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:a96e328b-ed78-4650-8f61-f246e5624e45@o10g2000yqa.googlegroups.com...
> You have discovered Standard SQL syntax, but missed the row
> constructor notation:
>
> SELECT * FROM X WHERE (a,b)  IN (SELECT a, b FROM Y);
>
> SQL Server is behind other products. You wind up using
>
> SELECT *
>  FROM X
> WHERE EXISTS
>              (SELECT *
>                  FROM Y
>                WHERE X.a = Y.a
>                  AND X.b = Y.b); 


0
Peter
11/13/2009 9:13:23 PM
Yes, vector expressions in predicates are still not supported in SQL Server. The current method using the EXISTS 
predicate can be very verbose especially when NULLs are involved. Hope we see something in the next versions.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
11/13/2009 9:57:53 PM
>> Hope we see something in the next versions.  <<

Me, too! And I hope to help with it.
0
CELKO
11/14/2009 2:38:18 AM
SELECT * FROM X WHERE A+B IN (SELECT A+B FROM Y)



Peter wrote:

WHERE IN (SELECT) with multiple columns
12-Nov-09

A continuing annoyance is that I can do stuff like this in SQL Server:

SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y)

This is getting more complex if any of A or B can be NULL. Is there an
alternative in SQLServer 2005+ to implement such predicates?

Pete

Previous Posts In This Thread:

On Thursday, November 12, 2009 10:35 PM
Peter wrote:

WHERE IN (SELECT) with multiple columns
A continuing annoyance is that I can do stuff like this in SQL Server:

SELECT * FROM X WHERE A,B IN (SELECT A,B FROM Y)

This is getting more complex if any of A or B can be NULL. Is there an
alternative in SQLServer 2005+ to implement such predicates?

Pete

On Thursday, November 12, 2009 10:49 PM
sloan wrote:

Select * from dbo.Employee e where exists ( select null fromdbo.
Select * from dbo.Employee e where exists ( select null from
dbo.SomeOtherTable sot where sot.StateID = e.StateID and sot.EmployeeID =
e.EmployeeID )

You can start there and then experiment.

Throw in a " or e.StateID IS NULL "... or something like that.

You should post some DDL , some INSERTS and then desired results
..................


"Peter" <peteATkapiti.co.nz> wrote in message

On Friday, November 13, 2009 4:13 PM
Peter wrote:

Yes you are right, I (accidentially) did missed the row constructor syntax
Yes you are right, I (accidentially) did missed the row constructor syntax in
my original post.

Still, this "Standard SQL Syntax" works fine in Oracle and MySQL but NOT SQL
Server 2005. The SELECT in the following SQL will give you a syntax error.

CREATE TABLE A (x int, y int )
CREATE TABLE B (x int, y int )
INSERT INTO A (x,y) VALUES (1,1)
INSERT INTO A (x,y) VALUES (1,3)
INSERT INTO A (x,y) VALUES (2,1)
INSERT INTO A (x,y) VALUES (2,3)
INSERT INTO B (x,y) VALUES (1,1)
INSERT INTO B (x,y) VALUES (1,2)
INSERT INTO B (x,y) VALUES (1,3)
INSERT INTO B (x,y) VALUES (2,1)
INSERT INTO B (x,y) VALUES (2,2)
INSERT INTO B (x,y) VALUES (2,3)
SELECT * FROM B WHERE (x,y) IN (SELECT x,y FROM A)
DROP TABLE B
DROP TABLE A

On Friday, November 13, 2009 4:30 PM
--CELKO-- wrote:

You have discovered Standard SQL syntax, but missed the rowconstructor
You have discovered Standard SQL syntax, but missed the row
constructor notation:

SELECT * FROM X WHERE (a,b)  IN (SELECT a, b FROM Y);

SQL Server is behind other products. You wind up using

SELECT *
FROM X
WHERE EXISTS
(SELECT *
FROM Y
WHERE X.a = Y.a
AND X.b = Y.b);

On Friday, November 13, 2009 4:57 PM
Plamen Ratchev wrote:

Yes, vector expressions in predicates are still not supported in SQL Server.
Yes, vector expressions in predicates are still not supported in SQL Server. The current method using the EXISTS
predicate can be very verbose especially when NULLs are involved. Hope we see something in the next versions.

--
Plamen Ratchev
http://www.SQLStudio.com

On Friday, November 13, 2009 10:41 PM
--CELKO-- wrote:

Me, too! And I hope to help with it.
Me, too! And I hope to help with it.


Submitted via EggHeadCafe - Software Developer Portal of Choice 
ASP.NET GridView: Select Row and Display Item Detail
http://www.eggheadcafe.com/tutorials/aspnet/ff14a008-2af9-4f9d-a09d-1af670466a80/aspnet-gridview-select-row-and-display-item-detail.aspx
0
Abu
6/21/2010 10:04:46 AM
Reply:

Similar Artilces:

Column Headings #11
Can you add seperate column headings (A, B, C, ...) into one spreadsheet? I'm attempting to alter the column sizes half-way through the spreadsheet w/out affecting the upper column sizes... Coolumn width belongs to the entire column and cannot be altered in separate sections of that column. Gord Dibben Excel MVP On Tue, 8 Mar 2005 15:51:01 -0800, spencer4hire <spencer4hire@discussions.microsoft.com> wrote: >Can you add seperate column headings (A, B, C, ...) into one spreadsheet? >I'm attempting to alter the column sizes half-way through the spreadsheet >w/ou...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

multiple CD's, same bank
I've got multiple CD's opened up at the same bank. They are all under the same account number, but each under a separate heading. Each of the CD's mature at a different time, and they will be rolled over into new CD's over the next 18 years. Futhermore, my bank provides online banking services directly within MS Money, including automated download of statements. So...what would be the best way to set up these accounts in Money? Should I create one account named CD and then make a cash transaction for each CD? Should I set up each CD as an individual investment accou...

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Downloading multiple messages
All of a sudden outlook has started downloading multiple messages from my pop account (ie 22 of the same) It is also sending out 22 of the same message. How can I resolve this issue? What version of Outlook do you have? Does it download duplicates of *all* messages, or just of one or two messages? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "GW" <anonymous@discussions.microsoft.com> wrote in message news:005101c3bf4c$e748bf30$a501280a@phx.gbl... > All of a sudden outlook has started do...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Excel 2003 Copy/Paste filtered column
I have a filtered column on my spreadsheet. I have copied the column, changed the figures and then tried to paste it back on to the filtered column. It is not copying over the original filtered column but rather over cells that have been filtered out. The worksheet/cells are not protected. What could the problem be? Kind Regards Heather That's the way pasting works. It'll hit the visible and hidden cells. Heather wrote: > > I have a filtered column on my spreadsheet. I have copied the column, > changed the figures and then tried to paste it back on to the filter...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

how to automatically suppress space before after column break?
Having Spacing Before and After on some of the styles, I seem to be unable to have the space before at the beginning of a column automatically dismissed when applying a column break. I have tried a couple of options under compatibility, but to no avail. This in on Word 2003. The No HTML function + No Space Before after column break do not solve the problem. Can you help please? Tools | Options | Compatibility: Suppress Space Before after a hard page or column break. If this isn't working, then check to make sure you don't have an empty paragraph before the first text pa...

matching columns of numbers
In EXCEL 2000 for Windows, I have two columns of numbers. Column A has 500 numbers, Column B has 1000 numbers. I need to know which cells in Column A have a match in Column B, and if so, what is the Cell (or row number) in B that matches to that particular cell in A. How can I do this? Thank you for your help. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi try the following: - insert a new column between A and B (so make B the new C column) enter the following in B1 =IF(ISNA(MATCH...

Count the text in a column
I would like to count the text in a column then for it to add a figure in another cell if it meets the text criteria Thanks! Do you mean count the characters? If so =SUM(NOT(ISNUMBER(A1:A20))*LEN(A1:A20)) as an array formula (committed with Ctrl-Shift-Enter) -- HTH RP (remove nothere from the email address if mailing direct) "Peter Curtis" <PeterCurtis@discussions.microsoft.com> wrote in message news:94C093C4-38DC-4989-846A-9352F3298B7C@microsoft.com... > I would like to count the text in a column then for it to add a figure in > another cell if it meets the t...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Multiple Language Issues
Hi all, Has anyone successfully manually updated a non-English supported 5.5 to 2003? (The reason for the manual update was a broken admin account.) Mail and contacts were all exported to PST files, and then imported to 2003. However, although the text of the messages is fine (English and Russian), the text for contact names and message subjects gets gorched if they contained any Russian text. Does anyone know the correct sequence to follow to take messages and contacts in Russian (or other languages) from 5.5 to 2003, while retaining the non-English subject lines and contact names? ...

Multiple Sales Transaction for Same Work Order
I am in the process of trying to create a store level report by department, catagory, and item of the moneys received in total by both deposit on work orders and on direct sales that are not work orders. We require a 100% deposit (of a single tender type) on work orders. I then need to break this deposit down by department, catagory and item. Then I must add that to the same break down for direct sales. Thereby getting a total of all moneys received during a specified period for the store across all registers. So basically I am trying to figure out the data structure and org...

Highlight color for selected items
How can I find out what color is used for highlighting selected items (for example in Windows Explorer)on a users computer? Normally it is a blue color, but a user can change that. "Urban Olars" <anonymous@discussions.microsoft.com> wrote in message news:071c01c3c941$3db2c890$a401280a@phx.gbl... > How can I find out what color is used for highlighting > selected items (for example in Windows Explorer)on a users > computer? > Normally it is a blue color, but a user can change that. See if it's GetSysColor/COLOR_HIGHLIGHT. -- Jeff Partch [VC++ MVP] Take a ...

Routing Restrictions button unavailable (gray) no matter what options selected in IMS-->Routing tab
I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my "Routing Restrictions" button is unavailable (greyed-out) no matter what options I select. Any suggestions? Thank you. Try installing Exch Admin prog on other machine, apply SP4 and see if you can change it from there. Post back with details. ryanadmin wrote: > I am running Exchange 5.5 SP4. In Connections-->IMS-->Routing Tab, my > "Routing Restrictions" button is unavailable (greyed-out) no matter > what options I select. Any suggestions? Thank you. ...

Multiple Report Dictionaries
We just acquired another company and some of the modified reports will need to be different than the ones we currently use. There are a few people that will need to work with both companies in Great Plains. Is there a way to change which reportws dictionary a company uses - or a user uses - without editing the launch file? Thanks,, Mike You could have two installations of the client on the workstation. Each installation would have a different dictionary file. The user would have to pick the right one. "MikeW" <MikeW@discussions.microsoft.com> wrote in message news:...

in Publisher I want to merge/send email with multiple attachments
Using Publisher 2007 I know how to do a merge and then email out from a list. Publisher gives the option to attach multiple attachments to the email but when the send actually goes out it only carries the first of the attachments and discards the others. Is there a trick to making multiple attachments stay attached? or is this a bug... Thanks to anyone who can clear this up.. Rust Gilbert Rust, Since you already know how to do email merges in Publisher 2007, you're probably the ideal person to answer my question...I have been trying to do an email merge using an existing publis...

Selecting a column with an integer
Sub ColumnSelection() ' Selecting a column with an integer ' Please show me how to eliminate the use of Cells(1, 1) Dim r As Integer Dim c As String Dim numericcolumn As Integer Dim alphabetcolumn As String numericcolumn = 4 ' in practice 4 is the resultant of an equation alphabetcolumn = "=CHAR(" & numericcolumn + 64 & ")" Cells(1, 1) = alphabetcolumn ' I like to eliminate the use of Cells(1, 1) c = Cells(1, 1).Value ' I like to eliminate the use of Cells(1, 1) Cell...

Can I embed or link multiple .pdf files into or to an excel file?
I would like to link .pdf files to an excel file. Within my excel file I would like to have a column that has file names in it. Then have excel link those files to the excel file so when I print the excel file all the linked files print along with it. use hyper link "GrubbyG" wrote: > I would like to link .pdf files to an excel file. Within my excel file I > would like to have a column that has file names in it. Then have excel link > those files to the excel file so when I print the excel file all the linked > files print along with it. ...

Sending multiple Emails so each person does not get the list
We want to send out multiple emails to several email accounts. We dont want the accounts to see the list of accounts that the email went to. How does one do this in Outlook? In any contacts folder, Tools | Mail Merge is the best choice. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "tom" <Spamblocker@ameritech.net> wrote in message = ...

BP Req Mgmt Lookup should show additional columns
When doing a lookup I should be able to configure the columns that I would like to see visible on the lookup. For example, when looking up an item only item number and item description are visible fields. I would like to configure the lookup to show additional fields, like the vendor name. ...