Example of join multi-table join query in the CI framework

Join usage in mysql

Internal connection

1. Concept: inner join is a join that uses a comparison operator to compare the values of the columns to be joined.

2. inner join: join or inner join

3. SQL statements

Select * from table1 join table2 on table1.id = table2.id

------------- Result -------------

Idnameidscore

------------------------------

1lee190

2zhang2100

------------------------------

Note: Only the table1 and table2 columns that meet the conditions are returned.

4. Equivalent (same as the following execution)

A: select a. *, B. * from table1 a, table2 B where a. id = B. id

B: select * from table1 cross join table2 where table1.id = table2.id (note: cross join can only use where, not on)

Join usage in ci

So what is the difference in ci?

For example, use each ID in table A to query the information of this ID in the people table.

$ This-> db-> from ('A ');
$ This-> db-> join ('B', 'sites. id = B. Id ');

Use each ID in table A to query the information of this ID in Table B. Note the SQL conventions. If a column name is repeated in two tables, you must add the table name and a "." number before the column name. Therefore, sites. id indicates that the table where id is located is sites. When performing SQL multi-table queries, it is best to uniquely identify the column name to avoid ambiguity and make it clear to yourself.

For example, run the following statement:

$ This-> db-> select ('*');
$ This-> db-> from ('blogs ');
$ This-> db-> join ('comments', 'Comments. id = blogs. Id ');
$ Query = $ this-> db-> get ();

It is equivalent to executing this SQL statement.

SELECT * FROM blogs JOIN comments ON comments. id = blogs. id

If you want to use multiple connections in the query, you can call this function multiple times.

To specify the JOIN type, you can use the third parameter of this function. Options include left, right, outer, inner, left outer, and right outer.

$ This-> db-> join ('comments', 'Comments. id = blogs. ID', 'left ');

// Generate: left join comments ON comments. id = blogs. id