Comparing multiple columns in mySQL

Question

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

can you please make a sqlfiddle of your tables? www.sqlfiddle.com — BeNdErR
– BeNdErR, Commented Jun 9, 2013 at 14:21
do you want to select what is != $E_ID and = $status? Did I understand you correctly? — Fabio
– Fabio, Commented Jun 9, 2013 at 14:21
@fabio no I want to select what is not $E_ID WHEN event = $event. So if there are a few events such as gig, track and status it will return data that does not have the same E_ID for rows that have the same event — Jacob Windsor
– Jacob Windsor, Commented Jun 9, 2013 at 14:29

Μenelaοs · Accepted Answer · 2013-06-09 14:57:40Z

1

I think the OP is more interested in this:

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

edited Jun 9, 2013 at 14:57

user751651

answered Jun 9, 2013 at 14:27

Μenelaοs

24.4k20 gold badges98 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Studee · Accepted Answer · 2013-06-09 14:22:40Z

0

Do you mean like this?

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

answered Jun 9, 2013 at 14:22

Tom Studee

10.5k4 gold badges42 silver badges42 bronze badges

1 Comment

Fabio Over a year ago

$event should be surrounded with quotes since it's not integer

Fabio · Accepted Answer · 2013-06-09 14:22:49Z

0

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

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

answered Jun 9, 2013 at 14:22

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

2 Comments

Jacob Windsor Over a year ago

No actually I don't mean this. Ignore my previous comment that i've now deleted. This will select event only = to '$event'. Look at my example to see what i'm after

Fabio Over a year ago

@nbs i took a look at your example but i didn't figure out what you are trying to achieve, could you please explain?

Collectives™ on Stack Overflow

Comparing multiple columns in mySQL

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related