|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Greetings everyone. I am having trouble understanding how to get a cross
join to work properly. I am experimenting with a running total and grand sum
feature using cross joins. My problem is that all the help articles I have
read make reference to 'tablename a' and 'tablename b' or something to that
effect, basically calling the same table two different names in the query and
sub-query. Is this some kind of table alias, and if so how do I do it? I
have tried adding the same table twice in the query window using the add
table button, but when I do this the second instance of the table is called
'mytable_1'. Then, when I try to use 'mytable_1' in the sub-query I get an
error saying 'mytable_1' is not a valid object. I am trying this in
Enterprize Manager of SQL2K. Can anyone clear this up any, or point me to a
help article that will not assume I know anything? Thanks to all.
|
|
Cross join and Sub-queries are not the same concept. As far as I remember,
you cannot use the graphical designer to create sub-queries; you must write
them yourself while editing the SP in text mode. The graphical designer can
only be used for very simple tasks.
Finally, as you don't say anything about what you are trying to achieve,
it's hard to tell you if you need to write a cross join or a sub-query. The
mention of running total and grand sum could be a hint but when you are
willing to start wanting to write such things as running total and grand
sum, you are better to have a good knowledge of T-SQL if you want to achieve
your goal.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Greg Snidow" <GregSnidow[ at ]discussions.microsoft.com> wrote in message
news:97E7676B-1BCF-43EF-BCB2-AEB8B776191D[ at ]microsoft.com...
[Quoted Text] > Greetings everyone. I am having trouble understanding how to get a cross
> join to work properly. I am experimenting with a running total and grand
> sum
> feature using cross joins. My problem is that all the help articles I
> have
> read make reference to 'tablename a' and 'tablename b' or something to
> that
> effect, basically calling the same table two different names in the query
> and
> sub-query. Is this some kind of table alias, and if so how do I do it? I
> have tried adding the same table twice in the query window using the add
> table button, but when I do this the second instance of the table is
> called
> 'mytable_1'. Then, when I try to use 'mytable_1' in the sub-query I get
> an
> error saying 'mytable_1' is not a valid object. I am trying this in
> Enterprize Manager of SQL2K. Can anyone clear this up any, or point me to
> a
> help article that will not assume I know anything? Thanks to all.
|
|
Hi,
do you really mean a "cross join" or do you by chance mean a "pivot table" ?
A cross join is just a cartesian product as shown here
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/sqlp/rbafymstcrojo.htm
but in your question you ask for a grand sum and subqueries etc...
so you probably mean a pivot
maybe that article helps you out
http://www.databasejournal.com/features/mssql/article.php/3516331
If you're coming from Access (MDB) site, then you may know the Transform
startement in SQL queries.
Problem here is, that a similar pivoting statement first is implemented in
SQL Server with the 2005 version. It doesn't exist within 2000 So you have
it to emulate here, which surely is tricky ...
You may search in goole for sql and pivot for some interesting findings
--
mfg
Klaus Oberdalhoff KObd[ at ]gmx.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
Greg Snidow wrote:
[Quoted Text] > Greetings everyone. I am having trouble understanding how to get a
> cross join to work properly. I am experimenting with a running total
> and grand sum feature using cross joins. My problem is that all the
> help articles I have read make reference to 'tablename a' and
> 'tablename b' or something to that effect, basically calling the same
> table two different names in the query and sub-query. Is this some
> kind of table alias, and if so how do I do it? I have tried adding
> the same table twice in the query window using the add table button,
> but when I do this the second instance of the table is called
> 'mytable_1'. Then, when I try to use 'mytable_1' in the sub-query I
> get an error saying 'mytable_1' is not a valid object. I am trying
> this in Enterprize Manager of SQL2K. Can anyone clear this up any,
> or point me to a help article that will not assume I know anything?
> Thanks to all.
|
|
|
[Quoted Text] > Cross join and Sub-queries are not the same concept. As far as I
> remember, you cannot use the graphical designer to create sub-queries; you
> must write them yourself while editing the SP in text mode. The graphical
> designer can only be used for very simple tasks.
Actually, for sub-queries, you can design them in a separate window, then
cut & paste the results, or if you can "plan ahead", just design the
sub-query first, and once you're done, go into the SQL pane and surround it
with SELECT * FROM (<your sub query>) and continue your design work with the
sub-query now being treated like any other table/view.
Rob
|
|
|
[Quoted Text] > Actually, for sub-queries, you can design them in a separate window, then
> cut & paste the results, or if you can "plan ahead", just design the
Sorry, bad wording on my part...by "cut & paste the results", what I really
meant was "cut & paste the resulting SQL code".
Rob
|
|
Thanks to all of you. I am actually taking a class in TSQL this week and I
have realized the limitations of using the grid pane, and I have figured out
how to do the running sum. To any other newbies like me, it definately pays
to learn the code.
"Greg Snidow" wrote:
[Quoted Text] > Greetings everyone. I am having trouble understanding how to get a cross
> join to work properly. I am experimenting with a running total and grand sum
> feature using cross joins. My problem is that all the help articles I have
> read make reference to 'tablename a' and 'tablename b' or something to that
> effect, basically calling the same table two different names in the query and
> sub-query. Is this some kind of table alias, and if so how do I do it? I
> have tried adding the same table twice in the query window using the add
> table button, but when I do this the second instance of the table is called
> 'mytable_1'. Then, when I try to use 'mytable_1' in the sub-query I get an
> error saying 'mytable_1' is not a valid object. I am trying this in
> Enterprize Manager of SQL2K. Can anyone clear this up any, or point me to a
> help article that will not assume I know anything? Thanks to all.
|
|
|