category & subcategory1, 2, 3, 4... help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

category & subcategory1, 2, 3, 4... help!

Author

Topic

tudor
Starting Member

5 Posts

Posted - 2008-03-16 : 13:06:50

hi -

i have created a database and have the following tables:

[category]
category_id
category_name

[subcategory1]
category_id
subcategory1_id
subcategory1_name

[subcategory2]
subcategory1_id
subcategory2_id
subcategory2_name

[subcategory3]
subcategory2_id
subcategory3_id
subcategory3_name

[subcategory4]
subcategory3_id
subcategory4_id
subcategory4_name

...now i have written the following SQL code to list the contents as follows:

==========
SELECT
book.book_id,
book.book_title,
book.category_id,
book.subcategory1_id,
book.subcategory2_id,
book.subcategory3_id,
book.subcategory4_id,
author.author_id,
author.author_name,
category.category_id,
category.category_title,
subcategory1.subcategory1_id,
subcategory1.subcategory1_title,
subcategory2.subcategory2_id,
subcategory2.subcategory2_title,
subcategory3.subcategory3_id,
subcategory3.subcategory3_title,
subcategory4.subcategory4_id,
subcategory4.subcategory4_title

FROM
book,
author,
category,
subcategory1,
subcategory2,
subcategory3,
subcategory4

WHERE
book.book_id = #URL.id#
AND
book.author_id=author.author_id
AND
book.category_id=category.category_id
AND
book.subcategory1_id=subcategory1.subcategory1_id
AND
book.subcategory2_id=subcategory2.subcategory2_id
AND
book.subcategory3_id=subcategory3.subcategory3_id
AND
book.subcategory4_id=subcategory4.subcategory4_id
==========

...now, if any of the category and subcategory fields are empty (which is very probable), then data is NOT displayed!

what code would i need to ignore the EMPTY fields and display whatever is in the database?

so for example, if we look at it from a breadcrumbs viewpoint as follows, if all the fields are NOT empty, then the breadcrumbs should display as follows:

category > subcategory1 > subcategory2 > subcategory3 > subcategory4

...but IF there are empty fields, such as subcategory3 and subcategory4, then it should display as follows:

category > subcategory1 > subcategory2

i hope i have been able to explain my problem and urther hope someone is able to assist me.

cheers in anticipation

;-D

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-16 : 15:37:21

You need to to do LEFT joins from the table you want to retrieve all records from.

Poor planning on your part does not constitute an emergency on my part.

tudor
Starting Member

5 Posts

Posted - 2008-03-16 : 22:12:20

quote:
You need to to do LEFT joins from the table you want to retrieve all records from.

wow! that's WAY above my head!!

i don't even know how to do a JOIN let alone a LEFT one!!

any further help will be MUCH appreciated

;-D

cheers dude

tudor
Starting Member

5 Posts

Posted - 2008-03-19 : 09:38:50

...can anyone help me on this;

i've looked around on the net about LEFT JOIN but it don't make no sense to me!

:-(

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-19 : 13:34:37

http://office.microsoft.com/en-us/access/HP010322511033.aspx

http://www.techonthenet.com/access/queries/joins2.php

- Jeff
http://weblogs.sqlteam.com/JeffS

tudor
Starting Member

5 Posts

Posted - 2008-03-28 : 05:07:06

...thanks for the help; i've finally managed to come up with the following after BIG problems, but i am now having problems with the way the categories are being selected!

when i select a category, lets say [kids] and then a subcategory1 of [adventure] and then a subcategory2 of [fiction[ etc, it will only display the FIRST value from each of those tables. i know i have to include some sort of WHERE clause at the end like i did with the book_id but i can't seem to get my head around it:

SELECT
	category.*,
        subcategory1.*,
        subcategory2.*,
        subcategory3.*,
        subcategory4.*,
        author.*,
        book.*

FROM
	((((((book
	LEFT JOIN category ON category.category_id=book.category_id)
	LEFT JOIN subcategory1 ON subcategory1.category_id=category.category_id)
	LEFT JOIN subcategory2 ON subcategory2.subcategory1_id=subcategory1.subcategory1_id)
	LEFT JOIN subcategory3 ON subcategory3.subcategory2_id=subcategory2.subcategory2_id)
	LEFT JOIN subcategory4 ON subcategory4.subcategory3_id=subcategory3.subcategory3_id)
	LEFT JOIN author ON author.author_id=book.author_id)

WHERE
	book.book_id = #URL.ID#

...any further assistance will be MUCH appreciated

;-D

cheers!

tudor
Starting Member

5 Posts

Posted - 2008-04-07 : 13:23:07

...cheers [dataguru1971] and [jsmith8858] for your guidance; i would dearly appreciate a little further guidance in finally completing the sql code to give me the correct corresponding subcategory1,2,3 and 4 rather than the first item in each of these tables (see my post above for further clarification).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources