Join help!

[dhtmlgod]

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

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

[r937]

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

[dhtmlgod]

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...

[r937]

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:

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:

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

[dhtmlgod]

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?

[r937]

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

[Viral]

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