CODEWITHSUNDEEP
mysqlsql
Jacob Windsor
Jacob Windsor

Reputation: 6980

Comparing multiple columns in mySQL

I need to compare two different columns in a mysql WHERE statement. Within a view that I have created I have a column called E_ID and one called event. I need to SELECT from the view where the E_ID != $E_ID when event = $event.

So if $event = status and $E_ID = 1 the statement would select anything thats not got the same event and the same E_ID but it could return data that has the same E_ID and a different event.

Okay lets have some examples. Lets say I have this as my two variable:

$E_ID = '1';
$event = 'status';

Okay and now in the table is this;

E_ID    event

1       status
2       status
3       status
1       gig
3       track
5       gig

As you cans the first row contains the data set in the variables so we don't want to return that. But the problem lies in E_ID as they can be the same if the event is different. So I want to return everything that does not have the E_ID of 1 when the event is status. It can however return data of the same E_ID and the same event.

What should be returned is this;

E_ID    event

2       status
3       status
1       gig
3       track
5       gig

As you can see everything is returned but the row that has the data set in the variables.

Here's my query so far.

SELECT * FROM stream_view WHERE E_ID != '$E_ID'

Not really sure where to start so have struggled to figure it out myself

Upvotes: 3

Views: 4386

Answers (3)

Menelaos
Menelaos

Reputation: 25727

I think the OP is more interested in this:

SELECT * FROM 
stream_view 
WHERE (event = '$event' 
AND E_ID <> '$E_ID') 
OR  event<> '$event';

Upvotes: 1

Fabio
Fabio

Reputation: 23480

I think you can just place both condition in your WHERE clauses

SELECT * FROM stream_view WHERE E_ID != '$E_ID' and event = '$event'

Upvotes: 0

Tom Studee
Tom Studee

Reputation: 10452

Do you mean like this? 

SELECT * FROM stream_view WHERE E_ID != '$E_ID' AND event = '$event'

Upvotes: 0

Related Questions