| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-29 : 07:37:29
|
| Phyllis writes "I am joining multiple tables in a view.. The invoice table has multiple address keys .. billto, shipto, etc.. I need to join this table to an address table more than once..and these are only some of the columns needed in the view..I am getting an error message that the subquery is returning more than 1 value...HELP ...........SELECT dbo.tarInvoice.TranDate AS BIG01, dbo.tarInvoice.InvcKey AS BIG02, dbo.tarInvoice.CustPONo AS BIG04, dbo.CarvelEDICustomerFields.EDIVendorNo AS REF02IA, dbo.tarCustomer.CustID, dbo.tarCustomer.CustName AS N102C, dbo.tarCustomer.CustKey, dbo.tarInvoice.BatchKey, dbo.tarInvoice.BillToAddrKey, dbo.CarvelEDICustomerFields.EDIIdentifier AS N104BT, dbo.tciAddress.AddrLine1 AS N301BT0, dbo.tciAddress.AddrLine2 AS N301BT1, dbo.tciAddress.AddrLine3 AS N301BT2, dbo.tciAddress.AddrLine4 AS N301BT3, dbo.tciAddress.AddrLine5 AS N301BT4, dbo.tciAddress.AddrName AS N102BT, dbo.tciAddress.City AS N401BT, dbo.tciAddress.StateID AS N402BT, dbo.tciAddress.PostalCode AS N403BT, dbo.tciAddress.CountryID AS N404BT, (SELECT addrline1 FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N301BTC0, (SELECT addrline2 FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N301BTC1, (SELECT addrline3 FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N301BTC2, (SELECT addrline4 FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N301BTC3, (SELECT addrline5 FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N301BTC4, (SELECT addrname FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N102BTC, (SELECT city FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N401BTC, (SELECT StateID FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N402BTC, (SELECT PostalCode FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N403BTC, (SELECT CountryID FROM tciaddress a, tarinvoice b WHERE b.billtocustaddrKey = a.addrKey) AS N404BTC, dbo.CarvelEDICustomerFields.EDIIdentifier AS N104BTC,SELECT dbo.tarInvoice.TranDate AS BIG01, dbo.tarInvoice.InvcKey AS BIG02, dbo.tarInvoice.CustPONo AS BIG04, dbo.CarvelEDICustomerFields.EDIVendorNo AS REF02IA, dbo.tarCustomer.CustID, dbo.tarCustomer.CustName AS N102C, dbo.tarCustomer.CustKey, dbo.tarInvoice.BatchKey, dbo.tarInvoice.BillToAddrKey, dbo.CarvelEDICustomerFields.EDIIdentifier AS N104BT, dbo.tciAddress.AddrLine1 AS N301BT0, dbo.tciAddress.AddrLine2 AS N301BT1, dbo.tciAddress.AddrLine3 AS N301BT2, dbo.tciAddress.AddrLine4 AS N301BT3, dbo.tciAddress.AddrLine5 AS N301B |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-29 : 07:53:01
|
well one of your selects is returning more than one row back so you can use select top 1 ... with proper order by.or rewrite your query.without create table, insert into statement for sample data and expected results based on sample data there's not much more we can do...Go with the flow & have fun! Else fight the flow  |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-29 : 07:55:15
|
| I've modified the code as much as I could, but it should give you the idea:SELECT I.TranDate AS BIG01, I.InvcKey AS BIG02, I.CustPONo AS BIG04, CCF.EDIVendorNo AS REF02IA, C.CustID, C.CustName AS N102C, C.CustKey, I.BatchKey, I.BillToAddrKey, CCF.EDIIdentifier AS N104BT, A.AddrLine1 AS N301BT0, A.AddrLine2 AS N301BT1, A.AddrLine3 AS N301BT2, A.AddrLine4 AS N301BT3, A.AddrLine5 AS N301BT4, A.AddrName AS N102BT, A.City AS N401BT, A.StateID AS N402BT, A.PostalCode AS N403BT, A.CountryID AS N404BT,A.addrline1 AS N301BTC0,A.addrline2 AS N301BTC1,A.addrline3 AS N301BTC2,A.addrline4 AS N301BTC3,A.addrline5 AS N301BTC4,A.addrname AS N102BTC,A.city AS N401BTC,A.StateID AS N402BTC,A.PostalCode AS N403BTC,A.CountryID AS N404BTC, CCF.EDIIdentifier AS N104BTC, I.TranDate AS BIG01, I.InvcKey AS BIG02, I.CustPONo AS BIG04, CCF.EDIVendorNo AS REF02IA, C.CustID, C.CustName AS N102C, C.CustKey, I.BatchKey, I.BillToAddrKey, CCF.EDIIdentifier AS N104BT, A.AddrLine1 AS N301BT0, A.AddrLine2 AS N301BT1, A.AddrLine3 AS N301BT2, A.AddrLine4 AS N301BT3, A.AddrLine5 AS N301BFROM dbo.tarInvoice I INNER JOIN dbo.tciAddress A ON I.billtocustaddrKey = A.addrKeydbo.CarvelEDICustomerFields CCF --need join condition here, was not provideddbo.tarCustomer C --need join condition here, was not providedA few things to notice:- You use table aliases in the subqueries but not in the main query. You should always alias your table names, especially if they're long- Use ANSI JOIN syntax (highlighted in red), it is more clear than using WHERE syntax to do a join- Use joins instead of subqueries, especially if each subquery has the same FROM clause- Consider building the query from the bottom up: list all of the tables you need and build the FROM clause first. This includes the JOIN conditions and table aliases. Once that's done, you can build the SELECT clause with all of the columns you need. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-29 : 07:56:41
|
i stand corrected.  Go with the flow & have fun! Else fight the flow |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-29 : 08:17:22
|
| You were correct the first time. |
|
|
|
|
|
|
subqueries