[RESOLVED] Need help with a query

rprincejr

I have 2 mySQL tables. A member table and a team table. Im trying to write a query that will output all team members that are not in the members table.

The query would be like

SELECT firstName, lastName FROM teams WHERE (firstName, lastName FROM teams) != (firstName, lastName FROM members)

NogDog

We may need a better explanation of what the two tables contain and what the relations are between them.

(To me, it does not seem to make sense that there would be firstname and lastname columns in the teams table -- at least for what I usually think of as a "team".)

rprincejr

The team table has firstName1, lastName1, firstName2, lastName2 in it.

I didnt mention the 2nd team member for fear of diluting the question. Heres the full problem;

I took over for a job and the team table used member names instead of member id's. Also, the members table was added a year ago while the teams table has been in use for 4 years. Records are important so I need to split the team by member and then see if they are in the members table. If not, then add them.

So my thoughts are create a query to compare team member 1 (team table) to members (member table) to see if they exist as a member.

After I do team member 1, Ill then check team member 2.

NogDog

Maybe a union?

SELECT firstName1 AS first, lastName1 AS last FROM teams 
WHERE CONCAT(firstName, lastName) NOT IN(
   SELECT CONCAT(firstName, lastName) FROM members
)
UNION DISTINCT
SELECT firstName2 AS first, lastName2 AS last FROM teams 
WHERE CONCAT(firstName2, lastName2) NOT IN(
   SELECT CONCAT(firstName, lastName) FROM members
)

rprincejr

I think we are on the same page. This is what i was working on (although its not working yet)

$sql = "SELECT a.firstName1, a.lastName1, a.team_y ";
$sql .= "FROM tourn_teams a ";
$sql .= "WHERE CONCAT(a.firstName1, ' ', a.lastName1) AS teamMember NOT IN ";
$sql .= "(SELECT CONCAT(b.firstName, ' ', b.lastName) AS memberName ";
$sql .= "FROM tourn_members b WHERE teamMember = memberName)";

rprincejr

To help others, here is the code I used;

SELECT a.firstName, a.lastName FROM teams a WHERE
CONCAT(a.firstName, ' ', a.lastName) NOT IN 
(SELECT CONCAT(b.firstName, ' ', b.lastName) FROM members b)