Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You also tagged the question with PostgreSQL and it's actually quite easy with Postgres to work around this broken design:</p> <pre><code>SELECT grp.groupname, r.roletitle FROM groups grp join ( select groupid, cast(regexp_split_to_table(roles, ',') as integer) as role_id from privileges ) as privs on privs.groupid = grp.groupid join roles r on r.roleid = privs.role_id; </code></pre> <p>SQLFiddle: <a href="http://sqlfiddle.com/#!12/5e87b/1" rel="nofollow">http://sqlfiddle.com/#!12/5e87b/1</a></p> <p>(Note that I changed the incorrectly spelled name <code>preveleges</code> to the correct spelling <code>privileges</code>)</p> <p>But you should really, <em>really</em> re-design your data model! </p> <p>Fixing your design also enables you to define foreign key constraints and validate the input. In your current model, the application would probably break (just as my query would), if someone inserted the value <code>'one,two,three'</code> into the roles table. </p> <p><strong>Edit</strong></p> <p>To complete the picture, using Postgres's array handling the above could be slightly simplified using a similar approach as MySQL's <code>find_in_set()</code></p> <pre><code>select grp.groupname, r.roletitle from groups grp join privileges privs on grp.groupid = privs.groupid join roles r on r.roleid::text = any (string_to_array(privs.roles, ',')) </code></pre> <p>In both cases if all role titles should be shown as a comma separated list, the string_agg() function could be used (which is equivalent to MySQL's <code>group_concat()</code></p> <pre><code>select grp.groupname, string_agg(r.roletitle, ',') from groups grp join privileges privs on grp.groupid = privs.groupid join roles r on r.roleid::text = any (string_to_array(privs.roles, ',')) group by grp.groupname </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSql Result IN a Query
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USa_horse_with_no_name
    1. USa_horse_with_no_name
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthanx..it seems works..will it works in case of MYSQL..
      singulars
      1. PO
      1. USCodePlayer
    2. COthanx..i will use program code logic to meet my requirement
      singulars
      1. PO
      1. USCodePlayer
    3. CO@CodePlayer: suit yourself. But I'd bet you'll spend much more time fixing your data and fighting this problem then it would take to implement the correct design.
      singulars
      1. PO
      1. USa_horse_with_no_name
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload