Hjälp med fråga

Jag har 3 tabeller a, b och c

a.id, a.namn, a.tele
b.aid, b.antal, b.pris
c.aid, b.antal, c.pris

Nu vill jag hämta en rad ur tabellen a och alla relaterade rader i tabellerna b och c. Hur ska jag ställa frågan? Så som min fråga ser ut idag hämtar den alla rader dubbelt så fort det är mer än en rad i tabellerna b och c.

select a.id,a.namn,a.tele,b.antal, b.pris, 'b records' as origin
   from a join b on a.id = b.aid
 union all
select a.id,a.namn,a.tele,c.antal, c.pris, 'c records'
   from a join c on a.id = c.aid

Då är frågan om man kan göra frågan smidigare än hur den ser ut just nu.

SELECT
	/* Invoice */
	tblInvoice.InvoiceID, 
	tblInvoice.StartDate, 
	tblInvoice.EndDate,  
	(tblInvoice.InvoicePrefix + '' + Convert(varchar, tblInvoice.InvoiceNumber)) AS InvoiceNumber, 
	tblInvoice.Km, 
	tblInvoice.KmPrice, 
	tblInvoice.HourPrice, 
	tblInvoice.GST,

	/* DETAILS */
	tblProducts.Type,
	tblProducts.Quantity, 
	tblProducts.Description, 
	tblProducts.UnitPrice + (1 + CONVERT (money, (SELECT Setting FROM tblSetup WHERE (SetupID = 'ProduktAddering')))) AS UnitPrice, 
	dbo.LineTotal(tblProducts.Quantity, tblProducts.UnitPrice + (1 + CONVERT (money, (SELECT Setting FROM tblSetup AS tblSetup WHERE (SetupID = 'ProduktAddering')))), 0, tblInvoice.GST, 0) AS RowProductPrice, 

	/* Customer Details */
	tblCustomers.Name AS bName, 
	tblCustomers.Contact AS bContact, 
	tblCustomers.Street AS bStreet, 
	tblCustomers.PostCode AS bPostCode, 
	tblCustomers.City AS bCity, 
	tblCustomers.State AS bState,
	tblCustomers.Country AS bCountry, 
	tblCustomers.Phone AS bPhone, 
	tblCustomers.Fax AS bFax, 
	tblCustomers.Mobile AS bMobile, 
	tblCustomers.Email AS bEmail, 
	tblCustomers.Description AS bDescription, 
	
	/* Company Details */
	tblCompany.Name AS aName, 
	tblCompany.Contact AS aContact,
	tblCompany.Street AS aStreet, 
	tblCompany.PostCode AS aPostCode, 
	tblCompany.City AS aCity, 
	tblCompany.Email AS aEmail, 
	tblCompany.Phone AS aPhone, 
	tblCompany.Mobile AS aMobile, 
	tblCompany.MomsRegNr AS aMomsRegNr, 
	tblCompany.BankgiroNr AS aBankGiroNr, 
	tblCompany.PlusGiroNr AS aPlusGiroNr, 
	tblCompany.Fax AS aFax
	
FROM
	tblInvoice 
	INNER JOIN tblCompany ON tblInvoice.CompanyID = tblCompany.CompanyID 
	INNER JOIN tblCustomers ON tblInvoice.CustomerID = tblCustomers.CustomerID
	LEFT OUTER JOIN tblProducts ON tblInvoice.InvoiceID = tblProducts.InvoiceID
WHERE 
	tblInvoice.InvoiceID = @InvoiceID
UNION ALL
SELECT
	/* Invoice */
	tblInvoice.InvoiceID, 
	tblInvoice.StartDate, 
	tblInvoice.EndDate,  
	(tblInvoice.InvoicePrefix + '' + Convert(varchar, tblInvoice.InvoiceNumber)) AS InvoiceNumber, 
	tblInvoice.Km, 
	tblInvoice.KmPrice, 
	tblInvoice.HourPrice, 
	tblInvoice.GST,
 
	Convert(varchar, tblInvoiceDetails.Day), 
	tblInvoiceDetails.Quantity, 
	tblInvoiceDetails.Description,
	tblInvoice.HourPrice As UnitPrice, 
	dbo.LineTotal(tblInvoiceDetails.Quantity, tblInvoice.HourPrice, 0, tblInvoice.GST, 0) AS RowTotal,

	/* Customer Details */
	tblCustomers.Name AS bName, 
	tblCustomers.Contact AS bContact, 
	tblCustomers.Street AS bStreet, 
	tblCustomers.PostCode AS bPostCode, 
	tblCustomers.City AS bCity, 
	tblCustomers.State AS bState,
	tblCustomers.Country AS bCountry, 
	tblCustomers.Phone AS bPhone, 
	tblCustomers.Fax AS bFax, 
	tblCustomers.Mobile AS bMobile, 
	tblCustomers.Email AS bEmail, 
	tblCustomers.Description AS bDescription, 
	
	/* Company Details */
	tblCompany.Name AS aName, 
	tblCompany.Contact AS aContact,
	tblCompany.Street AS aStreet, 
	tblCompany.PostCode AS aPostCode, 
	tblCompany.City AS aCity, 
	tblCompany.Email AS aEmail, 
	tblCompany.Phone AS aPhone, 
	tblCompany.Mobile AS aMobile, 
	tblCompany.MomsRegNr AS aMomsRegNr, 
	tblCompany.BankgiroNr AS aBankGiroNr, 
	tblCompany.PlusGiroNr AS aPlusGiroNr, 
	tblCompany.Fax AS aFax
	
FROM
	tblInvoice 
	INNER JOIN tblCompany ON tblInvoice.CompanyID = tblCompany.CompanyID 
	INNER JOIN tblCustomers ON tblInvoice.CustomerID = tblCustomers.CustomerID
	LEFT OUTER JOIN tblInvoiceDetails ON tblInvoice.InvoiceID = tblInvoiceDetails.InvoiceID

WHERE 
	tblInvoice.InvoiceID = @InvoiceID

Det känns inte som en toppenlösning trots allt. Hur borde jag tänka?

Kanske ska spara det som en view och hämta från den?

Jag förstår inte, du har ju samma tabeller i båda union-grenarna.

Borde du inte ha enbart customer i ena grenen och enbart company i andra?

Nja det var inte frågan egentligen, det handlar om att hämta dels arbetat tid och vilken dag samt vilka produkter användaren har köpt och sedan slå ihop det hela.

För tillfället finns bara ett företag och en kund men det blir möjligt att bygga på med flera kunder. Det är en helt skräddarsydd lösning åt ett mindre företag.

Förlåt mit taskiga svar nyss. Det jag behöver göra en union på är arbetad tid och inköpta produkter. Vill slippa använda subreports och dubbla dataset i crystal reports.