CODEWITHSUNDEEP
mysqlselectjoinouter-join
Navnav
Navnav

Reputation: 1022

Outer join the output of two select statement - mysql

I am trying to join the output of two select statement, since each select statement will return 0 or 1 output, INNER JOIN will return nothing in case of one returning 0 output. I was wondering how OUTER join can be done. I have seen solution for INNER JOIN and have got that one to work but, when I change it to OUTER JOIN I get this error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN (SELECT value FROM server_setting WHERE server_id=1 AND server_sett' at line 3

here is the query:

SELECT * FROM
(SELECT `value` AS url FROM server_setting WHERE server_id=1 AND server_setting_type_id=(SELECT min(id) FROM server_setting_type WHERE type='url')) AS t1
FULL OUTER JOIN
(SELECT `value` AS port FROM server_setting WHERE server_id=1 AND server_setting_type_id=(SELECT min(id) FROM server_setting_type WHERE type='port')) AS t2
;

sample input:

server_setting table:

|---------|----------------------|-----|
|server_id|server_setting_type_id|value|
|---------|----------------------|-----|
|1        |1                     |http |
|1        |2                     |22   |
|---------|----------------------|-----|

server_setting_type table:

|--|----|
|id|type|
|--|----|
|1 |url |
|2 |port|
|--|----|

Result:

|----|----|
|url |port|
|----|----|
|http|22  |
|----|----|

thanks

Upvotes: 1

Views: 4726

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Your query is:

SELECT *
FROM (SELECT `value` AS url
      FROM server_setting
      WHERE server_id=1 AND
            server_setting_type_id=(SELECT min(id)
                                    FROM server_setting_type
                                    WHERE type='url')
     ) AS t1 FULL OUTER JOIN
     (SELECT `value` AS port
      FROM server_setting
      WHERE server_id=1 AND
            server_setting_type_id=(SELECT min(id)
                                    FROM server_setting_type
                                    WHERE type='port')
    ) AS t2;

Several things. First, MySQL does not support full outer join (which is where you are getting your error. Second, you don't have an on clause. Third, it seems really strange that the server_setting_type table would have multiple rows for "port" and "url". My first suggestion would be do do a cross join instead of a full outer join. That may do what you want.

However, for some reason, I think the following may come closer to what you really want:

      SELECT sst_url.value as url, sst_port.`value` AS port
      FROM server_setting ss left outer join
           server_setting_type sst_url
           on ss.server_setting_type_id = sst_port.id and
              sst.type = 'url' left outer join
           server_setting_type sst_port
           on ss.server_setting_type_id = sst_port.id and
              sst.type = 'port'
      where ss.server_id = 1;

This version does assume that there is only one id in server_setting_type for "url" and one for "port". That seems reasonable to me.

Upvotes: 2

Related Questions