SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditional mySql ?

    I have my original query which works great:

    Code MySQL:
    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:

    Code MySQL:
     
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you want to select the same fields in both queries, you can use a UNION ALL.

  3. #3
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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'

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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

  5. #5
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wonderful, I will try that now and let you know how I get on

  6. #6
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again,

    im getting the following with the UNION:

    "The used SELECT statements have a different number of columns"

    regards and thanks.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    My statements both have five fields. Yours, I don't know
    Post your query here.

  8. #8
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I missed one off the end, I feel silly


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •