Help with an SQL Query

Pests

I need help with an sql query and have no idea how to do it. I can descibe my table layout and explain what I wait to do.

Theres two tables. matchs and users. Each user must fight the other user and it gets put into the match table.

matchs:
id|user1|user2
1|1|2
2|1|3
3|2|3

What I want to do is select all the users someone has not fought yet. Obvisly, you cant fight yourself so user1 and user2 will never be the same value. With the example above, If I wanted to find out which fights Pests has to do then it would return Mike, since his id is the only one missing out of the matchs table. For Joe it would return Pests, Bob, Mike.

Can anyone help with this? It needs to be one sql query.

AstroTeg

Sounds like you need a subquery. You using MySQL? If so, I think you're going to be stuck.

I think what you need to do is:

Grab a list of all the available user IDs except the current user.

In your matches table, search the user1 field for the current user ID and then find all IDs that are not in your list of IDs found in the previous paragraph.

Pests

Im pretty sure you can do it in sql. Ive asked for help on something relating to this a few months back, but dont remember anything. It has something to do with selecting the users out of the users table where the id in the matchs table is null.

Danicek

This will not work in MySQL, because it does not support subqueries

Select C.name, D.name
From Users C, Users D
Where D.id Not In (
Select A.user2
From Matches A
Where A.user1 = C.id)
And C.id <> D.id

Pests

I need something that will work in mysql.

AshtrayWaterloo

You can spoof the subquery by doing the select of the subquery first, putting those results into an array, and creating something that works like this:
(My English is bad, but my SQL is better):

$sqlSub = "Select A.user2 From Matches A INNER JOIN C ON A.user1 = C.id";
$resultSub = mysql_query($sqlSub);
if (mysql_num_rows($resultSub) > 0) {
    $inner = "(";
    while ($row=mysql_fetch_assoc($resultSub)) {
        $inner .= $row['user2'] . ", ";
    }
    //get rid of the last comma
    $inner = substr($inner, 0, strlen($inner) - 2) . ")";
    mysql_free_result($resultSub);
}

$sqlMain = "Select C.name, D.name
From Users C, Users D
Where D.id Not In " . $inner ."
And C.id <> D.id";
$result = mysql_query($sqlMain);

Hope this does it for you.

Ash

Pests

Not to sound mean, but I knew how to do it that way. I need to do it in one sql query.

AshtrayWaterloo

Don't worry, no insult taken! 🙂

MySQL Doesn't have support for subqueries, so you'll either need to split it out into two queries, or upgrade your database system to one that does support subqueries. Correct me if I'm wrong anybody, but I believe postgresql does.

www.postgresql.com

Ash

Danicek

Exactly.
I do not see a way to do it in one query in MySQL.

AstroTeg

In theory, if you have MySQL v4.1, you can do subselects. More info here:

http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

If you don't have v4.1, that link should give you a start in how to simulate subselects with older MySQL versions.

ultraslacker

You do not need subselects for this.

SELECT u.
FROM users u
LEFT OUTER JOIN matchs m
ON ((u.id = user1 AND user2 = )
OR (u.id = user2 AND user1 = ))
WHERE m.id IS NULL AND id != ***;

Replace **** with user in question.

Pests

One thing. Does the above query assume that if user1 fights user2, it automatically means user2 fights user1?

Edit: Just did some testing, seems it does. Which is a good thing. Im guessing its because of the OR part of ON. I never understood joins, but I have been studying how that one works...

It combines the row from the user and the row from the matchs into one where the ON statement matchs. Or something like this, im hoping...

ultraslacker

To paraphrase the query, the ON conditions designate the rows that would match the user in question, OUTER JOIN will return the unmatched rows as well as matched, the WHERE clause prunes the rows that matched the JOIN conditions (NULLs for match columns) and removes the user in question (id != **).

The query is a good way to understand JOINs - look at the full dataset ( not user.) and play around with the type of JOIN and the parameters.

Pests

Yeah, I changed it to show the users and matchs rows, and also removed the WHERE cluase to see what was going on. I just dont understand all the joins (ect: LEFT, RIGHT, OUTER, INNER, combo of the two like you have).

I also had to fix the query, was giving some erros. Basically had to add the u. and m. onto every column.