MySQL query with IF Statement

Question

I want to be able to select values from either column, where column a or b is not the value I specify.

Let me explain more.

I have a table, of buddies on a website, like this:

  columna     |    columnb
  6774                887
  887                 2423
  3434                 887
  3434                 6774
  887                 3455
  887                 33444
  343434              2343

Columna tells you who sent the request.

Our user is currently 887, how using MySQL can I find out who he is friends with?

Thanks

what is column b, rather what is the relationship between the two columns? — Dan Kanze
– Dan Kanze, Commented Jan 18, 2012 at 20:23
if you can explain what is columnb, that would help me understand what you are after. — Web User
– Web User, Commented Jan 18, 2012 at 20:24
both columns are id's of members. the first is just the person who sent the request. there is also a column status, if this is 0, then the request hasnt been aprooved yet and if its 1 then it has been aprooved and those two people are friends — Jake
– Jake, Commented Jan 18, 2012 at 20:29
Ok. So I don't think the status column is immediately relevant to your question. So if you want to get all the rows where 887 is in either columna or columnb and then get the value of the other column (that is not 887), I think you may find my answer below. — Web User
– Web User, Commented Jan 18, 2012 at 20:33

Web User · Accepted Answer · 2012-01-18 20:52:08Z

2

SELECT (CASE WHEN columna = 887 THEN columnb ELSE columna END) AS 'columnAlias'
FROM <table>
WHERE columna = 887
      OR columnb = 887

Edit: For using the result of the above query to get details records from another table...

Using JOIN:

SELECT T2.*
FROM Table2 AS T2
     INNER JOIN (SELECT (CASE WHEN columna = 887 THEN columnb ELSE columna END) AS 'columnAlias'
                 FROM Table1
                 WHERE columna = 887
                       OR columnb = 887) AS _Temp ON _Temp.columnAlias = T2.ForeignKeyColumn

Using Nested Query:

SELECT T2.*
FROM Table2 AS T2
WHERE T2.ForeignKeyColumn IN (SELECT (CASE WHEN columna = 887 THEN columnb ELSE columna END) AS 'columnAlias'
                              FROM Table1
                              WHERE columna = 887
                                    OR columnb = 887)

edited Jan 18, 2012 at 20:52

answered Jan 18, 2012 at 20:25

Web User

7,82615 gold badges71 silver badges101 bronze badges

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

6 Comments

Jake Over a year ago

this works! but how can i modify it further to select information based on the column which wasnt 887 from another table? thanks!

Web User Over a year ago

Maybe nest this query inside another... SELECT * FROM <anotherTable> WHERE someColumn IN (... <above query ...). You should be able to nest or JOIN to the same effect.

Jake Over a year ago

thanks for the reply, im reading up on join, im going to try and do it that way if my intelligence lets me

Jake Over a year ago

ok so i tried the following but got unkown column 'friend' in where clause:

Jake Over a year ago

SELECT (CASE WHEN buddy.penpalA = 887 THEN buddy.penpalB ELSE buddy.penpalA END) AS friend, exchange.name FROM buddy, exchange WHERE (buddy.penpalA = 887 OR buddy.penpalB = 887) AND (buddy.status = 1) AND (friend = exchange.id)

|

Vyktor · Accepted Answer · 2012-01-18 20:24:11Z

0

You probably need WHERE statement and the query would look like SELECT ... WHERE columna = 887 OR columnb = 887

answered Jan 18, 2012 at 20:24

Vyktor

21k6 gold badges69 silver badges98 bronze badges

Comments

hanzolo · Accepted Answer · 2012-01-18 20:26:14Z

0

Selecting multiple columns is easy

SELECT columna, columnb FROM ...

Then to exclude the results WHERE the value does not match you can use the WHERE clause

 ... WHERE (columna != 887 OR columnb != 887)

answered Jan 18, 2012 at 20:26

hanzolo

1,1501 gold badge11 silver badges18 bronze badges

1 Comment

DRapp Over a year ago

Wow.. you just pulled almost the entire database with EXCEPTION to buddy = 887 in either position. What about a row where ColumnA = 23 and ColumnB = 54892... that would be included in the result set...

DRapp · Accepted Answer · 2012-01-18 20:26:14Z

0

The WHERE qualifies that buddy "887" is in EITHER position... you dont care. The select FIELD will get the OTHER ID... so if A = 887, then grab B, otherwise it was "B" that was 887 and get person A.

select
      if( b.ColumnA = 887, b.ColumnB, b.ColumnA ) as OtherPerson
   from
      Buddies b
   where
      887 in( b.ColumnA, b.ColumnB )

answered Jan 18, 2012 at 20:26

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Comments

tim · Accepted Answer · 2012-01-18 20:27:15Z

0

Make sure you unite the condition incase of more conditions:

Where (a != 1 or b != 1) and c = something

answered Jan 18, 2012 at 20:27

tim

2,7363 gold badges33 silver badges49 bronze badges

Collectives™ on Stack Overflow

MySQL query with IF Statement

5 Answers 5

6 Comments

Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

1 Comment

Comments

Comments

Related