SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    Syracuse
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    To join or not to join....

    I guess that is my question! I've got two tables - one houses information on available subjects to categorize electronic resources and the other houses the information on the individual resource

    What I'm trying to do is insert a number in the resource table that corresponds to the subject_id (primary key) in the subject table. I'm having a hard time retrieving the correct information and am not sure if the problem is with my join statement or with the code generates the select box on my form.

    Here's my select statement:

    Code:
    SELECT library_resources.*, library_subject.*  FROM dbo.library_subject, dbo.library_resources WHERE library_resources.library_subject_1=library_subject.library_subject_id
    And for the select box:
    Code:
    <select name="library_subject_1">
    <%
    While (NOT catalog.EOF)
    %>
    <option value="<%=(catalog.Fields.Item("library_subject_id").value)%>" 
    <%if subject1.Fields.Item("library_subject_1")=catalog.Fields.Item("library_subject_id") then%> SELECTED <%end if%>><%= catalog.Fields.Item("library_topic_grouping")%> -  <%=catalog.Fields.Item("library_category")%></option>
    <%
    catalog.MoveNext()
    Wend
    If (catalog.CursorType > 0) Then
      catalog.MoveFirst
    Else
      catalog.Requery
    End If
    %>
    </select>
    I don't get the category that matches the value of the resource, I just get a list of all the categories.

    I guess my question is two-fold - is this a good way to do it and does anyone have a suggestion as to what I'm doing wrong.

    Thanks in advance and apologies for the rambling.

    Michelle

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    when you say "insert a number in the resource table" do you mean insert a number INTO the resource table, or insert a number that is in the resource table INTO something else?

    if you're populating a dropdown list of subjects, i don't think you need a join, just query the subjects table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    Syracuse
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry - I knew I wasn't explaining that well. My subject table has an entry like

    Subject_ID: 1
    Subject_Description: General Reference

    On the form I wrote to insert a resource into the resource table, I only insert the subject_id number into the database. This column is library_subject_1 (actually the form is designed so that you can enter up to 10 subject designations)

    So what I want on this form (which lets the user edit an existing entry), is to display the current subject in the select with the rest of the available options in the select box.

    Hope that makes a bit more sense.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, the select box would have to show all the currently selected subject options (since a resource might have more than one) among all the possible options

    of most concern is the fact that you appear to allow multiple values to be inserted into a single column

    you should really, really change this design to implement a proper many-to-many relationship


    library_subjects
    id PK
    name

    library_resources
    id PK
    name

    library_subject_resources
    resource_id FK
    subject_id FK
    PK (resource_id,subject_id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2004
    Location
    Syracuse
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't allow multiple values in the column, but rather have columns for library_subject_1, library_subject_2, etc

    I knew I was doing something wrong, but didn't know what. Thanks for the suggestion


Bookmarks

Posting Permissions

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