I have tables ..
tblcolours ColourID Colour
tblItemColours ID FKItemID FKColourID
Queries
SELECT FKItemID, FKColourID FROM dbo.tblItemColours WHERE (FKItemID = 1)
(Brings back the four colours available for this item ok)
I want to get the colour as well for the drop down list so join the two tables instead
SELECT dbo.tblColours.Colour, dbo.tblItemColours.FKItemID, dbo.tblItemColours.FKColourID, dbo.tblColours.ColourID
FROM dbo.tblColours CROSS JOIN
dbo.tblItemColours
WHERE (dbo.tblItemColours.FKItemID = 1)
(Brings back everything in the table ...)
I think I have a joining problem (I never was any good at woodwork)
Regards - Paul
From: [EMAIL PROTECTED] Reply-To: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Subject: Re: [ cf-dev ] loop the loop loopiness Date: Fri, 16 Jul 2004 11:57:28 +0100
that explains a lot. one way you could get round this withouth changing your tables would be to use a de-duplicate function on the list. I think cflib.org should have something to do that.
but ideally you want to do this properly, with linker tables for a many-to-many relationship between colours and items, and again between sizes and items:
tblItems ID Name Description etc
tblColours ID Colour
tblSizes ID Size
tblItem_Colours ID itemID ColourID
tblItem_Sizes ID itemID SizeID
"Paul Swingewood"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
tmail.com> cc:
Subject: Re: [ cf-dev ] loop the loop loopiness
16/07/2004 11:54
Please respond to
dev
Tblitems....
No I cheated and did this.
NAME COLOUR SIZE Shoe1 - red,white,blue - 1,2,3,4,5,6,7,8 Shoe2 - Orange,purple - 5,7,9,10,11 etc etc
>From: [EMAIL PROTECTED]
>Reply-To: <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Subject: Re: [ cf-dev ] loop the loop loopiness
>Date: Fri, 16 Jul 2004 11:50:48 +0100
>
>
>Paul, does tblItems have a seperate row for each colour, and each size?
>
>e.g.
>Shoe 1, Red, size 3
>Shoe 1, Red, size 4
>Shoe 1, Red, size 5
>Shoe 1, Black, size 3
>Shoe 2, Black, size 3
>Shoe 2, Black, size 3
>
>etc etc?
>
>
>
>
> "Paul Swingewood"
> <[EMAIL PROTECTED] To:
>[EMAIL PROTECTED]
> tmail.com> cc:
> Subject: Re: [ cf-dev
]
>loop the loop loopiness
> 16/07/2004 11:47
> Please respond to
> dev
>
>
>
>
>
>The query is simple enough really .... (I would have thought this was all
>ok
>....)
>I still don't see how the select list gets appended to?
>
><CFQUERY NAME="GetCartItems" datasource="#application.dsn#">
> SELECT DISTINCT ItemIDPK, PartNum, ItemName,
>ItemCost,
>Quantity,
> tblitems.itemsize AS ItemsItemSize,
> tblitems.itemcolour AS ItemsItemColour,
> tblCartItems.ItemSize AS CartItemsItemSize,
> tblCartItems.ItemColour AS CartItemsItemColour
> FROM tblItems, tblCartItems
> WHERE itemID = ItemIDPK
> AND CartIDPK = '#Cookie.CartID#'
> </cfquery>
>
>
> >From: Steve Powell <[EMAIL PROTECTED]>
> >Reply-To: <[EMAIL PROTECTED]>
> >To: <[EMAIL PROTECTED]>
> >CC: Steve Powell <[EMAIL PROTECTED]>
> >Subject: Re: [ cf-dev ] loop the loop loopiness
> >Date: Fri, 16 Jul 2004 10:45:16 GMT
> >
> >Looks to me like you are getting a cross product effect in one of your
> >JOINS in a query and getting duplicate rows.
> >
> >Look at the query as the cause of the problem. Try a DISTINCT or
altering
> >the JOIN criteria
> >
> >regards
> >
> >Steve
> >
> >
> >
> >
> >
> >--
> >These lists are syncronised with the CFDeveloper forum at
> >http://forum.cfdeveloper.co.uk/
> >Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
> >
> >CFDeveloper Sponsors and contributors:-
> >*Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF
>provided
>
> >by activepdf.com*
> > *Forums provided by fusetalk.com* :: *ProWorkFlow provided by
> >proworkflow.com*
> > *Tutorials provided by helmguru.com* :: *Lists hosted by
> >gradwell.com*
> >
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >
>
>
>
>--
>These lists are syncronised with the CFDeveloper forum at
>http://forum.cfdeveloper.co.uk/
>Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
>CFDeveloper Sponsors and contributors:-
>*Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided
>by activepdf.com*
> *Forums provided by fusetalk.com* :: *ProWorkFlow provided by
>proworkflow.com*
> *Tutorials provided by helmguru.com* :: *Lists hosted by
>gradwell.com*
>
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>
>
>
>
>
>
>--
>These lists are syncronised with the CFDeveloper forum at
>http://forum.cfdeveloper.co.uk/
>Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
>
>CFDeveloper Sponsors and contributors:-
>*Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided
>by activepdf.com* > *Forums provided by fusetalk.com* :: *ProWorkFlow provided by >proworkflow.com* > *Tutorials provided by helmguru.com* :: *Lists hosted by >gradwell.com* > >To unsubscribe, e-mail: [EMAIL PROTECTED] >
-- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com*
To unsubscribe, e-mail: [EMAIL PROTECTED]
--
These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/
Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
CFDeveloper Sponsors and contributors:-
*Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com*
*Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com*
*Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com*
To unsubscribe, e-mail: [EMAIL PROTECTED]
-- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
CFDeveloper Sponsors and contributors:-
*Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by
activepdf.com*
*Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com*
*Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com*To unsubscribe, e-mail: [EMAIL PROTECTED]
