SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query use of right outer join

    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
    Code:
    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!
    Links? I don't need no stinkin' links.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 --
    Code:
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Links? I don't need no stinkin' links.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by atomi
    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?

    Quote Originally Posted by atomi
    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

    Quote Originally Posted by atomi
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    <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.
    Links? I don't need no stinkin' links.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    <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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    call-e-for-nea
    Posts
    174
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    <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
    Links? I don't need no stinkin' links.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •