Hi, I have a quick question regarding unions in SQL 2005 (using express edition for development at the moment).
I am setting up somewhat of a stock management system and I want to be able to edit descriptions on an invoice (and only for that invoice), so what i have is:
*the stock table which holds the stock info (really cryptic) including the description
*a 'sales lines' table which holds the stock id, sale id, qty, etc etc...
*a 'sales lines descriptions table which will hold the description for a particular sales line ONLY if it has been edited
(amongst the other tables)
the only problem i'm facing at the moment is an easy way to determine if i use the standard or custom description, and what i was hoping i could do is something along the lines of 'unioning' the result of
"SELECT description FROM tblSalesLinesDescriptions WHERE lineID = tblSalesLines.LineID"
and
"SELECT description FROM tblStock WHERE stockID = tblSalesLines.StockID"
and just selecting the 'TOP 1' results, or grouping or something...
this works logically in my head as it should select the custom description if there is one, or the standard if there isnt...
I googled and found an example which was along the lines of
SELECT id, sum(amount)
FROM
(SELECT id, amount FROM table1
UNION ALL
SELECT id, amount FROM table2)
GROUP BY id
I tried adopting this to:
SELECT TOP 1 description FROM
(
SELECT description from tblStock where stockid = '<INSERT RELEVANT GUID HERE>'
UNION
SELECT description from tblsaleslinesdescriptions where lineid = '<INSERT RELEVANT GUID HERE>'
)
but this didnt work... came up with the error:
Incorrect syntax near ')'
referring to the last ')'
the subquery (unions) does return the correct result (ie: 2 descriptions), its just not doing the "SELECT TOP 1".
anyone got any suggestions??
Thanks
