query use of right outer join

[atomi]

hi.
My impression of right outer join for mssql was that it returned all rows of a table specifed to the right of the join.
But it isnt doing it for me.
i tried this

SELECT item, cost, quantity, category
FROM tbl_items
RIGHT OUTER JOIN tbl_categories ON tbl_items.category = tbl_categories.category
WHER item = 'item'

Ive spent hours. I need the query to return an array for all rows in tbl_categories ( i want to create a list of categories for html select option) and one row for the specified item.
Is this possible?
thanks!

[r937]

in your WHERE clause, you have item = 'item'

if this column is in the tbl_items table, then you in effect get an inner join

note furthermore that if your ON condition is correct, you have two columns named category, and therefore the query as given will give a syntax error (ambiguous column)

tip: always qualify your columns in a join

try this --

select I.item
     , I.cost
     , I.quantity
     , C.category
  from tbl_items as I
right outer 
  join tbl_categories as C
    on I.category 
     = C.category
   and I.item = 'item'

[atomi]

thanks for the reply, your query did give me all the categories but for each category I get a row from items table. When I output the result set it loops over the empty rows. I stayed up till 2 last night, and Im almost sure that acquiring a result set that produces all the categories but only one row for the item is not possible. I would break it into two queries but as you know its not possible to nest a cfoutput query into another cfoutput query. My only other option is to add the select option list after the query that outputs the item information. again thanks for the reply.

atomi

[r937]

your query did give me all the categories but for each category I get a row from items table.

isn't that what you wanted?

Im almost sure that acquiring a result set that produces all the categories but only one row for the item is not possible.

that depends if you want only one row or one row per category

as you know its not possible to nest a cfoutput query into another cfoutput query

actually, it is


perhaps you could give a few examples of rows in each table and show the results you want

[atomi]

okay thanks for the advice.
what I want is this: return a result set that produces the idkey and category columns for all rows in the categories table. I also need to get the item information for one item from the items table and cfoutput query it to a form

<form name="edititem" method="post">
   <input type="text" name="itemname" value="#query.itemname#">
   <input type="text" name="itemdescription" value="#query.itemdescription#">
   <input type="text" name="itemprice" value="#query.itemprice#">
   <select name="categories">
         <option value="#query.currentitemcategoryID#">#query.currentitemcategory#</option>
         <option value="#query.categoryID#">#query.category#</option> <--need to loop-->
    </select>
     <input type="submit">

As of now Im doing this using two queries - one after another, also how do you nest a cfoutput query inside another cfoutput query?
Well regardless, thanks alot.

[r937]

ah, i get it

you want only one item, and you also want all the categories

in fact, it appears likely that the item might not even have any categories (yet)

i would do this with two queries

nested CFOUTPUTs are possible if you use the GROUP= parameter of the CFOUTPUT tag on the outer ones, but this wouldn't apply in your situation

<CFQUERY NAME="getitem">
  select itemname 
       , itemdescription
       , itemprice
       , category
    from tbl_items
   where itemname = 'widget'
</CFQUERY>

<CFQUERY NAME="getcategories">
  select categoryID
       , category
    from tbl_categories
  order by category
</CFQUERY>

<form name="edititem" method="post">
<CFOUTPUT>
<input type="text" name="itemname" 
         value="#getitem.itemname#">
<input type="text" name="itemdescription" 
         value="#getitem.itemdescription#">
<input type="text" name="itemprice" 
         value="#getitem.itemprice#">
</CFOUTPUT>         
<select name="categories">
<CFOUTPUT QUERY="getcategories">
<option value="#getcategories.categoryID#"
  <CFIF getitem.category EQ getcategories.categoryID>
    selected="selected"
  </CFIF>
 >#getcategories.category#</option> 
</CFOUTPUT>         
</select>

note how the CFIF detects when the item has the category

if you need the item's category as the first option, that'll take a different bit of code

[atomi]

Thats almost exactly how I did it!
heh, I needed the items category as the first option like you say. I winged it in a way.

<form name="edititem" method="post">
<select name="category">
<cfoutput query="getcategories">
<option value="#categoryID#">#category#</option>
</cfoutput>
<cfoutput query="getitem">
<option value="#categoryID#" selected>#category#</option>
</select>
<input name="itemname" type="text" value="#itemname#">
<input name="itemname" type="text" value="#itemname#">
<input name="itemname" type="text" value="#itemname#">
<input type="submit" value="submit">
</cfoutput>

It gets the job done.
-atomi