Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: Query with AND criteria not working

    Ok, this is probably a simple mistake or I am not understanding something basic. I have a table with member names. Another table with member positions. A third join table to allow a many to many relationship. I want to show members that have two different postions. So, for example, a member can be a chief and that same member can be an EMT. I want to display that member with the positions. The postition field and the member name first and last field are text fields. Here is my sql for the query:

    Code:
    SELECT tblMembers.memberID, tblMembers.memberFirst, tblMembers.memberLast, tblMembersPositionLU.positionID, tblMembersPositionLU.position
    FROM tblMembersPositionLU INNER JOIN (tblMembers INNER JOIN tblMembersPositionsMM ON tblMembers.memberID = tblMembersPositionsMM.driverID) ON tblMembersPositionLU.positionID = tblMembersPositionsMM.positionID
    WHERE (((tblMembersPositionLU.position)="EMT" And (tblMembersPositionLU.position)="Chief"));
    I know that there is one row that answers the query. I get no rows. So I am feeling stupid that I cannot figure this out. "OR" works fine. "AND" does not. Any help is appreciated. Best, Scott

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how is it possible for the same column in the same table equal 'EMT' and 'Chief' at the same time? I don't mean in terms of the problem, you are trying to solve but in terms of the way you phrased your where clause.


    there was a sort of similar question posed on the MySQL foru mthat may be of interest
    http://www.dbforums.com/showthread.p...-single-column
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2013
    Posts
    70
    i will try to clarify.

    table 1 = names of members
    table 2 = list of positions
    table 3 = memberPosition for many to many relationship

    in table1 i have a member
    member ID, memberFirstNane, memberLastName
    in table 2 i have positions chief and EMT
    positionID, txtPositionName
    in table 3
    memberID, positionID
    i have a row that is member#1:chief
    i have a row that is member#1:EMT

    so in a queery i should be able to ask for either:
    member:chief OR
    member:EMT

    or i should be able to ask for:
    member:chief AND member:EMT

    so in the query there are 3 tables. members joined to memberPositions in a one to many relationship joined on memberID and positions joined to memberPositions in a one to manyrelationship joined on positionID.

    if i use no criteria the query gives me all members and their postions including the example above of member#1:chief and member#1:EMT. the OR criteria gives me all members who are chiefs ands all members who are EMTs. that works fine.
    i.e. "Chief" OR "EMT"

    but when i use "Chief" AND "EMT" i get no results. i have a row that conains each. shouldn't the query return 1 record for each matching row?

    i hope that helps make it clearer. thanks, scott

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by brastedhouse View Post
    but when i use "Chief" AND "EMT" i get no results. i have a row that conains each. shouldn't the query return 1 record for each matching row?
    no.
    think about it. how can the same column in the same row IN THE SAME TABLE have two different values at the SAME TIME. forget what your design is tryign to do but try to get to grips with what your SQL is actually STATING
    its down to how you are performing the join and how you manipulate the data
    ..in the following Iim making up SQL on the fly, the join syntax is wrong for Access though
    if the MySQL thread doenst' cut it, another approach could be to join to the membersposition table once for each keyword you are looking for
    Code:
    SELECT T1.memberID, T1.memberFirst, T1.memberLast from tblMembers as T1
    join memberspositions as T3a ON T1.memberid = t3a.memberid
    join memberspositions as T3b ON T1.memberid = t3b.memberid
    join positions as t2a on t3a.positionid =t2a.positionid
    join positions as t2b on t3b.positionid =t2b.positionid
    WHERE t2a.positionid = 'Chief' and t2a.positionid = 'EMT'
    ..becuase of the way you have designed your tables essentially you don't need two of those joins, so you could simplify the query to be
    Code:
    SELECT T1.memberID, T1.memberFirst, T1.memberLast from tblMembers as T1
    join memberspositions as T3a ON T1.memberid = t3a.memberid
    join memberspositions as T3b ON T1.memberid = t3b.memberid
    WHERE t3a.positionid = 'Chief' and t3b.positionid = 'EMT'
    that will identify any rows where both the specified keywords are persent, but woudl exclude rows where only one of or none of the keywords were present

    Code:
    SELECT T1.memberID, T1.memberFirst, T1.memberLast from tblMembers as T1
    left join memberspositions as T3a ON T1.memberid = t3a.memberid
    left join memberspositions as T3b ON T1.memberid = t3b.memberid
    WHERE (isnull(t3a.positionid) or t3a.positionid = 'Chief')  
    and (isnull(t3b.positionid) or t3b.positionid = 'Chief')
    the advantage of the above is that it returns one row for the member where their defiend role is either EMT or Chief
    you may get away with a select distinct....
    Code:
    SELECT distinct T1.memberID, T1.memberFirst, T1.memberLast from tblMembers as T1
    left join memberspositions as T3a ON T1.memberid = t3a.memberid
    where and t3b.positionid in 'EMT', 'Chief'
    but that will return any member who has one or or both key words

    SQL can be a very finely nuanced language. you need to understand the interaction between the where clause (and group by and having if applicable)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2013
    Posts
    70
    first, i really appreciate your time and attention. i will go through the sql you provided. however, (there's always a but) i do not understand how you think that i have a column and a row the same in table3. i have a row that is unique as member1/position1 and a row that is unique as member1/position2. what am i missing here? all that table3 has is a member id column and a position id column. each row is unique. the primary key for the table is a combination of the two IDs so they have to be unique. so i am very confused. thanks, scott

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    again you are thinking about your design, as opposed to the actual SQL you are using
    Code:
    WHERE (((tblMembersPositionLU.position)="EMT" And (tblMembersPositionLU.position)="Chief"));
    ..same table, same column so it can never EVER be satisfied. Hence why I rephrased the query using two joins
    an OR in place of the AND would returns rows with either value
    ..but you could rephrase it to:-
    Code:
    WHERE tblMembersPositionLU.position in ("EMT", "Chief");
    not being sure what you are trying to achievbe I still suspect the MySQL example may be a better approach

    bear in mind the SQL join syntax in MS Access is not very compliant, and TBH Im not certain my syntax is ANSI SQL compliant, it IS air code after all.

    you cna get the smae effect by placing the tabels on the query design gui tool
    table 1 once
    table 3 twice
    define the relationships if not already defined for t1 ->t3a AND t1->t3b
    add the columns you need to see in the query (drag and drop as required)
    add the columns that aren't needed to be seen in the query but are need in the where clause (drag and drop as required, but uncheck the visible checkbox)
    then in the criteria define the t3a as ='EMT', t3b as ='Chief'
    ..again this may be wrong as I don't use the query designer.....
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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