Basic SQL joining 2 tables' data

It's been awhile since I've done any web development, so this should be an easy one.

I have 2 tables...

TBLUSER
- This is a table that lists a bunch of users that belong to a team. There are 2 person teams. The structure of columns is as follows:
USERID----USERNAME----TEAMNO----TEAMNAME----P1----P2----
1----------jshoemaker------1---------Team1-----John----Mike----
2----------mhamp----------1---------Team1-----John----Mike----
3----------sgrub-----------2---------Team2------Sam----Steve---
4----------smicks----------2---------Team2------Sam----Steve---
and so on....

TBSET
- This is a table that lists the matches each team will play. There are 3 games per match. Here is the structure:
ID----SET----GAME---GAMEID----TEAMNO----
1------1-------1--------1------------1------
2------1-------2--------2------------1------
3------1-------3--------3------------1------
4------1-------1--------4------------2------
5------1-------2--------5------------2------
6------1-------3--------6------------2------
7------1-------1--------1------------3------
8------1-------2--------2------------3------
9------1-------3--------3------------3------
10-----1-------1--------4------------4------
11-----1-------2--------5------------4------
12-----1-------3--------6------------4------

I want to do a query for say... team 1's schedule. So, Basically it would:

1. Query for the team whose schedule you are viewing (say team 1 here)
2. Search TBLSET for Team 1 games... then query on GAMEID to pull its opponents for each SET.
3. List the results by SET in ASCENDING order.

I tried the following:

    <cfquery name="schedule" datasource="database">
	SELECT u.TEAMNO, u.TEAMNAME, u.P1, u.P2, s.GAMEID, s.SET, u.TEAMNAME
    FROM TBLSET AS s
    	INNER JOIN TBLUSER AS u ON u.TEAMNO = s.TEAMNO
    WHERE s.GAME = 1 AND s.TEAMNO = #teamset.TEAMNO# AND u.TEAMNAME <> '#teamset.TEAMNAME#'
    ORDER BY s.SET ASC
  </cfquery>  

Code (markup):

Note: "teamset" is a query that first grabs the data for the specified team you're querying about... in this case, Team1.

I know i'm cancelling the results out, and nothing is plotted. How can I display a list of opponents for each set?

Thanks!

 

I'm not sure why the extra query is needed. Filtering on Game number and Team number should return the results you described

USERID----USERNAME----TEAMNO----TEAMNAME----P1----P2----
1----------jshoemaker------1---------Team1-----John----Mike----
2----------mhamp----------1---------Team1-----John----Mike----

Though I probably wouldnt store user and team info in the same table.