Cross join query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
Click to expand...

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
 
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
Click to expand...

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.
 
if you use SQL Server Management Studio; or Access Data Projects; you
can design the subqueries IN DESIGN VIEW.

Access MDB sure doesn't support this functionality; nor does Oracle or
anywhere else in the world.

-Aaron
 
if you use SQL Server Management Studio; or Access Data Projects; you
can design the subqueries IN DESIGN VIEW.

Access MDB sure doesn't support this functionality; nor does Oracle or
anywhere else in the world.

-Aaron
 
Back
Top