SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Feb 2004
    Location
    australia
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    3 Table Join Output Problem

    Hello All,

    I am having a bit of trouble with the output of a 3 table join i am running. My database structure is as follows.

    Code:
    PROPTYPE
    ----------
    ID
    TypeName
    
    PROPERTIES
    -----------
    ID
    TypeID
    PropName
    Sold
    
    VENDORS
    -----------
    ID
    PropID
    Name
    Phone

    What i am trying to achieve is output the properties in the database that are SOLD grouped by the area they are located in. I have got this part ok. The next part is to show the 'vendor' name and phone number (if available) for each property. Here is the query i currently run.

    Code:
    <cfquery name="GetProps" datasource="myDSN">
    SELECT t.TypeName, p.PropName, v.Name, v.Phone
    FROM (PROPERTIES p INNER JOIN PROPTYPE t ON p.TypeID = t.ID)
    LEFT JOIN VENDORS v ON p.ID = v.PropID
    WHERE p.Sold = Yes
    </cfquery>
    
    
    <cfoutput query="GetProps" group="TypeName">
    
    <strong>#TypeName#</strong>
    	<ul>
    	<cfoutput>
    	  <li>#PropName# - #Name# - #Phone#</li>
    	</cfoutput>
    	</ul>
    
    </cfoutput>

    The output that i get is below, for some reason it outputs the property twice, but only shows the vendor details in the first occurence of the property

    Code:
    Alpine Properties
    
        * White Crystal Apartment #214 - Melanie Smith - 987 345
        * White Crystal Apartment #214 -
        * Alpine Heights Apartment #10 - Joe Bloggs - 555 123
        * Alpine Heights Apartment #10 -
    
    Coastal Properties
    
        * Findlay Street - 
        * Findlay Street -
        * Vacant Land - Fred Nurk - 777 342
        * Vacant Land -
    If anyone can offer an idea or suggestion on how to fix this it would be much appreciated.

    Nesbit

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


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
  •