EasyCFM.COM ColdFusion Forums / ColdFusion Applications / Displaying data in more than one category

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Displaying data in more than one category

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

ctopping1
10-04-2006 @ 11:44 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 18
Joined: Oct 2006

Hello All,

I have two MS Access tables, Table 1 contains the detailed information and Table 2 contains to categories for Table 1. Here is what I am trying to accomplish. In Table 1 "ListID" is joined to Table 2. So "listID" with a value of 1 is Category 1 in Table 2. If a record in Table 1 has more than one category, how do I display the data, say for Category 1 and Category 3 from Table 2? Can the "listID" in Table 1 have more than one value (e.g. 1,2)?


megan
10-04-2006 @ 11:51 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

yes, in your query, you can select
where columnname in (1,2,3)
or if the list is text item
where columnname in ('bird', 'cat', 'dog')

hth ~megan

We have art so that we shall not die of reality ~ Nietzsche

ctopping1
10-05-2006 @ 10:56 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 18
Joined: Oct 2006

Can I have more than one ID in the ACCESS database for Table 2?

megan
10-05-2006 @ 12:08 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 2398
Joined: Jan 2003

ok so if you want items in table 1 to have more than one category (table 2) you will need a third connectory table that contains two foreign keys one for the item id (table one) and one for the category id (table 2) and you would then query for all the categories where itemid = whatever or all the items where category id = whatever.

hth  Megan

We have art so that we shall not die of reality ~ Nietzsche

weathermanfsu
10-05-2006 @ 4:48 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 183
Joined: Apr 2004

or you can have more than one record in table 2, 1 for each category.  

Prime example is a travel reimbursement app I wrote.  5 day trip that has a trip id of 1000  Well there are 2 tables, 1 with the main trip info, and another with the trip details.  each day can be asigned to a different cost code.  So I can say day 1 is cost code 256, day 2 is 245, day 3 iis 678...

Then I can query the database and say give me all records associated with trip 1000 or I can say give me all records associated with a certain cost code.  If I wanted one day to have multiple cost codes, I can have 2 records in the details table for the same day and have the cost code id be different.  

The trip ID from the main table is also in each record in the details table.

tjrockjockwv
11-16-2006 @ 12:34 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 49
Joined: May 2006

here's an idea to try.
(i'll use an invoice example)
tblorders
- orderID     int     4     
- strCustID     int     4     
- crtID     varchar     255     1
- orderSubTotal     smallmoney     4     
- orderDiscount     smallmoney     4     
- orderShipping     smallmoney     4     
- orderTax     smallmoney     4     
- orderTotal     smallmoney     4     

tblorderdetails
- ordDetailID     int     4     
- strCustID     numeric     9     
- orderID     numeric     9     
- crtID     varchar     255     
- productID     numeric     9     
- productname     varchar     255     
- prodquant     numeric     9     
- prodUnitPrice     numeric     9     
- prodtotal     numeric     9     1

Then to build the item list on the invoice a query would be something like:
<!--- Hypothetical Order ID ---->
<cfset orderID = 20204>

SELECT     a.*, b.*, c.*
FROM         tblorders a INNER JOIN
                      tblorderdetails b ON a.orderID = b.orderID INNER JOIN
                      tblproducts c ON b.productID = c.productID
WHERE     (a.orderID = #orderID#)


The above query also had calls into an item options table to pull out selected options that a customer selected during checkout. But I removed it as to not add too much into the mix.

I know its two different scenarios (invoice vs. trip data).  but the principles are there for an example.

post up if you need more assistance.

glad to help


Website Designed and Developed by Pablo Varando.