How do I create an sql view with more than two tables?

Question

How to create a view sql, I need to get the name and password in the Magento comic that are in different tables, to authenticate in Moodle.

Answer 1

I found the query below at this link:

SELECT ce.*, ea.attribute_code, cev.value 
FROM customer_entity AS ce 
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id AND ea.backend_type = ‘varchar’ 
LEFT JOIN customer_entity_varchar AS cev ON ce.entity_id = cev.entity_id AND ea.attribute_id = cev.attribute_id 

The above query returns client attributes, so you would only have to filter the attribute (ea.attribute_code) for the password in a clause WHERE.

More examples of queries that return user information here and here.

Answer 2

The syntax for creating a view is as follows:

CREATE OR REPLACE VIEW nome_da_view AS SELECT campo1, campo2, campo3 FROM tabela

mysql documentation

Answer 3

This SQL code will create a view for you

CREATE VIEW nome_view AS SELECT * FROM tabela

Documentation: http://www.w3schools.com/sql/sql_view.asp

Answer 4

The utluiz answer is quite right. However, there are some important things to understand about views in Mysql. Here at this link (in English), explains when you can make a UPDATE for a view.

It is sometimes possible for a Multiple-table view to be updatable, assuming that it can be processed with the MERGE Algorithm. For this to work, the view must use an Inner Join (not an Outer Join or a UNION). Also, only a single table in the view Definition can be updated, so the SET clause must name only Columns from one of the Tables in the view. Views that use UNION ALL are not permitted Even though they Might be theoretically updatable, because the implementation uses Temporary Tables to process them.

For a Multiple-table updatable view, INSERT can work if it Inserts into a single table. DELETE is not supported.

INSERT DELAYED is not supported for views.

In short, there are certain circumstances that, making a INNER JOIN in a view, you can use the UPDATE of the same. If using UNION or UNION ALL or a OUTER JOIN, can no longer make a UPDATE view. In such cases, you would have to do the UPDATE to tables directly.