If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Combine Two Queries Into One

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2002
Location: Land of OZ
Posts: 173
Re: Combine Two Queries Into One

Quote:
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2002
Location: Land of OZ
Posts: 173
Re: Combine Two Queries Into One

Quote:
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
File Type: zip player.zip (22.8 KB, 76 views)
__________________
Mona
________________________________
Life is too short to be sane or sensible. Weird people rule and normal people suck
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.

Quote:
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
File Type: zip db1.zip (12.1 KB, 37 views)
Reply With Quote
  #7 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
your access database does not help me, i have only 97
Quote:
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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!


Quote:
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On