Many to many relationship / query dilemma

Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Many to many relationship / query dilemma

  1. #1
    Join Date
    Dec 1969
    Posts
    76

    Default Many to many relationship / query dilemma

    If I have 3 tables:<BR><BR>tblEvent:<BR>eventID (pk)<BR>eventName<BR><BR>tblCategory<BR>catID (pk)<BR>catName<BR><BR>tblXRefLookup<BR>prodID<BR> catID<BR><BR>A record in the Events table can have many categories, so that&#039;s expressed by mulitple rows in the Lookup table.<BR><BR>How can I query these tables, so that I can return rows that look like:<BR><BR>prodID, prodName, catID (1, 2, 3), catName (xxx, xxx, xxx) <BR><BR>Where the catIDs are in one column and the catNames are in a column, and not one catID and catName per row.<BR><BR>Does this make sense?<BR><BR>Please help!<BR><BR>= )<BR><BR>--C. A.<BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    96,118

    Default You can't

    Consider: Suppose that there were 10,000 categories. Would you *REALLY* want a comma-delimited list of 10,000 catid&#039;s and another list of 10,000 category names returned???<BR><BR>The standard way to do this is to return <BR> prodID, prodName, catID, catName<BR> 137, framitz, 17, left-handed<BR> 137, framitz, 21, bolt-cutting<BR> 137, framitz, 43, pliers<BR><BR>And then you use your reporting software (e.g., VBScript) to consolidate things.<BR><BR>See, for example, http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=154<BR>***********<BR><BR>HAVING SAID THIS...<BR><BR>There *IS* a complex way in SQL Server to create lists, by traversing a cursor and building complex strings and and and... You can only do this in a Stored Procedure (not in a simple SQL query). How&#039;s your expertise with Stored Procs? Oh, and *are* you using SQL Server?<BR><BR><BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    76

    Default RE: You can't

    Bill:<BR><BR>I was afraid that was the case. Luckily, we only have about 40 categories, and most events would only be assigned to one or two, maybe half a dozen at the most. Still...<BR><BR>The recordcount returned, then, is an inaccurate number of events in the query, right? Can you think of anyway to at least indicate the "correct" number of events in the results?<BR><BR>Originally, I anticipating looping through the results, and building a temporary "category string" to display, as each prodID changed.<BR><BR>I am using SQL Server 2000 for this. My stored proc knowledge, however, is limited.<BR><BR>= )<BR><BR>--Chris

  4. #4
    Join Date
    Dec 1969
    Posts
    76

    Default RE: You can't

    Another question...if category is searchable by the user...is there a way to display all the category IDs and category names...even the ones that don&#039;t match...so that the user can see the complete list of categories for each event?<BR><BR>= )<BR><BR>C. A.


  5. #5
    Join Date
    Dec 1969
    Posts
    96,118

    Default I don't understand the question

    If you want to see *all* categories, then how does the EVENT even enter into it???<BR><BR>SELECT * FROM tblCategory<BR><BR>???<BR><BR><BR>

  6. #6
    Join Date
    Dec 1969
    Posts
    96,118

    Default Ummm...count them?

    Originally, I anticipating looping through the results, and building a temporary "category string" to display, as each prodID changed.<BR><BR>Yeah, just like the FAQ.<BR><BR>So then why can&#039;t you count the events, too, as the prodID changes?<BR><BR>If you need the count ahead of time, though, you could simply do a separate query:<BR> SELECT Count(*) FROM tblEvent<BR><BR>That&#039;s a pretty low-overhead query.<BR><BR><BR>

  7. #7
    Join Date
    Dec 1969
    Posts
    76

    Default RE: I don't understand the question

    I want to see the rows where the categories aren&#039;t match...for instance:<BR><BR>If I search for catID=17 (see below)...is there a way to still show the other rows somehow for prodID=137? The user would want to know all categories a product might be in, even though they didn&#039;t search for that category. <BR><BR> prodID, prodName, catID, catName <BR> 137, framitz, 17, left-handed <BR> 137, framitz, 21, bolt-cutting <BR> 137, framitz, 43, pliers <BR><BR>If they search, for example, catID=50, then they would get no results, because prodID 137 hasn&#039;t been assigned that category.<BR><BR>C. A.

  8. #8
    Join Date
    Dec 1969
    Posts
    76

    Default RE: Ummm...count them?

    True...but some events will have multiple rows, since there will be a row presented for each category. <BR><BR> prodID, prodName, catID, catName <BR> 137, framitz, 17, left-handed <BR> 137, framitz, 21, bolt-cutting <BR> 137, framitz, 43, pliers <BR><BR>This is only one prodID, even though it&#039;s 3 rows.<BR><BR>C. A.

  9. #9
    Join Date
    Dec 1969
    Posts
    96,118

    Default Read what I said...

    Count each *CHANGE* of prodID. That&#039;s all one prodID, hence it only counts as one.<BR><BR>

  10. #10
    Join Date
    Dec 1969
    Posts
    96,118

    Default Still don't get it...

    They search for catID=50.<BR><BR>No product in the entire system has that catID (we&#039;ll say).<BR><BR>But no matter, you want to show them every single product.<BR><BR>Okay, so then what did it matter what catID you were looking for? Why bother looking for the catID, at all????<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •