Create Views for complete newbie

Help
Lemonie
2012-09-26
2013-06-06
  • Lemonie

    Lemonie - 2012-09-26

    I am trying to teach myself how to build a database for a charity I help.  I want to create a view but no matter what code I enter I keep getting a syntax error?  Here is the code …

    Name: Home Checked Applications

    CREATE VIEW 'Home Checked Applications' AS
    SELECT Postcode:
    FROM Applications
    WHERE Home Checked=yes;

    please help, is there a tutorial somewhere as am struggling to find any help :(

     
  • Paul

    Paul - 2012-09-27

    What syntax error?

    The details of your Applications table is not known…
    1. There appears to be a colon after postcode?

    2. The WHERE clause has Home Checked which appears to be two words separated by a space…is that the column name?  If so, then that column would need back-ticks  to complete the column name, and the value would need single quotes as a string , so that WHERE clause should like this:   WHERE  `Home Checked`='yes';

    Other than that, other details would be needed.

    MySQL has plenty of information available.  Check this about the CREATE VIEW syntax:
    http://dev.mysql.com/doc/refman/5.1/en/create-view.html

    From there, there are links to other parts of the manual.

     
  • Lemonie

    Lemonie - 2012-09-27

    Hi

    My table, Applications, is a list of application forms for dogs.  I would like a new view which only shows the application where the Home Check column says yes.  Same columns to show just filtered.

    Have attached a screen shot of the error

     
  • Paul

    Paul - 2012-09-29

    Try this way instead, remove all the semi-colons…

    SELECT `Home Checked`
    FROM Applications
    WHERE `Home Checked`='yes'

    In that example, I used back-ticks to surround the Home Checked column.

     
  • Lemonie

    Lemonie - 2012-10-01

    Thank you, that worked!

     
  • Lemonie

    Lemonie - 2012-10-01

    This let me create the view but did not show any data?  My columns have a colon after each entry which is shown below.  Basically I want the columns listed below to be pulled from a table when the conditions are as shown?

    When I go to view the view I just get one column called 'Name_exp_1' with no data?

    select 'Name:, Number:, Breed:, Received from:, Leaving date:, Image:, Rescue:, Reason for Leaving:, Notes:' AS `Name_exp_1` from `Applications` where ('Reason for Leaving:' = 'Gone to Rescue')

     
  • Paul

    Paul - 2012-10-01

    I notice that there is not any column called `Home Checked`, based your previous posts…maybe it was left out of this latest column list? Anyway, You have created one very long string column , and the Name_exp_1 implies "name expression 1".

    Do you literally have column names with colons included?

    I might need to see, if you can post it, the create script of your applications table.  Run this SQL Command, :

    SHOW CREATE TABLE Applications
    
     
  • Paul

    Paul - 2012-10-02

    I went ahead and tried a sample applications table, using similar column names as yours, and I see that it does NOT like the colons present.  _

    To get your view syntax right in that last example , there will be MANY back ticks involved to keep the names:  around EACH column name, including the column name that appears in the WHERE clause._

     
  • Lemonie

    Lemonie - 2012-10-02

    The reason for the colons is that I import information from an application form on my website where there are colons after each question before the answer.  If I don't include them on the database there is a column mismatch and it won't import.

     
  • Paul

    Paul - 2012-10-02

    Like I said, I wouldn't use them if I can avoid them. So keep them if you must, but know that you will always need to add the back-ticks to your column names.

     
  • Lemonie

    Lemonie - 2012-10-02

    Thanks for your help, I really appreciate it!

    I don't know where to enter ..  SHOW CREATE TABLE Applications ?

    I was using examples above:

    My table is 'Dogs in Need'

    Column Type Comment
    Name char(100)
    Number int(10) Auto Increment
    Breed varchar(100)
    Sex enum('male','female') NULL
    Age varchar(20)
    Neutered enum('yes','no','don''t know') NULL
    Arrival date date
    Due out date
    Received from enum('Aspen','Sundown','Private Individual') NULL
    Current location enum('Sundown','Aspen','Coach House','Fir Tree','In Foster','On Home Trial','At Home') NULL
    Status set('pound dog','for adoption','reserved to a rescue','reserved to a home','in emergency boarding','serving 7 days','private rehoming') NULL
    Image varchar(500)
    Microchipped char(10)
    Microchip No int(20)
    Foster Carer varchar(100)
    Home Trial varchar(100)
    Coach House text
    Fir tree text
    Vet Fees text
    Leaving date date
    Reason for Leaving enum('Gone to Rescue','Rehomed by Us','Reclaimed by Owners','Rehomed by the Pound','PTS by Us','PTS by the Pound','Sent to Rescue by the Pound') NULL
    Rescue varchar(100)
    Adopter varchar(100)
    Adoption Fee decimal(10,2)
    Notes text

    I want to call certain columns where 'Reason for Leaving' is 'Gone to Rescue'

    This is the code I am using (have removed the colons like you advised)

    select 'Name, Number, Breed, Received from, Leaving date, Image, Rescue, Reason for Leaving, Notes' AS `Name_exp_1` from `Dogs in Need` where ('Reason for Leaving:' = 'Gone to Rescue')

    This creates a view fine.

    When I click on the view I see this …


    When I click on 'select data' I see this …

    http://i1265.photobucket.com/albums/jj516/Homeless_Hounds_Website/RescueView2.png

    I have previously been using Zoho but am wanting to learn and build a more customised database for us.  It's proving hard going but I'll get there :)

     
  • Paul

    Paul - 2012-10-02

    Run that SHOW command in the "SQL Command" area.

    The column still appears the same, because you still have ALL the columns enclosed/listed inside single quotes. .

    Because some of your column names contain spaces, those columns will still require back ticks.  So the query for the view you are trying to create, should look like this:

    SELECT Name, Number, Breed, `Received from`, `Leaving date`, Image, Rescue, `Reason for Leaving`, Notes 
    FROM `Dogs in Need`
    WHERE (`Reason for Leaving` = 'Gone to Rescue')
    
     
  • Lemonie

    Lemonie - 2012-10-02

    That is amazing!  Something so simple.  Thanks again :)

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks