You need to do a left outer join -
<cfquery datasource="myfictionalstore" name="qGetData">
SELECT vendors.vendorid, vendors.vendor_name,products.product_name
FROM vendors LEFT OUTER JOIN products ON vendors.id = products.vendorid
ORDER BY vendorid
</cfquery>
Give that a shot.
Adam.
> -----Original Message-----
> From: Willy Ray [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, December 04, 2002 4:50 PM
> To: CF-Talk
> Subject: SOT: Query Trouble
>
>
> Ok, say I have a table of vendors and a table of products,
> and I want to
> list out all the vendors and their products:
>
> <cfquery datasource="myfictionalstore" name="qGetData">
> SELECT vendors.vendor_name,products.product_name
> FROM vendors, products
> WHERE vendors.id = products.vendorid
> ORDER BY vendorid
> </cfquery>
>
>
> Then:
>
>
> <cfoutput query="qGetData" group="vendorid">
> #qGetData.vendor_name#:
> <cfoutput>
> #qGetData.product_name#,
> </cfoutput>
> <br>
> </cfoutput>
>
>
> This gives me a good list of all my vendors, with a somewhat sloppy
> comma delimited list of their products after. But what if I have
> vendors who don't currently have products in the product table, and I
> want to list them anyway? It's like a, where-are-the-vendors and BTW,
> here-are-their-products kind of deal.
>
> Another way to put it. I have three vendors and the second one has no
> products, I need my list to look like this:
>
> Vendor A: mouse traps, cat food,
> Vendor B:
> Vendor C: applesauce, condensed milk,
>
> But the above query doesn't do that. Any help?
>
> Willy
>
>
>
> -----
> Willy Ray
> Web Applications Developer
> Certified Advanced ColdFusion Developer
> Westminster College
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
