JOINS to Sub-Queries -vs- JOINS to Tables

SQL Server 2000

Howdy All.

Is it going to be faster to join several tables together and then
select what I need from the set or is it more efficient to select only
those columns I need in each of the tables and then join them together
?

The joins are all Integer primary keys and the tables are all about the
same.

I need the fastest most efficient method to extract the data as this
query is one of the most used in the system.

Thanks,

CraigOn 11 Aug 2005 09:24:08 -0700, csomberg@.dwr.com wrote:

>SQL Server 2000
>Howdy All.
>Is it going to be faster to join several tables together and then
>select what I need from the set or is it more efficient to select only
>those columns I need in each of the tables and then join them together
>?
>The joins are all Integer primary keys and the tables are all about the
>same.
>I need the fastest most efficient method to extract the data as this
>query is one of the most used in the system.
>Thanks,
>Craig

Hi Craig,

I'm not sure I understand your question. Are you asking about the
performance difference between queries like these two?

SELECT A.something, B.otherthing
FROM TableA AS A
INNER JOIN TableB AS B
ON A.xxx = B.xxx
WHERE A.yyy = y
AND B.zzz = z

or

SELECT A.something, B.otherthing
FROM (SELECT xxx, something
FROM TableA
WHERE A.yyy = y) AS A
INNER JOIN (SELECT xxx, otherthing
FROM TableB
WHERE B.zzz = z) AS B
ON A.xxx = B.xxx

My first guess is that there will be no difference. The optimizer is
free to rearrange the query every way it wants, as long as the end
results are the same. They will probably result in the same execution
plan.

On the other hand, it is very hard to predict what the optimizer will
do. It often does a good job, but there still are situations where it
shows that it's just a program.

If you really want to be sure, then why don't you simply test both
against your system?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(csomberg@.dwr.com) writes:
> Is it going to be faster to join several tables together and then
> select what I need from the set or is it more efficient to select only
> those columns I need in each of the tables and then join them together
> ?
> The joins are all Integer primary keys and the tables are all about the
> same.
> I need the fastest most efficient method to extract the data as this
> query is one of the most used in the system.

Your query is open to several interpretations, so the answers you get
may not address your real issue.

If your idea is to first join two tables, get those columns into
a temp table, join that with the next table, then this is generally
not a good idea. Although, when it comes to performance there a few
definitive answers. For a certain query, this could actually be a
good strategy. But as a general approach, it's better to throw in
all tables into one query.

And you should not use SELECT * - only list the columns you actually
need.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I need the fastest most efficient method to extract the data as this query is one of the most used in the system. <<

Then test them. But my guess is that the optimizer will do them the
same way. Putting all the tables in the FROM clause will be much easier
to read and maintain, however.sql

Joining when rows don't exist.

Afternoon all..
I'm trying to join two tables together. The tables have information
like:
Table_Data
Name - Calls - Sales
Bob -- 17 -- 10
John -- 23 -- 5
Dave -- 25 -- 7
Carol -- 16 -- 13
Table_Target_Data
Name - Calls - Sales
Bob -- 30 -- 20
Carol -- 40 -- 30
What I need to do is to join them together, but also include those
people that aren't mentioned in the target table.
So far I have :
SELECT
T1.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1,Table_Data T2
where T1.Name = T2.Name
Group by T1.Name
This will only bring back those names that are in both tables. How can
I bring back 'John' and 'Dave', filling in relevant results with <null>
or '0'?Hi John,
SELECT
T1.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1
LEFT JOIN Table_Data T2
ON T1.Name = T2.Name
HTH, Jens Suessmeyer.|||You need to use left join and you're using inner join.
MC
<vladikavkaz@.XXXXyou.co.uk> wrote in message
news:1139227585.567641.110490@.g44g2000cwa.googlegroups.com...
> Afternoon all..
> I'm trying to join two tables together. The tables have information
> like:
> Table_Data
> Name - Calls - Sales
> Bob -- 17 -- 10
> John -- 23 -- 5
> Dave -- 25 -- 7
> Carol -- 16 -- 13
> Table_Target_Data
> Name - Calls - Sales
> Bob -- 30 -- 20
> Carol -- 40 -- 30
> What I need to do is to join them together, but also include those
> people that aren't mentioned in the target table.
> So far I have :
> SELECT
> T1.Name AS [Agent Name],
> T1.Calls AS [Target Client Calls],
> T2.Calls AS [Client Calls],
> T1.Sales AS [Target Sales],
> T2.Sales AS [Sales]
> FROM
> Table_Target_Data T1,Table_Data T2
> where T1.Name = T2.Name
> Group by T1.Name
> This will only bring back those names that are in both tables. How can
> I bring back 'John' and 'Dave', filling in relevant results with <null>
> or '0'?
>|||vladikavkaz@.XXXXyou.co.uk wrote on 6 Feb 2006 04:06:25 -0800:

> Afternoon all..
> I'm trying to join two tables together. The tables have information
> like:
> Table_Data
> Name - Calls - Sales
> Bob -- 17 -- 10
> John -- 23 -- 5
> Dave -- 25 -- 7
> Carol -- 16 -- 13
> Table_Target_Data
> Name - Calls - Sales
> Bob -- 30 -- 20
> Carol -- 40 -- 30
> What I need to do is to join them together, but also include those
> people that aren't mentioned in the target table.
> So far I have :
> SELECT
> T1.Name AS [Agent Name],
> T1.Calls AS [Target Client Calls],
> T2.Calls AS [Client Calls],
> T1.Sales AS [Target Sales],
> T2.Sales AS [Sales]
> FROM
> Table_Target_Data T1,Table_Data T2
> where T1.Name = T2.Name
> Group by T1.Name
> This will only bring back those names that are in both tables. How can
> I bring back 'John' and 'Dave', filling in relevant results with <null>
> or '0'?
Use an outer join.
SELECT
T2.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1 RIGHT OUTER JOIN Table_Data T2
ON T1.Name = T2.Name
Group by T2.Name
If you want 0 in the target data rather than nulll, use
SELECT
T2.Name AS [Agent Name],
COALESCE(T1.Calls,0) AS [Target Client Calls],
T2.Calls AS [Client Calls],
COALESCE(T1.Sales,0) AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1 RIGHT OUTER JOIN Table_Data T2
ON T1.Name = T2.Name
Group by T2.Name
Dan|||Thanks all for the help.
Got the results I need now.

Joining two large queries as derived tables

All,
I want to join two large queries together keying on a common column. I
have simplifed the queries / derived tables; but I am trying to do
something like what I have below. How do you join two derived tables?
SELECT * FROM
(SELECT Name AS 'Name1', ... other columns
FROM [TABLE1]) AS a
JOIN
SELECT * FROM
(SELECT Name AS 'Name2', ... other columns
FROM [TABLE2]) AS b
ON a.Name = b.NameTry,
SELECT *
FROM
(SELECT Name AS 'Name1', ... other columns FROM [TABLE1]) AS a
inner JOIN
(SELECT Name AS 'Name2', ... other columns FROM [TABLE2]) AS b
ON a.Name = b.Name
go
AMB
"dpless@.gmail.com" wrote:

> All,
> I want to join two large queries together keying on a common column. I
> have simplifed the queries / derived tables; but I am trying to do
> something like what I have below. How do you join two derived tables?
>
> SELECT * FROM
> (SELECT Name AS 'Name1', ... other columns
> FROM [TABLE1]) AS a
> JOIN
> SELECT * FROM
> (SELECT Name AS 'Name2', ... other columns
> FROM [TABLE2]) AS b
> ON a.Name = b.Name
>|||Is this what you are trying to do?
SELECT * FROM
(SELECT * FROM table1) a
INNER JOIN (select * from table2) b ON a.name=b.name
-- or --
SELECT * FROM
(SELECT * FROM dailychallenge) a,
(SELECT * FROM DailyChallengeResponse) b
WHERE a.challengedate=b.challengedate
Let us know how you make out.
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--

Joining two columns together to be searched as one

Hi all,

I have three questions to ask that hopefully can be answered. My database server is MySQL 4.1 and my database table contains the following example data:

Id | Store | Make | Model | Price
----------------
1 | Store1 | JVC | AV36D502 | 100.00
2 | Store2 | JVC | AV36D502 | 400.00
3 | Store1 | Panasonic | CT32HX41 | 250.00
4 | Store2 | Panasonic | CT32HX41 | 350.00
5 | Store1 | Sony | KV32S42 | 400.00
6 | Store2 | Sony | KV32S42 | 500.00

(1) Is it possibe to have in my select statement the following: "WHERE ((Make, Model) = 'Sony KV32S42')", I have already tried this but it throws up errors. What I'm trying to do is to some how join together the Make and Model columns and then to see if it matches the search term.

(2) If I was to "SELECT DISTINCT Make" or equivalent, rather than returning 3 rows is there anyway to return just the one row but with the value of "JVC Panasonic Sony" i.e. a string of all Makes, or is this impossible.

and (3) I have another select statement "SELECT DISTINCT Make, MIN(Price), MAX(Price)" with the output shown below. If I "ORDER BY Price ASC" the default is that it orders it by the maximum price, what I want to do is to order it by the minimum price only. "ORDER BY MIN(Price) ASC" has errors or again is this impossible.

Make | MIN(Price) | MAX(Price)
------------
JVC | 100.00 | 400.00
Panasonic | 250.00 | 350.00
Sony | 400.00 | 500.00

I know I'm asking a lot but again I just can't find any good information on the net. Thanking you in advance.(1) where concat_ws(' ',make.model) = 'Sony KV32S42'

(2) with the GROUP_CONCAT function

(3) you can't use DISTINCT here

select Make, MIN(Price) as minprice, MAX(Price) as maxprice
from yourtable group by Make order by minprice|||(1) WHERE make = 'Sony' AND model = 'KV32S42'.
(2) AFAIK, this is impossible with "standard" SQL.
(3) See r937's solution, or alternatively "ORDER BY 2".|||Many thanks guys,

(1) and (3) work perfectly now, (2) will work once I upgrade to mySQL 4.1 or 5.0|||btw, your design is denormalized... Is there a reason you don't store make and model in a seperate table?|||I'm sorry I don't understand, why would you want to put make and model in seperate tables, surely that would make the design more complex? Or would it be better (more efficient) if I just had the one column called "product" which would store the make and model, i.e. Sony KV32S42.|||It would make the model slighly more "complex" in that you will have an extra table, sure. However it will make the model much simpler in that you will have far more flexibility with a "normalized" design. Read up at this site (http://r937.com/relational.html). It has some good models for what I'm talking about.

You do have the right general idea though. If you were to put make and model in a seperate table with a unique key, you could then use that key to store prices for competitors. When it comes time to report, your queries become far easier and more intuitive, with less margin for error.

Joining multiple result sets together

Hi All,
I've been trying this for over a week now, and I simply cannot get this to work. Basically, what I am trying to do is get a result set that has the 4 columns, PID, LAST, SMOKER, HYPER for every person in the table (every person has a unique PID). My problem is that these columns are from different tables, so I have been trying to do 2 left joins to make the result set. This is what I came up with, but it has syntax problems from what I can gather.

SELECT E.PID, E.LAST, M.SMOKER, C.HYPER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)

After looking at the above query, I got the feeling that the C.HYPER might have been the problem as it is getting its data from the first left join. I tried to fix that with the statement below. It's probably terribly wrong too, but my SQL experience is limited, and i've never actually had to do a join from 3 tables before.

SELECT TT.PID, TT.LAST, TT.SMOKER_PAST, C.HYPER FROM (SELECT E.PID, E.LAST, M.SMOKER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT) LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)

What I was aiming for here was to make the second select result set act like a table of its own (TT). This didn't work too well either :)

Any help at all would be greatly appreciated.

Thanks,
Michael

BTW this forum is great. I only just found it, but I think I'll be coming here a lot more now. The amount of knowledge in here is unbelievable.try this --SELECT E.PID, E.LAST, M.SMOKER, C.HYPER
FROM ENROLLMENT E
LEFT
JOIN MEDICALHISTORY M
ON E.PID = M.PID
LEFT
JOIN CARDIACHISTORY C
ON E.PID = C.PIDrudy
http://r937.com/|||Thanks for the reply. I don't have access to the database right now, but I'll get back to you on Monday to let you know how it worked.|||Hi Rudy,
Thanks for the help. I just tried it out on the database, and whilst it didn't work for me, it got me thinking and I managed to get something that was workable. I'd post up what I ended up using, but it's a mess and hardly the most efficient way of doing it (it had to be generated by code on the fly, so the actual solution I ended up with is really redundant, but is easy to build in the code when certain buttons are pressed).

Thanks for the help, mate.

Cheers,
Michael

Join using max value

I want to join two tables together but only join the where the column
in table 2 (of type date) is the maximum value. Is this possible?

Note table 1 is the main table it is also joined to other tables too.Hello,

Would something like this work?

select
*
from
table1 t1
join table2 t2 on t1.joinkey=t2.joinkey
where
t2.date = (select max(datefield) as max_date from table2)

Greg.

ree32 wrote:
> I want to join two tables together but only join the where the column
> in table 2 (of type date) is the maximum value. Is this possible?
> Note table 1 is the main table it is also joined to other tables too.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. It is a bitch to debug code that you cannot see.|||Actually, if you read his request, he is only asking if its possible.

So a simple YES, (sans any explanation of technique), would appear to
suffice.

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1112319257.363540.54270@.f14g2000cwb.googlegro ups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications. It is a bitch to debug code that you cannot see.|||Thanks for the idea, I will test it out later.

"Greg" <gregdenison@.gmail.com> wrote in message news:<1112314603.380779.78130@.g14g2000cwa.googlegroups.c om>...
> Hello,
> Would something like this work?
> select
> *
> from
> table1 t1
> join table2 t2 on t1.joinkey=t2.joinkey
> where
> t2.date = (select max(datefield) as max_date from table2)

join two tables and only return the latest data for the child table

I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:

select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?

I tried to create a function to get the latest sentence for a chargeID like the following:

select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?

thanks,

if you are on 2005, you can use row_number(), as follows:

select * from(
select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
) t
where rn=1
|||

Your suggestion works, thanks.

But when I modified the query to:

select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

where rn=1

the query fails with the error "Invalid column name 'rn'.", why?

|||Because Where applies to the source fields, not the result fields, so your alias "as rn" hasn't been applied yet.

That's why Akuz nested it inside another Select. The outer Select/Where can use the alias.

join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).

join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).

JOIN question

Hi, I have a question concerning joining three tables together, and
getting the information to spit out correctly. Say I have three
tables:
products
ID Code Name
1 9000 Test Product
categories
ID Name
1 Test Category
2 Another Test
3 Yet another test
productcategories
ID Category Product
1 1 1
2 2 1
3 3 1
Now I want SQL to send back product ID, Code, Product Name, and all
categories it is with. So for example, one row would be:
ID Code Name Categories
1 9000 Test Product Test Category,Another Test,Yet
another test
Here is the SQL I am using. All it is doing is pulling one category
for now. I don't know how to group by and do a concatenation of all
the categories.
SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
(C.ID=PC.Name) GROUP BY P.ID
Any help in this would be appreciated. I am still relatively new to
the complexity of GROUP BYs in SQL.
Thanks for all the help!
JJHi,
I think pure SQL will not be sufficient in this case as far as text
(categories.names) shall be aggregated (concatenated). 'Group by'
returns just one member of the (each) group. If the select list contains
an aggregate function, 'group by' will aggregate over all group
records . So, if a aggregate function e.g. 'concatenateText' would
exist, it could be used here. As far as I know it does not exist.
Therefore a function could be used doing the concatenation.
Then the query would be like:
SELECT P.ID,Code,P.Name, getAllCategories(P.ID) AS AllCategories FROM
products p
The function 'getAllCategories(@.Pid integer)' has to get all Categories
with your query , concatenates the names and returns the concatenated
string.
Hope this helps
Olli
Jimmy Jim schrieb:

> Hi, I have a question concerning joining three tables together, and
> getting the information to spit out correctly. Say I have three
> tables:
> products
> ID Code Name
> 1 9000 Test Product
>
> categories
> ID Name
> 1 Test Category
> 2 Another Test
> 3 Yet another test
> productcategories
> ID Category Product
> 1 1 1
> 2 2 1
> 3 3 1
> Now I want SQL to send back product ID, Code, Product Name, and all
> categories it is with. So for example, one row would be:
> ID Code Name Categories
> 1 9000 Test Product Test Category,Another Test,Yet
> another test
> Here is the SQL I am using. All it is doing is pulling one category
> for now. I don't know how to group by and do a concatenation of all
> the categories.
> SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
> productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
> (C.ID=PC.Name) GROUP BY P.ID
> Any help in this would be appreciated. I am still relatively new to
> the complexity of GROUP BYs in SQL.
> Thanks for all the help!
> JJ|||Thanks for teh reply. Yeah, I tought it was going to be tough with SQL,
and I am using MySQL, so I can't use subqueries like I watned to. I
ended up creating something like a view and using that in PHP/MySQL. Not
as efficient, but all I could do.
Thanks again for the response!
JJ
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

JOIN question

Hi, I have a question concerning joining three tables together, and
getting the information to spit out correctly. Say I have three
tables:
products
ID Code Name
1 9000 Test Product
categories
ID Name
1 Test Category
2 Another Test
3 Yet another test
productcategories
ID Category Product
1 1 1
2 2 1
3 3 1
Now I want SQL to send back product ID, Code, Product Name, and all
categories it is with. So for example, one row would be:
ID Code Name Categories
1 9000 Test Product Test Category,Another Test,Yet
another test
Here is the SQL I am using. All it is doing is pulling one category
for now. I don't know how to group by and do a concatenation of all
the categories.
SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
(C.ID=PC.Name) GROUP BY P.ID
Any help in this would be appreciated. I am still relatively new to
the complexity of GROUP BYs in SQL.
Thanks for all the help!
JJ
Hi,
I think pure SQL will not be sufficient in this case as far as text
(categories.names) shall be aggregated (concatenated). 'Group by'
returns just one member of the (each) group. If the select list contains
an aggregate function, 'group by' will aggregate over all group
records . So, if a aggregate function e.g. 'concatenateText' would
exist, it could be used here. As far as I know it does not exist.
Therefore a function could be used doing the concatenation.
Then the query would be like:
SELECT P.ID,Code,P.Name, getAllCategories(P.ID) AS AllCategories FROM
products p
The function 'getAllCategories(@.Pid integer)' has to get all Categories
with your query , concatenates the names and returns the concatenated
string.
Hope this helps
Olli
Jimmy Jim schrieb:

> Hi, I have a question concerning joining three tables together, and
> getting the information to spit out correctly. Say I have three
> tables:
> products
> ID Code Name
> 1 9000 Test Product
>
> categories
> ID Name
> 1 Test Category
> 2 Another Test
> 3 Yet another test
> productcategories
> ID Category Product
> 1 1 1
> 2 2 1
> 3 3 1
> Now I want SQL to send back product ID, Code, Product Name, and all
> categories it is with. So for example, one row would be:
> ID Code Name Categories
> 1 9000 Test Product Test Category,Another Test,Yet
> another test
> Here is the SQL I am using. All it is doing is pulling one category
> for now. I don't know how to group by and do a concatenation of all
> the categories.
> SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
> productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
> (C.ID=PC.Name) GROUP BY P.ID
> Any help in this would be appreciated. I am still relatively new to
> the complexity of GROUP BYs in SQL.
> Thanks for all the help!
> JJ
|||Thanks for teh reply. Yeah, I tought it was going to be tough with SQL,
and I am using MySQL, so I can't use subqueries like I watned to. I
ended up creating something like a view and using that in PHP/MySQL. Not
as efficient, but all I could do.
Thanks again for the response!
JJ
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

JOIN Process Order and Performance Comparisons

Hi all,
A common SQL that I do is joining parent and child tables together (1-M
relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
number of rows.
Q1) Compare the two statements (that give the same result) below, from a
programming point of view, which one is more efficient?
Statement 1
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
AND IvoItm.ProductType = 1 --This line is processed in WHERE.
Statement 2
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
AND IvoItm.ProductType = 1 --This line is processed in JOIN.
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
This is something which I have been wondering for quite sometime. After
reading MSDN article "Join Fundamentals" stating, it says the JOIN statement
s
are processed first.
Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
process the JOIN?
Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm,
before doing the JOINS? Therefore improving performance, as the amount of
data to join is reduced in the IvoItm?
Q4) Using the same analogy in Q3, would there be performance gain if I
rewrite the statement using sub-query to do the filtering 1st?
SELECT *
FROM Invoice Ivo
INNER JOIN (
SELECT *
FROM InvoiceItem
WHERE ProductType = 1 --This line is processed in sub-query.
) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
Q5) And would above be efficient than using the Statement 1 and 2?Answers inline:
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:3E92F8CB-3E8A-4A4C-A0D9-71B707658D1A@.microsoft.com...
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
These two statements are probably going to perform equivalently, as they are
mathematically equivalent. If this was an outer join, then it will make a
difference. First thing to do is to check the plan using Query analyzer. It
should be the exact same plan.
Logically, all of the JOIN operators will be dealt with, building the set
with Invoice.* + InvoiceLineItem, eliminating rows where the join criteria
fails. Then for every row in the output you apply the where clause.
However, the optimizer can can reorganize the query to make it execute
better as long as the same results would be the same.
For the rest of your questions, try looking at the plan first. It may answer
the questions for you as the answers may be different based on the number of
rows in each table.

> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN
> statements
> are processed first.
>
This is true logically, but it is not required if the results are the same

> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in
> IvoItm,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?|||As noted by Louis, for Inner Joins it doesn't matter whether you specify
the predicates in the WHERE clause or in the JOIN ON clause. For Outer
Joins the meaning is different.
What the query optimizer will do, is analyse which indexes your tables
have and whether they can be sed or scanned in order to reduce the
I/O needed to retrieve the actual data. Then it will do an access path
analysis to see in which order the joins would be fastest. If there is
an appropriate index, then physically, the (partial) filtering will
occur before the join.
Suppose you have a clustered index on Invoice(Date). Then you will
probably see a clustered index s on table Invoice, regardless whether
you used syntax 1 or 2. BTW: the only way to really tell is check the
query plan.
HTH,
Gert-Jan
HardKhor wrote:
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN stateme
nts
> are processed first.
> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm
,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?

Join Opinion

What is the better practice when joining tables in a query example 1 or 2? I've always joined tables together like example 2, but it seems that many people are advocates of example 1. Are there any differences performance wise between the two?

Example 1:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
ORDER BY a.au_lname DESC

Example 2:
SELECT *
FROM authors AS a, publishers AS p
WHERE a.city = p.city
ORDER BY a.au_lname DESCBoth examples are executionally identical.|||I find the first way helps against inadvertant cross joins. Also, it is a lot easier to do the outer joins in the first method.|||Both examples are executionally identical because the optimizer converts the second syntax to the first syntax, at least for simple queries such as your example. For more complex queries it is possible that the optimer will not be able to do this, and then the first syntax is preferable.

blindman|||For both queries you posted, I do not think there is a different performance between them, but INNER JOIN is a really good way to be used in a query statement. It also can be used in the WHERE clause.|||ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...|||Brett, there's no need to get all ansi about it! :D|||Brett, tell me how you really feel about it LOL|||Well,

You could probably still use...

SELECT *
FROM Orders o, [Order Details] d
WHERE o.Orderid =* d.Orderid

Then there Oracle

SELECT *
FROM Orders o, [Order Details] (+) d
WHERE o.Orderid = d.Orderid

Or Informix

SELECT *
FROM Orders o, [Order Details] d
WHERE o.Orderid = d.Orderid(+)

Ya gotta love standards...

But as far as performance goes...howzabout for nonexistance

SELECT * FROM Orders o LEFT JOIN [Order Details] d
WHERE o.OrderId = d.OrderId AND d.OrderId IS NULL

instead on NOT EXISTS or NOT IN...|||You will never regret using ANSI syntax when you go back to do maintenance.

Separating the JOIN conditions from the WHERE conditions will always make things much clearer six months down the road when you are asking yourself 'Now what the hell is this query/view doing??'

The ANSI syntax may be more verbose, but is always clearer to understand when you come back to it later.

Trust me, it grows on you. The more complex the queries you write, the more sense it makes. When you are writing a query like the example above...

SELECT *
FROM authors AS a, publishers AS p
WHERE a.city = p.city
ORDER BY a.au_lname DESC

...it doesn't make much difference, but when you write something like this piece of a view definition....

SELECT (huge select section left out for clarity)
FROM table_case AS cs
CROSS JOIN table_report_window AS rw
JOIN table_gbst_elm AS elm ON cs.casests2gbst_elm = elm.objid
JOIN table_close_case AS cc ON cc.last_close2case = cs.objid
LEFT OUTER JOIN table_ibm_machine_type AS mt ON cs.case2machine_type = mt.objid
LEFT OUTER JOIN table_atlas_dispatch AS ad ON cs.case2dispatch = ad.objid
LEFT OUTER JOIN table_user AS usr ON cs.case_owner2user = usr.objid
LEFT OUTER JOIN table_time_zone AS tmzn ON cs.case2time_zone = tmzn.objid
LEFT OUTER JOIN table_contract AS cont ON cs.entitlement2contract = cont.objid
LEFT OUTER JOIN table_act_entry AS ae200 ON ae200.act_entry2case = cs.objid
AND ae200.entry_time = (SELECT MAX(aecsq1.entry_time)
FROM table_act_entry AS aecsq1
WHERE aecsq1.act_code = 200
AND aecsq1.act_entry2case = cs.objid)
AND ae200.act_code = 200
LEFT OUTER JOIN table_act_entry AS ae92002 ON ae92002.act_entry2case = cs.objid
AND ae92002.entry_time = (SELECT MIN(aecsq2.entry_time)
FROM table_act_entry AS aecsq2
WHERE aecsq2.act_code = 92002
AND aecsq2.act_entry2case = cs.objid)
AND ae92002.act_code = 92002
LEFT OUTER JOIN table_atlas_part_order AS po1 ON po1.part_order2dispatch = cs.case2dispatch
AND po1.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq1
WHERE pocsq1.part_order2dispatch = cs.case2dispatch)
LEFT OUTER JOIN table_atlas_part_order AS po2 ON po2.part_order2dispatch = cs.case2dispatch
AND po2.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq2
WHERE pocsq2.part_order2dispatch = cs.case2dispatch
AND pocsq2.objid > (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq3
WHERE pocsq3.part_order2dispatch = cs.case2dispatch))
LEFT OUTER JOIN table_demand_hdr AS hdr on hdr.caseinfo2case = cs.objid
WHERE cc.close_date BETWEEN rw.start_date AND rw.end_date
UNION
SELECT (huge select section left out for clarity)
FROM table_demand_hdr AS hdr
CROSS JOIN table_report_window AS rw
JOIN table_case AS cs ON hdr.caseinfo2case = cs.objid
JOIN table_gbst_elm AS elm1 ON cs.calltype2gbst_elm = elm1.objid
JOIN table_gbst_elm AS elm2 ON cs.casests2gbst_elm = elm2.objid
LEFT OUTER JOIN table_ibm_machine_type AS mt ON cs.case2machine_type = mt.objid
LEFT OUTER JOIN table_atlas_dispatch AS ad ON cs.case2dispatch = ad.objid
LEFT OUTER JOIN table_user AS usr ON cs.case_owner2user = usr.objid
LEFT OUTER JOIN table_time_zone AS tmzn ON cs.case2time_zone = tmzn.objid
LEFT OUTER JOIN table_contract AS cont ON cs.entitlement2contract = cont.objid
LEFT OUTER JOIN table_act_entry AS ae200 ON ae200.act_entry2case = cs.objid
AND ae200.entry_time = (SELECT MAX(aecsq1.entry_time)
FROM table_act_entry AS aecsq1
WHERE aecsq1.act_code = 200
AND aecsq1.act_entry2case = cs.objid)
AND ae200.act_code = 200
LEFT OUTER JOIN table_act_entry AS ae92002 ON ae92002.act_entry2case = cs.objid
AND ae92002.entry_time = (SELECT MIN(aecsq2.entry_time)
FROM table_act_entry AS aecsq2
WHERE aecsq2.act_code = 92002
AND aecsq2.act_entry2case = cs.objid)
AND ae92002.act_code = 92002
LEFT OUTER JOIN table_atlas_part_order AS po1 ON po1.part_order2dispatch = cs.case2dispatch
AND po1.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq1
WHERE pocsq1.part_order2dispatch = cs.case2dispatch)
LEFT OUTER JOIN table_atlas_part_order AS po2 ON po2.part_order2dispatch = cs.case2dispatch
AND po2.objid = (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq2
WHERE pocsq2.part_order2dispatch = cs.case2dispatch
AND pocsq2.objid > (SELECT MIN(objid)
FROM table_atlas_part_order AS pocsq3
WHERE pocsq3.part_order2dispatch = cs.case2dispatch))
WHERE elm2.title = 'Cancel'
AND hdr.x_req_type = 'Vendor Exchange'
AND elm1.title = 'Customer'
AND cs.modify_stmp BETWEEN rw.start_date AND rw.end_date

... with multiple and cascaded correlated subqueries on outer joined tables, and multiple conditions on a single join, you will be VERY happy you used the ANSI syntax when it's time to make changes.

Once you start joining several tables, it becomes difficult to tell where the join conditions end and the conditions that actually select the rows begin - and that's assuming you didn't intermix them. In the big unioin above, it's easy to see the two different selection criteria that are applied to what are in fact two very similar joins (they only differ by one table), but you don't need a union to make this separation of join conditions and where conditions handy. Get in the habit now.