Error .. Only one expression can be in SELECT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error .. Only one expression can be in SELECT

Author

Topic

mallorz4
Starting Member

27 Posts

Posted - 2014-02-02 : 14:50:44

The full error is this: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

My Code:

SELECT Customers.CustomerID, Customers.EmailAddress, Customers.LastName, Customers.FirstName,
	(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers 
		ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID),
	(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers 
		ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID)
FROM Customers JOIN Addresses	
	ON Customers.CustomerID = Addresses.CustomerID;

I am trying to test a query I am going to use to create a view with all of a customers addresses (billing and shipping). Is there another way I should be approaching this?

mallorz4
Starting Member

27 Posts

Posted - 2014-02-02 : 16:24:58

I tried naming each subquery and throwing in an EXISTS clause, it just doesn't make sense.

How can I get this information?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-02 : 17:39:33

Try this:

select c.CustomerID
      ,c.EmailAddress
      ,c.LastName
      ,c.FirstName
      ,ba.Line1
      ,ba.Line2
      ,ba.City
      ,ba.State
      ,ba.ZipCode
      ,sa.Line1
      ,sa.Line2
      ,sa.City
      ,sa.State
      ,sa.ZipCode
  from Customers as c
       inner join Addresses as ca
               on ca.CustomerID=c.CustomerID
       inner join Addresses as ba
               on ba.AddressID=c.BillingAddressID
       inner join Addresses as sa
               on sa.AddressID=c.ShippingAddressID

Lines in red are code not needed, giving the example you provided. I left it in there, cause your query joined this table, so maybe you'll need it to show address fields from the customer.

mallorz4
Starting Member

27 Posts

Posted - 2014-02-02 : 17:45:10

quote:

Originally posted by bitsmed

Try this:

select c.CustomerID
      ,c.EmailAddress
      ,c.LastName
      ,c.FirstName
      ,ba.Line1
      ,ba.Line2
      ,ba.City
      ,ba.State
      ,ba.ZipCode
      ,sa.Line1
      ,sa.Line2
      ,sa.City
      ,sa.State
      ,sa.ZipCode
  from Customers as c
       inner join Addresses as ca
               on ca.CustomerID=c.CustomerID
       inner join Addresses as ba
               on ba.AddressID=c.BillingAddressID
       inner join Addresses as sa
               on sa.AddressID=c.ShippingAddressID

Lines in red are code not needed, giving the example you provided. I left it in there, cause your query joined this table, so maybe you'll need it to show address fields from the customer.

Thank you. I will give that a try and let you know. I just tried the following which received an error that more than one value was returned ...

SELECT Customers.CustomerID, Customers.EmailAddress, Customers.LastName, Customers.FirstName,
	(SELECT Line1 FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID) AS BillLine1,
	(SELECT Line2 FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID) AS BillLine2,
	(SELECT City FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID) AS BillCity,
	(SELECT State FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID) AS BillState,
	(SELECT ZipCode FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID) AS BillZip,

	(SELECT Line1 FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID) AS ShipLine1,
	(SELECT Line2 FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID) AS ShipLine2,
	(SELECT City FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID) AS ShipCity,
	(SELECT State FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID) AS ShipState,
	(SELECT ZipCode FROM Addresses JOIN Customers ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID) AS ShipZip
FROM Customers JOIN Addresses	
	ON Customers.CustomerID = Addresses.CustomerID

mallorz4
Starting Member

27 Posts

Posted - 2014-02-02 : 17:49:38

Bitsmed -

Thank you so much. That is much clearer and executed without a hitch, just need to work out my view now. I don't think I've seen that approach before - very nice. Honestly, I really appreciate the help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-02-02 : 22:58:34

you might want to change the INNER JOIN to LEFT JOIN, if you might have NULL value in BillingAddressID or ShippingAddressID. Else, you will not get any result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources