need help comparing data between to db's

[VanPEP]

OK I have two different tables I need to know how I can compare data against another one before displaing the results this is what I have for my select, and it works to display everything, but I only want it to show wich ones have the availibility of yes, so how would I compare the two and weed out the no's?

$result = mysql_query("select from table1 where Avail = 'Yes'");
$result2 = mysql_query("select from table2");
while ($row = mysql_fetch_object($result))
while ($row2 = mysql_fetch_object($result2))

exampl db
Table 1
+---------+-------+-------+------+
| index | team | skill |Avail |
+---------+-------+-------+------+
| 1 | A | 23 | Yes |
| 2 | B | 18 | No |
| 3 | A | 20 | Yes |
| 4 | C | 30 | Yes |
+---------+-------+-------+------+

Table2
+---------+--------+-------+
| name | phone | email |
+---------+--------+-------+
| Charles |555-3333|me@you |
| Jack |555-3334|me@you |
| Peter |555-3335|me@you |
| Tim |555-3336|me@you |
+---------+--------+-------+

[TheIceman5]

well table 1 query u got is right aint it? it will display only the YES: $result = mysql_query("select * from table1 where Avail = 'Yes'");

But what has table 2 got to do with this problem? it is in no way related to table 1 so where is this other table you want to compare stuff to?

[VanPEP]

Well I actually need to display whats in table 2, but it needs to check table 1 to see if the availability is yes or not. btw those arent the actual tables just made up for example, but same scenario. Basically one table needs to be displayed but only if a certain value exixts in the other table, so I need to know how to make them check and compare.

[VanPEP]

OK nm I got that worked out, but i have one more question . . . .

If I wanted to display table 1 and table 2 on the same page, but altenate them by fileds would that be possible? This is what I mean .

Table one Row 1
Table 2 Row 1
Table 1 row 2
Table2 row 2
Table 1 row 3
Table 2 row 3
and so on . . . .

Is there a way to put this in a loop so it will altaenate displaying all those tables?

[drawmack]

you need tables that are linked like this

CREATE TABLE IF NOT EXISTS players {
   ID BIGINT NOT NULL auto_increment,
   Monacher VARCHAR(100) NOT NULL,
   Email  VARCHAR(100) NOT NULL,
   PRIMARY KEY (ID),
   INDEX Name (Name),
   UNIQUE Email (Email)
}

CREATE TABLE IF NOT EXISTS teams {
  ID BIGINT NOT NULL auto_increment,
  TeamName VARCHAR(100) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE TeamName (TeamName)
}

CREATE TABLE IF NOT EXISTS positions {
  ID BIGINT NOT NULL auto_increment,
  Position VARCHAR(100) NOT NULL,
  PRIMARY KEY(ID),
  UNIQUE Position (Position)
}

CREATE TABLE IF NOT EXISTS players_to_teams {
  ID BIGINT NOT NULL auto_increment,
  UserID BIGINT NOT NULL,
  TeamID BIGINT NOT NULL,
  PositionID BIGINT NOT NULL,
  String INT NOT NULL,
  PRIMARY KEY (ID),
  INDEX UserID (UserID),
  INDEX TeamID (TeamID),
  INDEX PositionID (PositionID),
  INDEX String (String),
  UNIQUE PlayerData (UserID,TeamID,PositionID,String)
}

there is a lot going on here. Basically you have three entiries:
Players, Teams and Positions.

You then have a manor of relating those entities by assigning a player to a team and a position which also includes giving them a string.

You can do all kinds of stuff with this database.

From this database you can get any information you need about the teams because it has been properly normalized.