SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Join help!

  1. #1
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join help!

    Ok, I'm no good at joins... and I came up with the terrible thing below... could someone help me?!

    I'm trying return some info from a two tables depending if an integer is lower than the one in the database, and that some "inputtedText" matches. The problem is, I need to return some other "inputtedText" but it only returns the matched text. Which is the right thing to do really, but I need it to return some other stuff, mainly the stuff that's "displayOnList" matches 1... If that makes any sense...

    Below is the SQL, remember I'm a new to joins and MS SQL Servers
    Code:
    CREATE PROCEDURE generateReport_afterDateTEMP
    (
    @inputtedInfo text,
    @dateJoined integer
    ) 
    AS
    
    SELECT tblMembers.memberID, tblMemberProfileElements.displayName, tblMemberProfileElements.elementID, tblMemberRELProfile.inputtedInfo 
    FROM tblMemberProfileElements, tblMembers
    INNER JOIN tblMemberRELProfile 
    ON tblMembers.memberID = tblMemberRELProfile.memberID 
    WHERE tblMemberRELProfile.InputtedInfo LIKE @inputtedInfo 
    AND tblMembers.DateJoined > @dateJoined 
    AND tblMemberRELProfile.elementID = tblMemberProfileElements.elementID
    AND tblMemberProfileElements.displayOnList = 1
    GO

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please list sample rows of each of the tables showing the columns that relate to this query

    you started off by saying you need info from two tables, but there's three of them in the query

    also, LIKE @inputtedInfo is not going to do what you want unless you make sure @inputtedInfo contains the wildcard characters in the right places as part of the value that you pass in to the stored proc

    rudy

  3. #3
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The LIKE statement does contain the wildcards, I'm not that dumb

    It access' three tables, but only returns the data from two... let me try to explain it...

    One of the tables contains information to generate a form, it holds the name of the form element, what type it is (textbox, check box, radio button, etc.) and if its to be listed on the administration screen.

    Another table contains the user inputted info, the user id and what form element it was submitted to.

    The first table contains the basic user information, username, password, email etc.

    One of the form elements is called "memberType", which has 4 options and a date. The client wants to generate a report using this, but wants only certain information returned. The SQL statement needs to check if the date is higher or lower (theres another SQL statement like this) and if the user has submitted info matching the selected memberType, it then needs to check what info is has to return according to the setting in the form element table and then return the user inputted data that matches those elements...


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, dhtmlgod, sorry, you know about wildcards

    but your query does return data from all three tables

    syntactically, your query appears okay on the surface, except for the fact that it mixes table list and inner join syntax methods

    your query is:
    Code:
    SELECT tblMembers.memberID
         , tblMemberProfileElements.displayName
         , tblMemberProfileElements.elementID
         , tblMemberRELProfile.inputtedInfo 
      FROM tblMemberProfileElements
         , tblMembers
    INNER 
      JOIN tblMemberRELProfile 
        ON tblMembers.memberID = tblMemberRELProfile.memberID 
     WHERE tblMemberRELProfile.InputtedInfo LIKE @inputtedInfo 
       AND tblMembers.DateJoined > @dateJoined 
       AND tblMemberRELProfile.elementID = tblMemberProfileElements.elementID
       AND tblMemberProfileElements.displayOnList = 1
    and i would suggest you rewrite it as:
    Code:
    SELECT tblMembers.memberID
         , tblMemberProfileElements.displayName
         , tblMemberProfileElements.elementID
         , tblMemberRELProfile.inputtedInfo 
      FROM tblMembers
    INNER 
      JOIN tblMemberRELProfile 
        ON tblMembers.memberID = tblMemberRELProfile.memberID 
    INNER 
      JOIN tblMemberProfileElements
        ON tblMemberRELProfile.elementID = tblMemberProfileElements.elementID
     WHERE tblMemberRELProfile.InputtedInfo LIKE @inputtedInfo 
       AND tblMembers.DateJoined > @dateJoined 
       AND tblMemberProfileElements.displayOnList = 1
    if it's not returning the exact rows you want, then there's a semantic error, which i can help you solve only if i have an idea of the values of the fields involved (hence my request for sample rows)

    rudy

  5. #5
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You SQL returns the same as mine

    Ok, some example data would be like:

    tblMemberRELProfile
    elementID | MemberID | inputtedInfo
    1 | 1 | Chris
    2 | 1 | Canal
    3 | 1 | Full Member
    1 | 2 | Bob
    2 | 2 | Smith
    3 | 2 | Temp. Member


    tblMemberProfileElements
    elementID | displayName | displayOnList
    1 | First Name | 1
    2 | Last Name | 1
    3 | Membership | 0

    tblMembers
    memberID | DateJoined
    1 | 10 Oct. 2002
    2 | 13 Oct. 2002

    I need to check that @inputtedText matches something in the tblMemberRELProfile.inputtedInfo row and the tblMembers.dateJoined. If it does, it needs to check the tblMemberProlfileElements table for the elements that have displayOnList set to 1 (it being a bit) and then return the data that matches that from the tblMemberRELProfile.inputted table... does that make sense?


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    I need to check that @inputtedText matches something in the tblMemberRELProfile.inputtedInfo row and the tblMembers.dateJoined
    that part doesn't make sense -- how can the same @inputtedText string match something in tblMemberRELProfile.inputtedInfo and tblMembers.dateJoined?? the first is a text field and the second is a date field

  7. #7
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by dhtmlgod

    tblMemberRELProfile
    elementID | MemberID | inputtedInfo
    1 | 1 | Chris
    2 | 1 | Canal
    3 | 1 | Full Member
    1 | 2 | Bob
    2 | 2 | Smith
    3 | 2 | Temp. Member
    I think your biggest issue is stemming from poor table design. The table above should be something like:

    MemberID | Fname | Lname | Status
    1 | Chris | Canal | Full Member
    2 | Bob | Smith | Temp. Member

    I'm not quite sure why you divided a single entry (in this case, a member) into multiple rows. This sort of defeats the concept of a database record and adds a level of complexity to your queries, especially joins. Is these some reason why you can't use this type of layout in your DB?

    --Viral
    A computer without Windows is like a chocolate cake without mustard.


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
  •