Creating a new column in mysql view from an existing value

Question

I am incredibly new at this so let me know if I can clarify. Essentially I am looking to create a new column in a mysql view based on values from a column in an existing table. I'm using phpGrid to output on the front end.

For instance - let's say I have existing column question_id:

    name | question_id | answer
----------------------------------
    Joe  | 1           | yes
    Joe  | 2           | no
    Ann  | 1           | maybe
    Ann  | 2           | kinda

and I have custom view my_view where I want the columns to be as such (not necessarily with question_id= in front, but whatever works):

    name  | question_id=1 | question_id=2
------------------------------------------
    Joe   | yes           | no
    Ann   | maybe         | kinda

Or something to that effect. How do I get the question_id values to become columns, and the answers in the right place?

Also, if name is from one table, and question_id is from another, how can I get the correctly associated values alongside each other in my_view? name values often have duplicates, but they have individual entry ids.

Cheers and thanks!

EDIT: Create MySQL view using distinct values as columns is the right idea, but I get error #1064 about incorrect syntax.

Also, I'm not sure it solves the second problem of getting the data to align by unique entry ID. I tried to use this: 1052: Column 'id' in field list is ambiguous to fix the ambiguous issue but then I get error #1060 that there is a duplicate.

EDIT 2: Here is the code I use to try to get the columns from two separate tables, including the shared registration_id column present in both. I got the code from the post above regarding solving the ambiguous issue:

CREATE VIEW access AS
    SELECT fname,lname,email,city,state,country_id,phone,payment_status,event_id,wp_events_attendee.registration_id,wp_events_answer.registration_id,question_id,answer
    FROM wp_events_answer
    JOIN wp_events_attendee ON wp_events_attendee.registration_id = wp_events_answer.registration_id

It gives me "#1060 - Duplicate column name 'registration_id'". Also not sure how to integrate that into the code seen at Create MySQL view using distinct values as columns. I tried to do a test:

  CREATE VIEW access AS
SELECT
    fname,lname,email,city,state,country_id,phone,payment_status,event_id,wp_events_attendee.registration_id,wp_events_answer.registration_id,question_id,answer,registration_id,
    MAX(IF(question_id = '1', status, NULL)) FIRST,
    MAX(IF(question_id = '11', status, NULL)) DOB,
FROM wp_events_answer, wp_events_attendee
JOIN wp_events_attendee ON wp_events_attendee.registration_id = wp_events_answer.registration_id 
GROUP BY registration_id;

But got "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_events_answer, wp_events_attendee GROUP BY registration_id' at line 6". Thanks!

possible duplicate of [Create MySQL view using distinct values as columns](http://stackoverflow.com/questions/4842148/create-mysql-view-using-distinct-values-as-columns) — jpw, Aug 02 '15 at 22:31
If you have code that generates a syntax error, then include it in your question. — Gordon Linoff, Aug 03 '15 at 00:04

score -1 · Answer 1 · answered Aug 03 '15 at 10:56

-1

I don't have much knowledge about sql views, but it seems there is a problem in your query when joining tables.

if you need to join 3 tables, follow the query below.

SELECT `table1`.`column1`,`table2`.`column2` 
FROM `table1` 
JOIN `mapping_table` 
JOIN `table2` 
ON `table1`.`table1_id`=`mapping_table`.`table1_foreign_key` 
AND `mapping_table`.`table2_foreign_key`=`table2`.`table2_id` 
GROUP BY `table1`.`group_column`

Hope this will help you to create the view you want

answered Aug 03 '15 at 10:56

user27

169
1
9

Hi and thanks! I need to join two tables, not three. I don't think I adjusted it right and I'm not clear on what mapping table or foreign key are standing in for? Thanks for your help. – Lauren Aug 03 '15 at 20:01
if it's only two tables u need to join then it's more easy. you don't need a mapping table. foreign key is used to have a relationship between 2 tables (i suggest u do more research on this) . This example w3schools.com/sql/sql_foreignkey.asp will also give you a better idea about foreign keys. I need to know for which table/tables the columns **fname,lname,email,city,state,country_id,phone,payment_status,event_id,answer,re‌gistration_id** are belongs to so that i can provide u with better answer – user27 Aug 04 '15 at 03:53

forgetso · Accepted Answer · 2015-08-03T21:08:58.943

-2

Assuming you know the questions ids in advance this could be achieved with a bunch of UNIONS - http://sqlfiddle.com/#!9/00a0c/8

I'm afraid I'd need some sample data to answer the second part of your question.

UPDATE - I didn't realise that the additional rows were a problem. The following SQL fiddle addresses this issue.

http://sqlfiddle.com/#!9/0f8c7/2

edited Aug 03 '15 at 21:08

answered Aug 02 '15 at 22:34

forgetso

2,194
14
33

Thanks for the suggestion. Looks a bit over my head but I'll explore. As for the second question, the tables I mention store event registration data. So 'name' will sign up for multiple events, and answer the same questions, but the entries will be separated by a registration_id. One table stores their name, email, and registration_id. The other stores their answers to custom questions, but links it all together with the same registration_id. The problem: when I create a view, it won't link data in the right rows. I tried to fix it but got errors (see my edit for details). Thanks again! – Lauren Aug 02 '15 at 23:34
1

Try this instead - http://sqlfiddle.com/#!9/10b28/13 There were several things wrong with your code: 1) You have an unecessary comma after DOB 2) I think FROM should be followed by one table name only (the other one is referenced in the JOIN) 3) Your IF statements are referring to a column "status" that doesn't exist within your table schema. I would recommend to do some research into SQL JOINS to better understand how to pull data from multiple tables. http://www.w3schools.com/sql/sql_join.asp – forgetso Aug 03 '15 at 09:43
1

Thank you - but that still creates two unnecessary rows for "Bob" and "Joe". Even when I adjusted the values there to make each person have an answer in each column, they still have everything separated on multiple rows. – Lauren Aug 03 '15 at 19:40
I redid the code to match the one I was working from - and simplified it for testing - and took your suggestions, but I am still getting " #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1, IF(question_id = '11', answer, NULL) 11 FROM wp_events_answer GROUP BY' at line 4" `CREATE VIEW access AS SELECT registration_id, MAX(IF(question_id = '1', answer, NULL)) 1, MAX(IF(question_id = '11', answer, NULL)) 11 FROM wp_events_answer GROUP BY registration_id;` – Lauren Aug 03 '15 at 20:12
I modified the data slightly and the example to remove the additional rows. Each respondent's answers are now featured on one row. I would take a guess that your statement is not working because of the column name. Try writing " AS question_1" instead of simply "1" `MAX(IF(question_id = '1', answer, NULL)) AS question_1` – forgetso Aug 03 '15 at 21:13

Creating a new column in mysql view from an existing value

2 Answers2