title: Selecting from MySQL view
body: <p>I'm writing a simple user login webservice and facing a problem when selecting data from a view created in phpmyadmin.</p>
  
  <h1>SQLs</h1>
  
  <p>These are my tables (not every column but the important ones).</p>
  
  <p>account table</p>
  
  <pre><code>CREATE TABLE generel.account ( 
    id INT NOT NULL auto_increment ,
    username VARCHAR(15) NOT NULL ,
    mail VARCHAR(30) NOT NULL ,
    password VARCHAR(20) NOT NULL ,
    ...
    PRIMARY KEY (user_id),
    UNIQUE username (username),
    UNIQUE `mail` (mail)
  );
  </code></pre>
  
  <p>userstatus table</p>
  
  <pre><code>CREATE TABLE game.userstatus (
    user_id INT NOT NULL ,
    is_logged_in BOOLEAN NOT NULL ,
    ...
    PRIMARY KEY `ip` (`last_known_ip`)
  );
  </code></pre>
  
  <p>with foreign key</p>
  
  <pre><code>ALTER TABLE userstatus
    ADD CONSTRAINT fk_generel.account.id
    FOREIGN key (user_id)
    REFERENCES generel.account(user_id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE;
  </code></pre>
  
  <p>Please notice the sql libraries are different (generel and game).</p>
  
  <p>This is the view I want to work with:</p>
  
  <pre><code>CREATE VIEW game.gameuser_view AS 
  SELECT
    acc.id AS user_id ,
    acc.username ,
    acc.password ,
    acc.mail ,
    usrsts.is_logged_in
    ...
  FROM
    generel.account AS acc 
    INNER JOIN
      game.userstatus AS usrsts 
      ON acc.id = usrsts.user_id
  </code></pre>
  
  <h1>Expected Behaviour</h1>
  
  <p>When selecting from the view the data the result should not be empty but depend on the where clause.
  When inserting into the view the data should be redirected to both tables and be also selectable from the view.</p>
  
  <h1>Problems</h1>
  
  <ul>
  <li>cannot select from view</li>
  <li>can insert in view but still not select</li>
  <li>data will be redirected to account table but not userstatus table</li>
  </ul>
  
  <p>As I mentioned already I'm writing a webservice for a login and the same issues persist in there as well. (I didn't check the insert thing becouse the registration happens somewhere else.)</p>