I think what you want is an inner join. An inner join gives you ONLY
the records in the Product Table that have matching ids in the Product
Options table.
An outer join will give you all the records in the Product Table but
only those in Product Options that with matching ids.
So if your tables look like this:
Product
_______
id type
1 boat
2 ship
3 plane
4 car
5 truck
Product Option
option_id option_product_id option
1 2 cargo
2 2 container
3 4 sedan
4 4 suv
6 null convertible
An inner join will return
Product_id product_name option_id option_product_id option
2 ship 1 2 cargo
2 ship 2 2
container
4 car 3 4 sedan
4 car 4 4 suv
An outer join will return
Product_id product_name option_id option_product_id option
1 boat null null null
2 ship 1 2
cargo
2 ship 2 2
container
3 plane null null null
4 car 3 4
sedan
4 car 4 4 suv
5 truck null null null
On Sat, Apr 23, 2011 at 4:58 PM, Jenny Gavin-Wear
<[email protected]> wrote:
>
> Hi Maureen,
>
> Many thanks for the reply.
>
> Taking the first two queries.
>
> In Product I search on title, short description and long description.
> In Product Options I search on the option title and description.
>
> I tried some tests joining the tables:-
>
> <cfquery dbtype="query" name="product">
> select stockID
> from stockTable
> left outer join stockItemsTable ON stockItemsTable.stockID =
> stockTable.stockID
> </cfquery>
>
> I assume I need to use a left outer join. Although the stocktable and
> stockitemstable individually returned results, I can't get the combined
> tables to produce anything.
>
> As I understand it FREETEXTTABLE can only search on one table at a time?
>
> I can't use a database view in the fulltext catalogue? If I can it answers
> all my problem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:343928
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
