How to check same id have multiple values in another column?

[nimmi4me]

i have team member table and team table.
In team member table have , three columns are there teamid, staff_id and stafftype(leader or technician). Only one leader (staff_type column value) will comes in one team. So when i insert data to team member table i need to check whether any leader is there in same team.

How to show an error message that “already have leader in this team”?

team_id   staff_id   stafftype
1          2        leader
2          1        other
3          8        other
1          5        Technician
2          3        Other
1          4        Leader //This should not come. becoz already teamid-1 have Leader When trying to save from frond end, need to show error message ie;"already have leader in this team"

[phdr]

The following query will limit the number of rows, that match the WHERE condition in the sub-query, to 1 (the literal value in the < comparison, which you can dynamically supply) and should (untested) work for what you are doing (this was originally for inserting x cards from a deck of cards.) Use this for the case where the stafftype is ‘leader’ (you would just use a normal INSERT query for non-leader stafftype values) -

	$query = "INSERT INTO team_members (team_id, staff_id, stafftype)
	SELECT -- the following values being SELECTed are the actual data values to insert
	?,?,? FROM DUAL -- dual is an allowed dummy table name to satisfy the FROM ... WHERE syntax
	WHERE (SELECT COUNT(*) FROM team_members WHERE team_id = ? AND stafftype='leader') < 1 -- insert the data if the WHERE (subquery count) < 1 is TRUE";

The ? are prepared query place-holders. The first three are the team_id, staff_id, and stafftype values to be inserted. The Forth one is the team_id, repeated, in the sub-query WHERE clause.

To use this, you would just attempt to insert the data and test how many rows were inserted. If zero row(s) were inserted, it means that there is already a leader in the team. If the row was inserted, there was not a leader and the data for the leader got inserted.

[astonecipher]

A composite unique constraint would do that for you. You just need to make the constraint the team id and the leader position.