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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 (solved) Subquery alias implementation

Author  Topic 

opopanax666
Starting Member

8 Posts

Posted - 2014-06-12 : 04:21:32
Hi everyone,

I usually make my T-SQL queries in Access, and then change the syntax to use the query in Excel (eg change "dbo_" to "dbo.", or change " to ').
These are usually very basic SELECT queries, and this method has worked fine so far.

But now I need to construct a query involving a subquery, and I have no clue how to incorporate the subquery in the main query, except that I would need to use an alias. But I have no clue about the syntax...

Main query:
SELECT dbo.tblGEPRODUCEERD2.*, qryBESTELLING_barcode_BE.Verzending_datum, dbo.tblBESTEMMING_SUB.Bestemming_sub_naam
FROM (qryBESTELLING_barcode_BE INNER JOIN dbo.tblGEPRODUCEERD2 ON qryBESTELLING_barcode_BE.Barcode = dbo.tblGEPRODUCEERD2.Verzending_id) INNER JOIN dbo.tblBESTEMMING_SUB ON qryBESTELLING_barcode_BE.Bestemming_sub_id = dbo.tblBESTEMMING_SUB.Bestemming_sub_ID
WHERE ((year(qryBESTELLING_barcode_BE.Verzending_datum)>=2014) AND (dbo.tblGEPRODUCEERD2.Verzending_id Is Not Null));


Query "qryBESTELLING_barcode_BE":
SELECT 'BE' & Right('000000' & dbo.tblBESTELLING.Bestelling_ID,6) AS Barcode, dbo.tblBESTELLING.*
FROM dbo.tblBESTELLING;


I could use the name "qryBESTELLING_barcode_BE" as alias, but don't know where to insert the subquery.

Any help would be much appreciated!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-12 : 04:42:17
try this:
SELECT 
dbo.tblGEPRODUCEERD2.*,
qryBESTELLING_barcode_BE.Verzending_datum,
dbo.tblBESTEMMING_SUB.Bestemming_sub_naam
FROM
(
SELECT 'BE' & Right('000000' & dbo.tblBESTELLING.Bestelling_ID,6) AS Barcode, dbo.tblBESTELLING.*
FROM dbo.tblBESTELLING
) as qryBESTELLING_barcode_BE

INNER JOIN dbo.tblGEPRODUCEERD2 ON qryBESTELLING_barcode_BE.Barcode = dbo.tblGEPRODUCEERD2.Verzending_id
INNER JOIN dbo.tblBESTEMMING_SUB ON qryBESTELLING_barcode_BE.Bestemming_sub_id = dbo.tblBESTEMMING_SUB.Bestemming_sub_ID

WHERE ((year(qryBESTELLING_barcode_BE.Verzending_datum)>=2014) AND (dbo.tblGEPRODUCEERD2.Verzending_id Is Not Null));



Too old to Rock'n'Roll too young to die.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-12 : 04:52:18
ah and change & by +


Too old to Rock'n'Roll too young to die.
Go to Top of Page

opopanax666
Starting Member

8 Posts

Posted - 2014-06-12 : 05:01:43
Woah, that was quick!

It also clearly shows the logical structure, so gonna keep it as an example.

Thank you very much!
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-12 : 05:44:45
my pleasure :)


Too old to Rock'n'Roll too young to die.
Go to Top of Page
   

- Advertisement -