SQL OR CF loops?

[Damnit Spock!]

I'm hoping someone that actually knows what they are doing can assist me. I
have a CFLOOP that simply spits out the contents of a table. Problem is, the
table contains integer references to other tables, those other tables
containing that actual text references for the integers. I need to display the
TEXT, not the integer value in the loop table, in the loop output. I can do it
by inserting seperate queries inside the loop that take the integer value and
retrieve the text based on the value from another table, but that'w allot of
overhead when the loop generates thousands of rows (4x for each row, plus the
initial query). Is there a way to do a query, either with SQL or in a CF loop,
that: SELECT * FROM looptable BUT reference the itemVALUE in another table,
based on the itemID in the loop table? I have 4 such itemID hits in the loop
that need the text associated with the itemID pulled from 3 other tables. Many
thanks.

[SafariTECH]

What DB are you using? You can do it 2 ways : 1. In the actual CFQUERY use
joins to query all the required data, which will then provide you with the text
from the joined tables 2. If possible, create a custom view in the database
itself that cross links all the related data. You can then query against this
pre-created view in the CF app as opposed to creating all the joins manually in
CF. Both techniques actually do the same thing, but (i.e.. MS SQL server) the
DBMS will be able to do much of it faster and with less overhead than having CF
do it in the fly. Both techniques result in less CF overhead than embedded
looped queries. If you have access to the actual database, I find it much
easier to do the View option since many DBMS have wizards to help you create
joins through it rather than trying to figure it out on your own.

An example of join SQL in either case looks something like:

SELECT dbo.Events.Event, dbo.CustomOrderZ.Event_ID,
dbo.CustomOrderZ.EventDate, dbo.CustomOrderZ.Options, dbo.CustomOrderZ.People,
dbo.CustomOrderZ.Extra, dbo.CustomOrderZ.Response,
dbo.CustomOrderZ.ResponseFile, dbo.CustomOrderZ.Status_Sub_Date,
dbo.CustomOrderZ.Status_Res_Date,
dbo.CustomOrderZ.Status_Fin_Date, dbo.CustomOrderZ.InvoiceFile,
dbo.CustomOrderZ.PriceTag,
dbo.CustomOrderZ.PayPalStatus, dbo.MemberZ.FirstName,
dbo.MemberZ.EmailAddress, dbo.MemberZ.LastName, dbo.MemberZ.Street1,
dbo.MemberZ.Street2, dbo.MemberZ.City, dbo.MemberZ.Prov,
dbo.MemberZ.Postal, dbo.MemberZ.DayPhone, dbo.MemberZ.EvePhone,
dbo.CustomOrderZ.MemberID, dbo.CustomOrderZ.CustomID,
dbo.CustomOrderZ.OverallStatus
FROM dbo.CustomOrderZ INNER JOIN
dbo.Events ON dbo.CustomOrderZ.Event_ID =
dbo.Events.KeyID INNER JOIN
dbo.MemberZ ON dbo.CustomOrderZ.MemberID =
dbo.MemberZ.MemID


In this example, the Events, CustomOrderZ, and MemberZ tables are all
INNER-JOINED so relevant data is pulled from each table per query.

[Damnit Spock!]

I'm using MS SQL. Never done VIEWS, guess I should learn though from the way
your post makes them look. For now, can you help with the join syntax? I can
get one join to work, but multiple? Here is the initial query: <CFQUERY
datasource='#DSN#' name='getInventory'> SELECT * FROM tblINVENTORY WHERE VENDER
!= 154 ORDER BY FLOORTYPE, VENDER, DESCRIPTIONI, DESCRIPTIONII, COLOUR
</CFQUERY> This returns a nice list that needs to reference other tables to
retrieve the names for the FLOORTYPE, VENDER, DESCRIPTIONI, DESCRIPTION II, and
COLOUR. The tables for those elements are: tblFLOORTypes tblVENDERValues
tblDESCRIPTIONIValues tblDESCRIPTIONIIValues tblCOLOURValues For instance,
within the output of the cfloop, I use: <CFQUERY datasource='#DSN#'
name='getFLOORName'> SELECT itemValue FROM tblFLOORTypes WHERE itemID =
#FLOORTYPE# </CFQUERY> UGLY, I know! :) newbie at work. In the initial
query, the retured values are names 'itemID'. The other tables contain the
'itemID' and the needed 'itemValue'. Thanks. And if you've got any tips or
great resources on VIEWS, that's be amazing.

[Jon Robertson]

Damnit Spock! wrote:

> I'm hoping someone that actually knows what they are doing can assist

me. I

> have a CFLOOP that simply spits out the contents of a table. Problem

is, the

> table contains integer references to other tables, those other tables

> containing that actual text references for the integers. I need to

display the

> TEXT, not the integer value in the loop table, in the loop output. I

can do it

> by inserting seperate queries inside the loop that take the integer

value and

> retrieve the text based on the value from another table, but that'w

allot of

> overhead when the loop generates thousands of rows (4x for each row,

plus the

> initial query). Is there a way to do a query, either with SQL or in

a CF loop,

> that: SELECT * FROM looptable BUT reference the itemVALUE in

another table,

> based on the itemID in the loop table? I have 4 such itemID hits in

the loop

> that need the text associated with the itemID pulled from 3 other

tables. Many

> thanks.

Have you heard of joins?

JR

[SafariTECH]

Are you using a MS SQL database?

[Damnit Spock!]

Yes, MS SQL 2000. I just tried this, which was built by the query builder in MS
SQL, which is an inner join on the 4 tables in question, but it trims the
results from 126 down to about 50. SELECT tblINVENTORY.ITEMID,
tblINVENTORY.STORE, tblFloorTypes.itemValue AS FLOORTYPE,
tblVENDERValues.itemValue AS VENDER,
tblDESCRIPTIONIValues.itemValue AS DESCRIPTIONI,
tblDESCRIPTIONIIValues.itemValue AS DESCRIPTIONII, tblINVENTORY.QTY,
tblINVENTORY.UNIT, tblINVENTORY.COST, tblINVENTORY.LANDED,
tblINVENTORY.LOCATION FROM tblINVENTORY INNER JOIN
tblFloorTypes ON tblINVENTORY.FLOORTYPE = tblFloorTypes.itemID INNER JOIN
tblVENDERValues ON tblINVENTORY.VENDER =
tblVENDERValues.itemID INNER JOIN tblDESCRIPTIONIIValues
ON tblINVENTORY.DESCRIPTIONII = tblDESCRIPTIONIIValues.itemID INNER JOIN
tblDESCRIPTIONIValues ON tblINVENTORY.DESCRIPTIONI =
tblDESCRIPTIONIValues.itemID For some reason the inner join is excluding
records?

[paross1]

Maybe this is what it might look like with everything in a single query.

Phil



<CFQUERY datasource="#DSN#" name="getInventory">
SELECT f.itemValue AS FloorType_value,
v.itemValue AS Vender_value,
d.itemValue AS DescriptionI_value,
di.itemValue AS DescriptionII_value,
c.itemValue AS Colour_value
FROM tblINVENTORY i,
tblFLOORTypes f,
tblVENDERValues v,
tblDESCRIPTIONIValues d,
tblDESCRIPTIONIIValues di,
tblCOLOURValues c
WHERE i.floortype = f.itemID
AND i.vender = v.itemID
AND i.descriptioni = d.itemID
AND i.descriptionii = di.itemID
AND i.colour = c.itemID
AND i.vender != 154
ORDER BY 1, 2, 3, 4, 5
</CFQUERY>

[Damnit Spock!]

Gotta love the query builder. I right clicked on the join lines, chose 'select
all items from tblINVENTORY' (which is the main table), and it converted the
inner join to a left outer join - presto, all 12500 results (126 was the beta
tester). So, thank you very much, you've been a big help, I obvisouly need to
read up on SQL. Still very interested in this view business though, it sounds
as if I can take a huge load of the CF engine by using the SQL engine?

[SafariTECH]

I see you got caught up in the same error I did in another loop If you save
the Query in SQL with a name, this is technically a 'custom view' and you
should be able to use it in your query: SELECT * FROM [thecustomviewyousaved]
so long as the fields you request and use in ordering, criteria, and selection
etc match those found in the query, it should work.

[Damnit Spock!]

So this would work with dynamic data?

[Damnit Spock!]

Is there special syntax for referencing a SQL SVR VIEW in the CF query, after the FROM clause?