1

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

Improve this question
4
  • 1
    What type of outer join? left, right, full? consider using Full Outer join or left outer join or right outer join Commented Aug 11, 2013 at 18:11
  • 1
    Do you want to append the results from both queries? In that case replace the OUTER JOIN in your query above with UNION. As your query stands right now, if you got it working it would have one row with two columns named value. If you don't want to append results could you add sample data and expected results to your question? Commented Aug 11, 2013 at 18:14
  • I want to do FULL OUTER JOIN, with FULL added I get the same error. Commented Aug 11, 2013 at 18:15
  • @EdGibbs actually I want to show the two resulting columns next to each other, as you mentioned, one row with two columns. Commented Aug 11, 2013 at 18:58

1 Answer 1

Reset to default
2

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.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.