SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 (solved) Subquery alias implementation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

opopanax666
Starting Member

8 Posts

Posted - 06/12/2014 :  04:21:32  Show Profile  Reply with Quote
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!

Edited by - opopanax666 on 06/12/2014 05:02:16

webfred
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 06/12/2014 :  04:42:17  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 06/12/2014 :  04:52:18  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 06/12/2014 :  05:01:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 06/12/2014 :  05:44:45  Show Profile  Visit webfred's Homepage  Reply with Quote
my pleasure :)


Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000