Re: A graphic of the db relationship

2005-01-17 Thread Protoculture 
Thanks Mike. I will consider your suggestions about putting all the data into 
one table. I did have a look at that database you mention and the layout was 
compelling.

However, if the querying would still cause me headaches I would be no further 
ahead. Behind in fact, because I've had to redesign the application to fit to 
the new db structure.

btw tried your query adn still no luck.

Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver]
 Syntax error (missing operator) in query expression '(A.id = S1.category_id)
INNER JOIN auction_item_categories_sub2 S2
ON (S1.id = S2.category_id)
INNER JOIN auction_item_categories_sub3 S3
ON (S2.id = S3.cateogyr_id)
INNER JOIN auction_item_categories_sub4 S4
ON (S3.id = S4.category_id)
INNER JOIN auc'.



 From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] 

I forgot to change something when copy/pasting.

SELECT
   A.name
FROM
   auction_item_categories A
   INNER JOIN auction_item_categories_sub S1 
   ON (A.id = S1.category_id)
   INNER JOIN auction_item_categories_sub2 S2 
   ON (S1.id = S2.category_id)
   INNER JOIN auction_item_categories_sub3 S3 
   ON (S2.id = S3.cateogyr_id)
   INNER JOIN auction_item_categories_sub4 S4 
   ON (S3.id = S4.category_id)
   INNER JOIN auction_item_categories_sub5 S5 
   ON (S4.id = S5.category_id)
WHERE
   A.id = 1

Sorry about that..

Mike

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190698
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: A graphic of the db relationship

2005-01-17 Thread Micha Schopman 
Why don't you create a many - many relationship if you want to use
multiple categories? 

Table: auction_item
Table: auction_category
Table: item_category

You only have to use 2 joins here, one join for getting category data,
and one for getting category relations.

SELECT DISTINCT AI.Label, AC.Label AS categoryLabel
FROM Items AI
JOIN ItemCategory IC ON (IC.ItemID = AI.ItemID)
JOIN Categories AC ON (AC.CategoryID = IC.CategoryID)


Micha Schopman
Software Engineer

Modern Media, Databankweg 12 M, 3821 AL  Amersfoort
Tel 033-4535377, Fax 033-4535388
KvK Amersfoort 39081679, Rabo 39.48.05.380



~|
Logware: a new and convenient web-based time tracking application. Start 
tracking and documenting hours spent on a project or with a client with Logware 
today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190703
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: A graphic of the db relationship

2005-01-17 Thread Umer Farooq 
Try this..

FROM auction_item_categories AS A INNER JOIN 
auction_item_categories_sub AS S1 ON A.id = S1.category_id) INNER JOIN 
auction_item_categories_sub1 AS S2 ON S1.id = S2.category_id) INNER JOIN 
auction_item_categories_sub2 AS S3 ON S2.id = S3.category_id) INNER JOIN 
auction_item_categories_sub3 AS S4 ON S3.id = S4.category_id) INNER JOIN 
auction_item_categories_sub4 AS S5 ON S4.id = S5.category_id

Also in your initial post the join was fine.. but you had the tables in 
FROM... and that was likly your cause.. did you try just using the 
Access Query tool to build you the query.

but as it has already been pointed out.. it would be better to switch to 
one table.

Protoculture wrote:
 Thanks Mike. I will consider your suggestions about putting all the data into 
 one table. I did have a look at that database you mention and the layout was 
 compelling.
 
 However, if the querying would still cause me headaches I would be no further 
 ahead. Behind in fact, because I've had to redesign the application to fit to 
 the new db structure.
 
 btw tried your query adn still no luck.
 
 Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver]
  Syntax error (missing operator) in query expression '(A.id = S1.category_id)
 INNER JOIN auction_item_categories_sub2 S2
 ON (S1.id = S2.category_id)
 INNER JOIN auction_item_categories_sub3 S3
 ON (S2.id = S3.cateogyr_id)
 INNER JOIN auction_item_categories_sub4 S4
 ON (S3.id = S4.category_id)
 INNER JOIN auc'.
 
 
 
 
From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] 

I forgot to change something when copy/pasting.

SELECT
  A.name
FROM
  auction_item_categories A
  INNER JOIN auction_item_categories_sub S1 
  ON (A.id = S1.category_id)
  INNER JOIN auction_item_categories_sub2 S2 
  ON (S1.id = S2.category_id)
  INNER JOIN auction_item_categories_sub3 S3 
  ON (S2.id = S3.cateogyr_id)
  INNER JOIN auction_item_categories_sub4 S4 
  ON (S3.id = S4.category_id)
  INNER JOIN auction_item_categories_sub5 S5 
  ON (S4.id = S5.category_id)
WHERE
  A.id = 1

Sorry about that..

Mike
 
 
 

~|
Logware: a new and convenient web-based time tracking application. Start 
tracking and documenting hours spent on a project or with a client with Logware 
today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190718
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

Re: A graphic of the db relationship

2005-01-17 Thread Adam Howitt 
If you take Jochem's working query that gave no results you should 
consider changing the INNER JOINs to LEFT JOINs since this will find 
matching rows in the first table you list and then matching rows from 
the tables on the right of the LEFT JOIN.  If there is no match you see 
a NULL for any values you are trying to get.  Then you can concatenate 
the category names as you go using s1.name  .  s2.name etc..

Protoculture wrote:

Thanks Mike. I will consider your suggestions about putting all the data into 
one table. I did have a look at that database you mention and the layout was 
compelling.

However, if the querying would still cause me headaches I would be no further 
ahead. Behind in fact, because I've had to redesign the application to fit to 
the new db structure.

btw tried your query adn still no luck.

Server Msg: -3100, State: 42000, [Microsoft][ODBC Microsoft Access Driver]
 Syntax error (missing operator) in query expression '(A.id = S1.category_id)
INNER JOIN auction_item_categories_sub2 S2
ON (S1.id = S2.category_id)
INNER JOIN auction_item_categories_sub3 S3
ON (S2.id = S3.cateogyr_id)
INNER JOIN auction_item_categories_sub4 S4
ON (S3.id = S4.category_id)
INNER JOIN auc'.



  

From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] 
  

I forgot to change something when copy/pasting.

SELECT
  A.name
FROM
  auction_item_categories A
  INNER JOIN auction_item_categories_sub S1 
  ON (A.id = S1.category_id)
  INNER JOIN auction_item_categories_sub2 S2 
  ON (S1.id = S2.category_id)
  INNER JOIN auction_item_categories_sub3 S3 
  ON (S2.id = S3.cateogyr_id)
  INNER JOIN auction_item_categories_sub4 S4 
  ON (S3.id = S4.category_id)
  INNER JOIN auction_item_categories_sub5 S5 
  ON (S4.id = S5.category_id)
WHERE
  A.id = 1

Sorry about that..

Mike





~|
Logware: a new and convenient web-based time tracking application. Start 
tracking and documenting hours spent on a project or with a client with Logware 
today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190731
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

A graphic of the db relationship

2005-01-16 Thread Protoculture 
Sorry Jochem you were right.

Anyway, to get things more concise here, perhaps I'm not even doing the right 
kind of join... here is my db structure...

http://www.webgoblins.net/db.gif

The Last Code I tried was Jochems and while it did run succesfully, there were 
no results returned. Essentially What I'm doing is selecting all related 
categories from the where all the ( auction_item_categories_sub.category_id = 1 
) and then it should filter down with the appropriate logic. The trouble is, 
I'm not having a great time finding that logic.

[CODE]
SELECT *
FROM    auction_item_categories_sub5 s5
INNER JOIN auction_item_categories_sub4 s4 ON s5.category_id  = 
s4.id)
INNER JOIN auction_item_categories_sub3 s3 ON s4.category_id = 
s3.id)
INNER JOIN auction_item_categories_sub2 s2 ON s3.category_id = 
s2.id)
INNER JOIN auction_item_categories_sub s ON s2.category_id = s.id)
INNER JOIN auction_item_categories c ON s.category_id = c.id
WHERE c.id = 1 

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190637
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: A graphic of the db relationship

2005-01-16 Thread Michael T. Tangorre 
 

 From: Protoculture [mailto:[EMAIL PROTECTED] 
 The Last Code I tried was Jochems and while it did run 
 succesfully, there were no results returned. Essentially What 
 I'm doing is selecting all related categories from the where 
 all the ( auction_item_categories_sub.category_id = 1 ) and 
 then it should filter down with the appropriate logic. The 
 trouble is, I'm not having a great time finding that logic.

I would recommend rethinking the DB structure. The fact that you have the
category hierarchy broken out into individual tables is not a good approach
in my opinion. It looks like you could benefit more from a set or tree
structure that would allow you to store the entire hierarchy in one table.
Google Joe Celko Nested Set and check it out. The code is out that for
selecting hierarchies, inserting a new node and removing nodes.

Given your current setup, remember thah the inner join will take care of
only filtering out records where there is no ID match (PK/FK).

Start with the top level...

SELECT
A.name
FROM
auction_item_categories A
INNER JOIN auction_item_categories_sub S1 ON (A.id = S1.category_id)
INNER JOIN auction_item_categories_sub2 S2 ON (S1.id =
S2.category_id)
INNER JOIN auction_item_categories_sub3 S3 ON (S2.id =
S3.cateogyr_id)
INNER JOIN auction_item_categories_sub2 S4 ON (S3.id =
S4.category_id)
INNER JOIN auction_item_categories_sub2 S5 ON (S4.id =
S5.category_id)
WHERE
A.id = 1

Give the above a try...

Mike



~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190639
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54

RE: A graphic of the db relationship

2005-01-16 Thread Michael T. Tangorre 
  From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] 

I forgot to change something when copy/pasting.

SELECT
A.name
FROM
auction_item_categories A
INNER JOIN auction_item_categories_sub S1 
ON (A.id = S1.category_id)
INNER JOIN auction_item_categories_sub2 S2 
ON (S1.id = S2.category_id)
INNER JOIN auction_item_categories_sub3 S3 
ON (S2.id = S3.cateogyr_id)
INNER JOIN auction_item_categories_sub4 S4 
ON (S3.id = S4.category_id)
INNER JOIN auction_item_categories_sub5 S5 
ON (S4.id = S5.category_id)
WHERE
A.id = 1

Sorry about that..

Mike



~|
Flash for programmers - Flash MX Pro
http://www.houseoffusion.com/banners/view.cfm?bannerid=56

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190640
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations  Support: http://www.houseoffusion.com/tiny.cfm/54