Conditional mySql?

Databases
1

I have my original query which works great:


SELECT *
FROM mytable mt INNER JOIN anotherTable at ON mt.activity_table_FK = at.pk
WHERE at.username = 'foobar' OR
      (at.permission = 'public' OR
      (at.permission = 'friends' AND
       at.username IN (SELECT friend_username_FK 
		       FROM friend_map_friend fmf
		       WHERE fmf.username_FK = 'foobar'))))
AND mt.activity_type = 'recordAddition'
ORDER BY mt.pk DESC

But I only want to use that query if the field mt.activity_type = ‘recordAddition’. Otherwise I wish to use a more basic query:



SELECT *
FROM myTable mt
WHERE mt.user_alias IN (SELECT friend_username_FK 
		                        FROM friend_map_friend fmf
		                        WHERE fmf.username_FK = 'foobar')
OR mt.user_alias = 'foobar'
ORDER BY mt.pk DESC

Is there anyway I can flip between the two using some sort of conditional processing? or can anyone suggest something better?

Kind regards and thanks.

2

I missed one off the end, I feel silly :blush:

3

My statements both have five fields. Yours, I don’t know :slight_smile:
Post your query here.

4

Hi again,

im getting the following with the UNION:

“The used SELECT statements have a different number of columns”

regards and thanks.

5

Wonderful, I will try that now and let you know how I get on :slight_smile:

6 

SELECT
    mt.field1
  , mt.field2
  , mt.field3
  , mt.field4
  , at.field5
FROM mytable mt 
INNER JOIN anotherTable at 
ON mt.activity_table_FK = at.pk
WHERE (at.username = 'foobar' OR
      (at.permission = 'public' OR
      (at.permission = 'friends' AND
       at.username IN (SELECT friend_username_FK
               FROM friend_map_friend fmf
               WHERE fmf.username_FK = 'foobar')))))
AND mt.activity_type = 'recordAddition'
UNION ALL
SELECT 
    mt.field1
  , mt.field2
  , mt.field3
  , mt.field4
  , NULL
FROM myTable mt
WHERE (mt.user_alias IN (SELECT friend_username_FK
                                FROM friend_map_friend fmf
                                WHERE fmf.username_FK = 'foobar')
OR mt.user_alias = 'foobar')
AND mt.activity_type <> 'recordAddition'
ORDER BY mt.pk DESC
7

Hi Guido, I should have put the fields I want to select in the example query.

In the first query I want to select 4 fields from ‘myTable’ and 1 from ‘anotherTable’.

In the second just 4 fields from ‘myTable’, and none from ‘anotherTable’

8

If you want to select the same fields in both queries, you can use a UNION ALL.