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
WHERE Home Checked=yes;
please help, is there a tutorial somewhere as am struggling to find any help :(
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:
From there, there are links to other parts of the manual.
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
Try this way instead, remove all the semi-colons…
SELECT `Home Checked`
WHERE `Home Checked`='yes'
In that example, I used back-ticks to surround the Home Checked column.
Thank you, that worked!
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')
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
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._
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.
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.
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
Number int(10) Auto Increment
Sex enum('male','female') NULL
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
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
Adoption Fee decimal(10,2)
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 …
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 :)
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')
That is amazing! Something so simple. Thanks again :)
Log in to post a comment.
Sign up for the SourceForge newsletter:
You seem to have CSS turned off.
Please don't fill out this field.