嵌套查询无法正确获取数据

Question

Problem

I have two different tables, table names are team_request and teams. [team_request] have unique records of each team submitted and [teams] saves duplicate records with unique username's but same team data as in [team_request] for the user who accepted to be part of that team.

Expected result from below query:

I am running the below query to fetch the pending challenges or team request, which have a basic logic of fetch all the teams from [team_request] where that team doesn't exists in [teams] table or hasn't been ignored by that user.

Actually what query is doing:

It's fetching all the rows (teams) from table [team_request] which belongs to that user, no matter whether a user has excepted or ignored.

Also forgot to mention above fbC_status is a field in [team_request] which saves user's email address if that user has ignored to be part of the team.

Any help or guidance will be appreciated I just got stuck with it.

MySQL Query

SELECT *
FROM team_request
WHERE (c_emails LIKE '[e-mail]%')
  OR (c_emails LIKE '%9876543210%')
  AND fbC_status NOT LIKE '[e-mail]%'
  AND fbC_status NOT LIKE '%9876543210%'
  AND '[e-mail]%' NOT IN
    (SELECT username
     FROM teams)
  AND t_name NOT IN
    (SELECT T.t_name
     FROM team_request TR,
          teams T
     WHERE TR.t_name = T.t_name
       AND T.username = '[e-mail]%'
       AND (TR.fbC_status NOT LIKE '[e-mail]%'
            AND TR.fbC_status NOT LIKE '%9876543210%')) 
LIMIT 0, 30

Answer 1

I would suggest you rethink how your database is structured, your teams table appears to be pretty much a duplicate of team_requests but given the current structure try something like:

@phone would be your '9876543210'
@username would be your '[e-mail]'

SELECT r.*
FROM team_request r
WHERE (r.c_emails = @username OR r.c_emails = @phone)
  AND r.fbC_status <> r.c_emails
  AND r.t_name NOT IN ( SELECT t.name 
                        FROM teams t
                        WHERE /* t.username = r.c_emails otherwise: */
                              t.username = @username /* OR t.phone = @phone*/

                      )

Its fairly unclear what all your fields are used for so this is unlikely to work without changes. The commented out parts are what I suspect you should use, but I have no means of knowing whether or not they would work with your tables.

You can feel free to use your LIKE '%...%' however remember that if a user has the email [e-mail], when a user with the email [e-mail] also exists LIKE '[e-mail]%' is going to match them both.