4

This question is for postgres, but I only know how to write basic mySQL so please forgive any incorrect syntax in my explanation. (switching from another technology (mongo), need to know if postgres can accommodate).

Here are some example tables:

table ``profile``
+---------------------------------------+
| id  | name       | ver   | os | os_id |
+---------------------------------------+
| 221 | test1      | 0.0.1 | 1  | 12    |
| 222 | test2      | 0.0.2 | 2  | 13    |
| 223 | helloworld | 1.0.0 | 2  | 12    |
+---------------------------------------+

table ``linux``
+------------------------+
| id  | distro | env     |
+------------------------+
| 12  | arch   | openbox |
| 13  | debian | kde     |
+------------------------+

table ``windows``
+-----------------------+
| id | release    | sp  |
+-----------------------+
| 8  | Windows 8  | sp1 |
| 9  | Windows 10 | sp2 |
+-----------------------+

table ``android``
+---------------------------------+
| id | release     | image | root |
+---------------------------------+
| 12 | lollipop    | uri   | 1    |
| 13 | marshmallow | uri   | 0    |
+---------------------------------+

table ``oslist``
+--------------+
| id | name    |
+--------------+
| 1  | android |
| 2  | linux   |
| 3  | windows |
+--------------+

The main query pulls specific row from the profile table, in this example, id 223:

SELECT profile.id, profile.name, profile.ver, profile.os, profile.os_id FROM profile WHERE profile.id=223 ...

But I also need to add the row data from the respective operating system in the result. It is not ideal to make a second query, so I would like this as a subquery or JOIN.

So in the same example, the query needs to understand that profile.os is 2, and to check table.oslist for an id of 2, see that it is linux, and then use that name to dynamically JOIN the appropriate table: JOIN linux ON linux.id=profile.os_id

The result would then be this:

+---------------------------------------+
| name       | ver   | distro | env     |
+---------------------------------------+
| helloworld | 1.0.0 | arch   | openbox |
+---------------------------------------+

Had the profile id been 221, the result would have been this instead:

+----------------------------------------------+
| name       | ver   | release  | image | root |
+----------------------------------------------+
| test1      | 0.0.1 | lollipop | uri   | 1    |
+----------------------------------------------+

Is this possible? Is using the oslist table required, or can there be some other pre-written function it can feed it into (like a switch-case sort of thing)?

Thank you!

Improve this question

1 Answer 1

Reset to default
4

Any single query cannot return a varying number of columns. You can however include all possible values or return the results as JSON. On the bright side, you have a limited number of different OSes, so this is doable.

SELECT
  profile.name, profile.ver,
  oslist.name AS os,
  android.release AS android_release, android.image, android.root,
  linux.distro, linux.env,
  windows.release AS windows_release, windows.sp
FROM profile
  LEFT JOIN oslist ON (oslist.id = profile.os)
  LEFT JOIN android ON (profile.os = 1 AND android.id = profile.os_id)
  LEFT JOIN linux ON (profile.os = 2 AND linux.id = profile.os_id)
  LEFT JOIN windows ON (profile.os = 3 AND windows.id = profile.os_id)
WHERE profile.id = 223

For JSON output, try the following:

SELECT
  jsonb_strip_nulls(
    jsonb_build_object(
      'name', profile.name,
      'version', profile.ver,
      'os', oslist.name,
      'release', coalesce(android.release, windows.release),
      'image', android.image,
      'root', android.root,
      'distro', linux.distro,
      'env', linux.env,
      'sp', windows.sp
    )
  ) AS result
FROM profile
  LEFT JOIN oslist ON (oslist.id = profile.os)
  LEFT JOIN android ON (profile.os = 1 AND android.id = profile.os_id)
  LEFT JOIN linux ON (profile.os = 2 AND linux.id = profile.os_id)
  LEFT JOIN windows ON (profile.os = 3 AND windows.id = profile.os_id)
WHERE profile.id=223

This will give you 

+----------------------------------------------------------------------------------+
| result                                                                           |
+----------------------------------------------------------------------------------+
| { "name": "helloworld", "version": "1.0.0", "distro": "arch", "env": "openbox" } |
+----------------------------------------------------------------------------------+
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

A rather simple solution to what I thought was a complicated problem. This will certainly work due to the finite number of os tables. cheers!

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.