Page 1 of 1

Query: NOT matches.

Posted: Tue Nov 11, 2003 7:22 pm
by Unipus
I have two tables, each with a "category" field. What I want to do is query these two tables and return a list of all categories that are NOT matches.

SELECT * from categories, features where categories.category != features.category

That's sort of what I thought I needed, but boy is that ever wrong. I tried a left join but it also seemed to return ALL results, not excluding the ones that match. I think I'm making this more complicated than it needs to be in my head now.

Posted: Tue Nov 11, 2003 7:41 pm
by Unipus
Solved it.

SELECT categories.category FROM categories LEFT JOIN features USING (category) WHERE features.category IS null;

Posted: Tue Nov 11, 2003 7:46 pm
by infolock
first off, ur query is wrong if i'm reading it correctly. you are telling the query to look at those field names as the actual table names.

what you should do is :

Code: Select all

$sql = "Select categories, features from <table_name> where ...";
now, as far as the "where" clause in the query, i'm kinda lost myself.

you might be able to do something like this instead of trying to do it in the query :

Code: Select all

<?php
$sql = "Select categories, features from <table_name>";
$result = mysql_query($sql);
$rows = mysql_num_rows($result);

if (!$result)
{ 
     echo 'no rows found!';
}

if ($result['categories'] != $result['features'])
{
    // do something
}
?>
hope that helps. i'm sure there is a better way, but i'm not that sure about how to do it within the actual query itself.

Posted: Wed Nov 12, 2003 11:18 am
by JAM
Just wanted to mention that SQL's != can be written as <>.
How about the following?

Code: Select all

select foo from bar where muu <> 'cow'
Edit: Miswrote the post.

Posted: Thu Nov 13, 2003 11:04 am
by Weirdan
JAM wrote:Just wanted to mention that SQL's != is written as <>.

Code: Select all

select foo from bar where muu <> 'cow'
In MySQL you can use `!=`.

Posted: Thu Nov 13, 2003 11:24 am
by JAM
True, I was blindly thinking about the 'usual' way. != is not available in standard sql language, as <> is.