Combined Query of two SQL tables and database query under yii, Combined Query yii

Source: Internet
Author: User

Combined Query of two SQL tables and database query under yii, Combined Query yii
Join on


For example, two table queries:

select  u.username, t.title  from user u join task t on u.id = t.id; 
Here, user u is used to simplify the join Operation of table names. If it is added to other tables, on is used to represent the query condition u. username is used to represent the username field in the user table.


The same principle applies to multi-table queries.
This is an SQL statement:

select * from user u join task t on u.id = t.idjoin task_user tu on tu.id = t.id

This method uses query under the yii framework:
$query = new Query();        $query->select('title,content,task.enable,task.id');        $query->from('task');        $query->leftJoin('task_user_rel', 'task.id= task_user_rel.task_id');        $query->leftJoin('user', 'task_user_rel.user_id = user.id');        $query->leftJoin('org_user_rel', 'user.id = org_user_rel.user_id');        $query->leftJoin('org', 'org_user_rel.org_id =org.id');        $query->where([            'org.id' => $orgModel->id        ]);$data = $query->all();$data = $query->one();


In fact, there are many database query methods in the yii framework, such:

   $data = JdCrmSmsUserModel::find()->where([            'vender_id' => [               $venderId            ]        ])->all();

Or use findBySql (), findOne (), findAll (), and so on to replace find () for query.
Combined Query of two SQL database tables

Two steps are involved.
Step 1: first convert the data in Table 2 from a horizontal table to a vertical table. The name field is empty first, and the second step is used for matching. Some users only have data for 8 to 9 months. In this case, they can write the data they do not have as 0. for example, if user a does not have data for December, the corresponding month12 field data is 0, and some correspond to dosage. Code:
Insert into table 3 (number, month1, mon2, month3. ...... month12)
Select number,
Sum (case when month = 1 then dosage else 0 end) month1,
Sum (case when month = 2 then dosage else 0 end) mon2,
........
Sum (case when month = 12 then dosage else 0 end) month12
Group by number
Note: month = 1 is written according to the field type in Table 2. For example, if the data of month is 'february ', it must be written as month = 'february '. 1 to 12.
Step 2: Match
Update Table 3
Set a. name = B. name
From table 1 B
Where a. number = B. number

How to associate SQL queries with two tables in two databases

To associate the data of the two databases, you need to add a prefix, that is, the database, user, table name, and so on. Simply press the query statement they wrote above. Otherwise, it won't work, another one is very stupid. If you export data from one database to another, the two tables can be directly associated.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.