How to create a view sql , I need to get the name and password in the Magento DB that are in different tables, to authenticate in Moodle.
How to create a view sql , I need to get the name and password in the Magento DB that are in different tables, to authenticate in Moodle.
I found the query below in 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 the attributes of the client, so you would only have to filter the attribute ( ea.attribute_code
) for the password in a WHERE
clause.
More examples of queries that return user information here and here .
The syntax for creating a view is as follows:
CREATE OR REPLACE VIEW nome_da_view AS SELECT campo1, campo2, campo3 FROM tabela
This SQL code will create a view for you
CREATE VIEW nome_view AS SELECT * FROM tabela
Documentation: link
The utluiz's answer is quite right. However, there are some important things to understand about views in MySQL. Here in this link explains when you can UPDATE
to 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 definition definition can be updated, so the SET clause must only have columns from one of the tables in the view. Views that use UNION ALL are not allowed even though they might be theoretically updatable, because 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 which, by making INNER JOIN
in a view, can use UPDATE
of it. If you use UNION
or UNION ALL
or OUTER JOIN
, you can no longer UPDATE
to the view. In these cases, you would have to%% of the tables directly.