Databases
StackOverflow2013
Postsdbo
POMySQL large relational query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL large relational query
    primarykey
    data
    text
    <p>I have a few tables:</p> <p><strong>letter_mail</strong> </p> <pre><code>index sent from to template public stamp stationery title content opened </code></pre> <p><strong>letter_user</strong> </p> <pre><code>index username password </code></pre> <p>ALL the rows in letter_mail are relational, except for index,public and opened, to another table.</p> <p>From and To in letter_mail corresponds to the index of letter_user. What i want is to pull all the data from the database, preferably in one query if possible. A * Select on a <code>letter_mail</code> row will yield a result much like this:</p> <pre><code>index:1 sent: 2013-10-03 from:1 to:2 template:1 public:1 stamp:1 stationery:1 title: 1 content: 1 opened : 0 </code></pre> <p>What i need, is for the information above to be filled with data from the related tables and be <code>JSON</code> encoded. And look somewhat like this:</p> <pre><code>index:1 sent: 2013-10-03 from: {1, John} to: {2, Jane} template: {index: 1, template: "standard template", url: "template_name"} public: 0 stamp: {index: 1, stamp: "standard stamp", url: "some/url"} stationery: {index: 1, stamp: "standard stationery", url: "some/url"} title: {index: 1, title: "some title"} content: {index: 1, content: "some text content"} opened : 0 </code></pre> <p>Is this completely crazy? Should i break the query into several bits or maybe collate everything into one table all together?</p> <p>Please tell if you need more information :)</p> <p><strong>Solution is this</strong>:</p> <pre><code>select mail.index, mail.sent, mail.opened, mail.public, FromU.username as FromUser, ToU.username as ToUser, T.template as TemplateName, T.url as TemplateURL, S.stamp, S.url as StampURL, S.stamp Stamp, STA.url StationaryURL, Ttl.title, C.content from letter_mail mail JOIN letter_user FromU on mail.from = FromU.index JOIN letter_user ToU on mail.to = ToU.index JOIN letter_templates T on mail.template = T.index JOIN letter_stamps S on mail.stamp = S.index JOIN letter_stationery STA on mail.stationery = STA.index JOIN letter_title Ttl on mail.title = Ttl.index JOIN letter_content C on mail.content = C.index </code></pre> <p>The query works, but doesn't return any rows.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USEirinn
    1. USEirinn
    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. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL large relational query
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POMySQL large relational query
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CONot crazy - though you will end up with a good deal of repeated data getting it all at once in a single query. Whether that is a good idea or not rather depends on the quantity of data and the proximity/connection to the database from the webserver.
      singulars
      1. POMySQL large relational query
      1. USOrbling
    2. COI just wanted to avoid having 9 select queries, but if that is what it takes then...yeah. I could store all the data from the queries inside the master table, but that will also give overhead when querying one thing. Also if people downvote, please tell why :)
      singulars
      1. POMySQL large relational query
      1. USEirinn
    3. COWasn't me - some people just mark down any question they consider basic. Don't put all the data in one table unless it should be, data duplication is to be avoided and is the primary point of a relational database. You should only need two queries, though one per table. The linking can be done in PHP.
      singulars
      1. POMySQL large relational query
      1. USOrbling
 

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