(solved) Subquery alias implementation

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.