Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Microsoft SQL Server 2000 Query Question

Posted on 2009-07-07
4
Medium Priority
?
214 Views
Last Modified: 2012-05-07
Hello!

I have a customer who has a book store online.  The books are arranged in the category trees such as:

Theology & History > Greco Roman Studies > Ancient Philosophy
Theology & History > Medieval > Manuscript Studies
Theology & History > Medieval > Byzantine Studies

They wanted it so that a book would be displayed in the category it was assigned to, plus any categories above.  Originally a book was only to fall into one category, but several years later, that has changed and they are now adding books to multiple categories and multiple book entries are showing up on the top level pages.

For example, if a book is in Ancient Philosophy and Manuscript Studies, it will show up twice under Theology & History.  

I currently have the books in a table:

Books2

Important Fields:
ID: Unique ID, Primary Key
manam: Authors Name
Approval: Is the book approved for display (1=yes, 0=no)
CataDate: Catalog Date, If the CataDate is less than today, then display the book
Private: Is the book Private (1=yes and dont display and 0=no and display)

BCJoin

Important Fields
BookID: Books2.ID
CatID: The ID of the Category in the Category Tree

SQL is not my strong suit, I know enough to get by, but Im assuming that there is a way to pull all the books in the top category and all sub-categories while being able to sort them by the Authors Name and then the Book Title.

Here is the page in question for reference:

https://secure4.visionsweb.com/loomebooks/Store5.cfm?DCatID=194

And my SQL Code is below.

Thank you for your help!
Joel
Select *
From Books2, BCJoin
Where (BCJoin.CatID = 265 or BCJoin.CatID = 278 or BCJoin.CatID = 285 or BCJoin.CatID = 286 or BCJoin.CatID = 358 or BCJoin.CatID = 359 or BCJoin.CatID = 284 or BCJoin.CatID = 279 or BCJoin.CatID = 217 or BCJoin.CatID = 220 or BCJoin.CatID = 275) and Books2.ID = BCJoin.BookID and Books2.Approval = 1 and (Books2.CataDate <{d '2009-07-07'} or Books2.CataDate is Null) and (Dead is Null or Dead = 0) and Private = 0
Order by Books2.MANAM, Books2.TNAM

Open in new window

0
Comment
Question by:JoelVogt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 24797459
You're better off creating a mapping table.

Columns:
CatID  MapsTo

Example:
Midieval 101
Manuscript Studies 102
Byzantine Studies 103

Entries in table
102 101
103 101


when a book is searched for in midieval (101) find any entries in the table that map to it (select CatID from mapping where mapsto=101)

Then get books for those categories as well.
0
 
LVL 2

Accepted Solution

by:
shoppedude earned 2000 total points
ID: 24797532
Instead of using:

Select *

Have you tried using:

Select distinct Books2.*

-Lee
0
 

Author Comment

by:JoelVogt
ID: 24797697
Changing it to:

Select distinct Books2.*

Did it, but I had to change two field from text to varchar 5000 fields instead.

Thank you!
0
 

Author Closing Comment

by:JoelVogt
ID: 31600756
Thank you very much!!!  I appreciate your help!!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question