SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error .. Only one expression can be in SELECT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mallorz4
Starting Member

27 Posts

Posted - 02/02/2014 :  14:50:44  Show Profile  Reply with Quote
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 - 02/02/2014 :  16:24:58  Show Profile  Reply with Quote
I tried naming each subquery and throwing in an EXISTS clause, it just doesn't make sense.

How can I get this information?
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 02/02/2014 :  17:39:33  Show Profile  Reply with Quote
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.
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 02/02/2014 :  17:45:10  Show Profile  Reply with Quote
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
Go to Top of Page

mallorz4
Starting Member

27 Posts

Posted - 02/02/2014 :  17:49:38  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 02/02/2014 :  22:58:34  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000