CODEWITHSUNDEEP
mysqlsql
Starkov
Starkov

Reputation: 1

MySQL/SQL. Combine two select statement in two columns

I need help with a SQL query. I've got two separated SELECT queries:

SELECT
     tbl_events.eventName as eventName1
  FROM tbl_events 
  INNER JOIN tbl_users
  ON tbl_events.eventAutorID = tbl_users.userID
  WHERE tbl_users.userID = "6"; 

Output:

eventName1:
eventNametest1
eventNametest2
eventNametest3
eventNametest9

and 

 SELECT
     tbl_events.eventName as eventName2
  FROM tbl_users
  INNER JOIN tbl_participants
  ON tbl_users.userID = tbl_participants.participantUser
  INNER JOIN tbl_events
  ON tbl_participants.participantEvent = tbl_events.eventID
  WHERE tbl_users.userID = "6";  

Output:

eventName2:
eventNametest1
eventNametest3
eventNametest6
eventNametest12
eventNametest15
eventNametest16

First query is to select all events (names) created by user with a certain id (eg. id = 6), second is to get all events (event names) where user with certain id is addded as a participant. How can I combine these statements into one but with results in two different columns? The output should look something like this:

   eventName1 | eventName2

  eventNametest1   eventNametest1
  eventNametest2   eventNametest3
  eventNametest3   eventNametest6
  eventNametest9   eventNametest12
  NULL             eventNametest15
  NULL             eventNametest16

Upvotes: 0

Views: 1038

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

MySQL doesn't offer FULL JOIN. And, if someone might authorize an event and not participate, you sort of need that.

One method uses UNION ALL and variables:

SELECT MAX(authName) as authName, MAX(partName) as partName
FROM ((SELECT e.eventName as authName, NULL as partName, (@rne := @rne + 1) as rn
       FROM tbl_events e JOIN
            tbl_users u
            ON e.eventAutorID = u.userID CROSS JOIN
            (SELECT @rna := 0) params
       WHERE u.userID = 6
       ORDER BY eventName
      ) UNION ALL
      (SELECT NULL, e.eventName, (@rnp := @rnp + 1) as rn
       FROM tbl_participants p JOIN
            tbl_events e
            ON e.eventId = p.participantEvent CROSS JOIN
            (SELECT @rnp := 0) params
       WHERE p.participantUser = 6
       ORDER BY e.eventName
      )
     ) ap
GROUP BY rn;

Upvotes: 1

Bisdem
Bisdem

Reputation: 1

you just use union reference: https://dev.mysql.com/doc/refman/5.7/en/union.html

Upvotes: 0

Related Questions