November 1, 2006 at 3:04 pm
Greetings everyone! I am working on a query to give access to a user based on the last name (KEY_NAME), Membership number (MemID) and 4 different membership categories (Category). I have come up with a query that while it distinguish the possible group of members, it doesn't return a single user when I program it to, it seems to return an odd number of members regardless of what I put in the criteria.
SELECT re7.dbo.CONSTITUENT.KEY_NAME, re7.dbo.Member.MemID,
dbo.MembershipTransaction.ExpiresOn, dbo.MembershipTransaction.Category
FROM re7.dbo.CONSTITUENT
INNER JOIN (RE7.dbo.Member INNER JOIN re7.dbo.MembershipTransaction ON re7.dbo.Member.ID = re7.dbo.MembershipTransaction.MembershipID)
ON re7.dbo.CONSTITUENT.RECORDS_ID = re7.dbo.Member.ConstitID
WHERE re7.dbo.CONSTITUENT.KEY_NAME LIKE 'Guerra'
AND re7.dbo.Member.MemID LIKE 1223826
AND re7.dbo.MembershipTransaction.ExpiresOn >getdate()
AND re7.dbo.MembershipTransaction.Category=41
or re7.dbo.MembershipTransaction.Category =42
or re7.dbo.MembershipTransaction.Category =43
or re7.dbo.MembershipTransaction.Category =44;
where it says 'Guerra' and 1223826 I can put WHATEVER I want and It'll always return the same, how can I modify this query?
Thank so much beforehand
November 1, 2006 at 3:21 pm
Your like clause is all wrong for a start.
When using like you need to use some sort of wilcard in the string to ensure it looks for more than just what you have entered
For example
WHERE re7.dbo.CONSTITUENT.KEY_NAME LIKE 'Guerra'
should read
WHERE re7.dbo.CONSTITUENT.KEY_NAME LIKE '%Guerra%'
Unless you sepcifically want 'Guerra' at the start of the column you are matching on then it would be
WHERE re7.dbo.CONSTITUENT.KEY_NAME LIKE 'Guerra%'
I suggest that you have a look at the LIKE clause in Books On Line. This will show you how to formulate the query correctly
You'll need to change the following as well
AND re7.dbo.Member.MemID LIKE 1223826
November 1, 2006 at 3:26 pm
November 2, 2006 at 5:49 am
This really works? If yes then it is something I didn't know about (which is of course possible).
FROM re7.dbo.CONSTITUENT
INNER JOIN (RE7.dbo.Member INNER JOIN re7.dbo.MembershipTransaction ON re7.dbo.Member.ID = re7.dbo.MembershipTransaction.MembershipID)
ON re7.dbo.CONSTITUENT.RECORDS_ID = re7.dbo.Member.ConstitID
I suppose you wanted something like that:
FROM re7.dbo.CONSTITUENT
INNER JOIN RE7.dbo.Member ON re7.dbo.CONSTITUENT.RECORDS_ID = re7.dbo.Member.ConstitID
INNER JOIN re7.dbo.MembershipTransaction ON re7.dbo.Member.ID = re7.dbo.MembershipTransaction.MembershipID
November 2, 2006 at 7:38 am
Thank you so much for all of your responses, Steve, yes the Like clause was not originally intended, it's actually an '=' operator because I do want the query to find one particular member based on the above criteria, not several. And for Vladan, the origin of this query is from access, we have access forms which we want to mimic on a web enviroment, my colleague prepares a query in the access wizard and then he gives me the SQL provided by access. I am going to try your suggestion to see if I get what I want. The main purpose of this query is to manage a login page, that's why I need it to be able to pick a particular user...
November 2, 2006 at 8:24 am
You should not rely on queries from Access, especially if they were created in the graphical interface. You can probably take them as a starting point, but always design your own queries for SQL Server.
For example, your query could be written this way:
SELECT Con.KEY_NAME, Mem.MemID, tr.ExpiresOn, tr.Category
FROM dbo.CONSTITUENT Con
INNER JOIN dbo.Member Mem ON Con.RECORDS_ID = Mem.ConstitID
INNER JOIN dbo.MembershipTransaction tr ON Mem.ID = tr.MembershipID
WHERE Con.KEY_NAME = 'Guerra'
AND Mem.MemID = 1223826
AND tr.ExpiresOn > GETDATE()
AND tr.Category IN (41,42,43,44)
I'm not sure why you include both Con.KEY_NAME and MemID - isn't MemID selective enough? I'd expect it to be unique.
Also I still don't know for sure what you are trying to do and what is the table CONSTITUENT, so it may not work properly. I'll be glad to modify it if you give me some more clues for it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply