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

2

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.

    
asked by anonymous 03.02.2014 / 13:28

4 answers

1

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 .

    
03.02.2014 / 14:42
0

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

    
03.02.2014 / 13:33
0

This SQL code will create a view for you

CREATE VIEW nome_view AS SELECT * FROM tabela

Documentation: link

    
03.02.2014 / 13:37
0

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.

    
04.02.2014 / 19:10