Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    8

    Combine Two Queries Into One

    Hello,
    I am working on a database for a fantasy football league. I have two queries that give me what I want, based on two tables. It seems like it would be very easy to combine these into one query, but I am having difficulty. I basically want to eliminate users trading back to old players. Here are the two queries:

    USEDQB1 Query:
    SELECT USEDQB1.UserName, USEDQB1.Player
    FROM USEDQB1
    WHERE (((USEDQB1.UserName)="mark"));

    QB1 Without Matching USEDQB1 Query:
    SELECT QB1.Player
    FROM QB1 LEFT JOIN [USEDQB1 Query] ON QB1.Player = [USEDQB1 Query].Player
    WHERE ((([USEDQB1 Query].Player) Is Null));

    Is there anyone who can help me?!? Is this possible?!?!
    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: Combine Two Queries Into One

    Originally posted by bcmark21
    Hello,
    I am working on a database for a fantasy football league. I have two queries that give me what I want, based on two tables. It seems like it would be very easy to combine these into one query, but I am having difficulty. I basically want to eliminate users trading back to old players. Here are the two queries:

    USEDQB1 Query:
    SELECT USEDQB1.UserName, USEDQB1.Player
    FROM USEDQB1
    WHERE (((USEDQB1.UserName)="mark"));

    QB1 Without Matching USEDQB1 Query:
    SELECT QB1.Player
    FROM QB1 LEFT JOIN [USEDQB1 Query] ON QB1.Player = [USEDQB1 Query].Player
    WHERE ((([USEDQB1 Query].Player) Is Null));

    Is there anyone who can help me?!? Is this possible?!?!
    Thanks in advance.

    Hi Mark,

    Can you send me the two table and I will have a go at a solutin for you.

    Mona
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  3. #3
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173

    Re: Combine Two Queries Into One

    Originally posted by mona
    Hi Mark,

    Can you send me the two table and I will have a go at a solutin for you.

    Mona


    Sample database attached. Hope that this works.

    Cheers
    Attached Files Attached Files
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  4. #4
    Join Date
    Mar 2003
    Posts
    8
    Yes that works, however I would like it to be just 1 single query that takes care of it. I got it to work with 2 before, but I need one. I plan on using this online, so I would like the user to login and based on his username, have a list of remaining players he can choose from. Can this be done? Thanks a lot for your help.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand why your second query has to use the first one -- why couldn't it left join to USEDQB1 instead of [USEDQB1 Query]

    and i don't understand how you want to combine those two -- they already are combined

    could you give it another try, explaining what you want?


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2003
    Posts
    8
    You are right, I have tried joining directly to the table USEDQB1. However, I cannot get it to work with one single query. I need one and only one query to get this to work, because I plan on using the SQL statement for an online operation that will identify which players each user has had on their roster. I basically want to eliminate duplicate players (null) and filter by username (username=mark, etc.), so I have two things that I want done in one statement. It works with 2 queries no problem, I was just wondering if it could be 1, it seems feasible. I have tried repeatedly, and have been unsuccessful. I appreciate your help, and I have attached the database for guidance. Thanks.

    Originally posted by r937
    i don't understand why your second query has to use the first one -- why couldn't it left join to USEDQB1 instead of [USEDQB1 Query]

    and i don't understand how you want to combine those two -- they already are combined

    could you give it another try, explaining what you want?


    rudy
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your access database does not help me, i have only 97
    I basically want to eliminate duplicate players (null) and filter by username (username=mark, etc.), so I have two things that I want done in one statement.
    duplicates are null? now i really need to see your table layouts

    filtering by user name is easy

  8. #8
    Join Date
    Mar 2003
    Posts
    8
    Ok, lets start over, I dont know what I'm talking about. Here are the tables.

    Table: QB1

    Player Team
    B. Favre GB
    B. Griese DEN
    D. Culpepper MIN
    D. McNabb PHI
    J. Garcia SF
    K. Warner STL
    P. Manning IND
    R. Gannon OAK
    S. McNair TEN
    T. Green KC

    Table: USEDQB1

    ID UserName Player
    1 mark B. Favre
    2 mark D. McNabb
    3 mark R. Gannon
    4 mark J. Garcia
    5 gullo B. Favre
    6 gullo D. McNabb
    7 gullo J. Garcia
    8 gullo R. Gannon
    9 gullo P. Manning

    Table [QB1] is the list of players that the users have to choose from. Table [USEDQB1] is a table that keeps track of which players the users have already taken. Right now there are 2 usernames in there (mark & gullo). What I want to do is generate 1 single query that will leave a list of remaining players a certain user has NOT taken already. So for 'mark' there will be 6 players (he used 4), and for 'gullo' there will be 5 players available (he used 5).
    Again, I was able to get what I wanted using 2 queries, however I would like just 1 single query where I will use the SQL statement for dynamic content on a webpage.

    Thank you for all of your help, and let me know if you need additional assistance in my horrible explanations.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not a bad explanation at all -- because now i totally get it

    query of query is perfect for this

    you need to join QB1 to usernames, and the only way to get them from your two tables is to select distinct

    UserQuery:

    select distinct username from USEDQB1

    save UserQuery

    now you need all username player combinations, that's a cross join:

    UserPlayerQuery:

    select username, player
    from UserQuery, QB1

    save UserPlayerQuery

    now your main query:

    select UP.username
    , UP.player
    from UserPlayerQuery UP
    left outer
    join USEDQB1
    on UP.username = USEDQB1.username
    and UP.player = USEDQB1.player
    where USEDQB1.player is null

    let me know if that works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2003
    Posts
    8
    OH YES!!! IT WORKS!!!

    Thank you sooo much. I will be sure to come back to you if I ever need some more help. THANKS A TON!


    Originally posted by r937
    not a bad explanation at all -- because now i totally get it

    query of query is perfect for this

    you need to join QB1 to usernames, and the only way to get them from your two tables is to select distinct

    UserQuery:

    select distinct username from USEDQB1

    save UserQuery

    now you need all username player combinations, that's a cross join:

    UserPlayerQuery:

    select username, player
    from UserQuery, QB1

    save UserPlayerQuery

    now your main query:

    select UP.username
    , UP.player
    from UserPlayerQuery UP
    left outer
    join USEDQB1
    on UP.username = USEDQB1.username
    and UP.player = USEDQB1.player
    where USEDQB1.player is null

    let me know if that works

Posting Permissions

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