Webmaster Forum / ColdFusion / Database Access / July 2008

Something like this maybe?

<cfquery name = "your_query" ... etc>
SELECT c.courseDescription,
s.sessionID,
s.sessionStartDate,
s.sessionEndDate
FROM course c
INNER JOIN session s ON c.courseID = s.sessionCourse
ORDER BY c.courseDescription
</cfquery>

<cfoutput query = "your_query" group = "courseDescription">
#courseDescription#
<cfoutput>
#sessionID# #sessionStartDate# #sessionEndDate#
</cfoutput>
</cfoutput>

Phil

Thanks for all the help - I dont do nearly as much database stuff as I'd like!

When you want records from one table whether or not a record exists in
the second joined table you want an OUTER JOIN rather then an INNER JOIN.

One uses LEFT or RIGHT OUTER JOIN to say which table has the optional
data in it, the table to the left or the right of the join statement.

My guess would be that you want courses whether or not there is a course
session so something like this should be close.

SELECT ...
FROM ptcourses 08 c
LEFT OUTER JOIN coursesession s ON c.courseID = sessionCourse
...

Can't say why it's called 'inner' must be some kind of mathematical
thing.  But an inner join is a join between two tables where both tables
must have a matching record, or no record is returned.  So if you join
table A to table B on field A equals field B then there must be a record
in both table A and B with a matching value in field A and field B or
nothing is returned.

A lot of databases make the key word 'inner' optional and it can be
shortened to just JOIN.

IE. tableA INNER JOIN tableB ... is the same as tableA JOIN tableB ...

The former is considered more explicit and clear by some.

Thanks, but I should point out that I wrote this backwards.  The table
you specify with the LEFT or the RIGHT is the table that will always
return records whether or not there are matching records in the other
table.  I.E. the opposite table is the optional table.

[q][i]Originally posted by: [b][b]indnajns[/b][/b][/i]
"One uses LEFT or RIGHT OUTER JOIN to say which table has the optional data in
it, the table to the left or the right of the join statement."

FINALLY!  An explanation I can understand!  I have never understood the Right
vs Left concept until now.  Could you please explain Inner Join with the same
clarity?[/q]

Thats exactly what I thought! I've never understood explanations of this up
until now.

http://en.wikipedia.org/wiki/Join_(SQL)

[i]An inner join requires each record in the two joined tables to have a
matching record. An inner join essentially combines the records from two tables
(A and B) based on a given join-predicate. The result of the join can be
defined as the outcome of first taking the Cartesian product (or cross-join) of
all records in the tables (combining every record in table A with every record
in table B) - then return all records which satisfy the join predicate.[/i]

[i]The result of a left outer join (or simply left join) for tables A and B
always contains all records of the "left" table (A), even if the join-condition
does not find any matching record in the "right" table (B). This means that if
the ON clause matches 0 (zero) records in B, the join will still return a row
in the result?but with NULL in each column from B. This means that a left outer
join returns all the values from the left table, plus matched values from the
right table (or NULL in case of no matching join predicate).[/i]

Google is your friend.......

Phil

Yes as I replied this morning, my original post was mistyped and I had
reversed the relationship.

Your question of using a Join versus an Where A=B clause strongly speaks
to you being a user of Oracle databases.

The older Oracle syntax for joins used the where clause such as this:

SELECT ...
FROM aTable, bTable
WHERE aTable.aField = bTable.bField

One would use the astric[*] to desiginat outer joins.

SELECT ...
FROM aTable, bTable
WHERE aTable.aField* = bTable.bField
--LEFT OUTER JOIN--

SELECT ...
FROM aTable,bTable
WHERE aTable.aField = bTable.bField*
--RIGHT OUTER JOIN--

This is Oracle specific syntax and does not work in all databases.  The
SQL92 standard is to use the JOIN clause.  Newer Oracle databases now
support the standard.

So the main advantage to the JOIN syntax is that it is more universal.
I also find it easier and clearer when one starts having to join three
or more tables, because the JOIN ON makes it clearer what order to join
the tables together.  Especially when one uses parenthesis to specify
join order.

FROM
   aTable INNER JOIN
      (bTable INNER JOIN
         cTable ON bTable.key2 = cTable.key2)
      ON aTable.key1 = bTable.key1

when to use JOIN and when to use WHERE clause to link your tables can
also depend on how your db utilizes table indexes.
check your db reference manual on indexing and index optimization - it
may cover the topic of when to use JOIN and when to use WHERE clause to
join your tables (mysql ref manual does).

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[q][i]Originally posted by: [b][b]indnajns[/b][/b][/i]
Yes, but Ian said it in under 30 words.  30 words that made SENSE.  :)

And yes, after understanding your answer, I went out and started reading up on
Inner/Outer Joins and realized either I was reading it backwards or you had
mistyped.  Which is fine.  I GET IT NOW!  The next question is, is there a real
good reason to use Join over Where A=B?  Or is it just programmer preference?  
In particular, if one is joining three tables with several Joins/Where
conditions?[/q]
Some db's support this syntax, which saves a lot of typing.

from table1 join table2 using (field1, field2, etc)

Also if are updating one table from another, using join syntax is a lot more
efficient.