How to add more conditions to the sub-table in mysql’s join join query

  mysql, question
table1:
 id      bgcolor
 1       #333333
 2       #666666
 3       #999999
 
 
 table2
 id      table1_id    clothes
 5 1 Men's T-shirt
 8 2 Women's T-shirt
 3 3 Neutral T-shirt
 
 
 table3
 id      table2_id    mytime         color
 1 5 2014-06-01 White
 2 5 2014-06-02 Grey
 3 5 2014-06-03 Green
 
 
 table4
 id      table2_id    mytime         styles
 3 5 2014-06-13 Chinese Style
 4 5 2014-06-29 European and American style
 5 5 2014-07-15 nostalgic

How to add more conditions to the sub-table in mysql’s join join query

If

table2.id=table3.table2_id
 table2.id=table4.table2_id

Because table2_id in table3 and table4 are duplicate

Therefore, the queried data will be repeated. How can I further retrieve the latest data according to mytime?

The following are the attempts in the real project:

Example 1: $ SQL = "select`score _ size'. `size _ disc'. `score _ disc'. `disc'.' category'.' bgcolor', `data'.' id', `data'.' weeks', `data'.' league',   `data`.`The_main`, `data`.`The_guest`, `data`.`The_game_time`, `data`.`The_weather`, `data`.`Half_score`, `data`.`Full_score`, `data`.`Half_size`,   `data`.`Full_size`, `data`.`Disc_Half_score`, `data`.`Disc_Full_score`, `data`.`Disc_Half_size`, `data`.`Disc_Full_size` FROM `score_size`,   `score_disc`, `data`, `category` WHERE `data`.`id`=(SELECT `id` FROM score_size ORDER BY id DESC LIMIT 0,   1) AND `data`.`id`=(SELECT `id` FROM score_disc ORDER BY id DESC LIMIT 0, 1) AND `category`.`id`=`data`.`category_id` ORDER BY `Ydata`.`The_game_time` ASC";

The query result is empty ….

Example 2: ci-based framework
 $select = 'score_1size.size_disc, score_disc.Dish, category.bgcolor, data.id, data.Weeks, data.League, data.The_main, data.The_guest,   data.The_game_time, data.The_weather, data.Half_score, data.Full_score, data.Half_size, data.Full_size, data.Disc_Half_score, data.Disc_Full_score, data.Disc_Half_size, data.Disc_Full_size';
 $where = array(
 'score_size.create_time'=>'(select max(create_time) from score_size)',
 'score_disc.create_time'=>'(select max(create_time) from score_disc)'
 );
 $D = $this->db->where($where)->select($select)->join('score_size', 'data.id=score_size.data_id')->join('score_disc',   'data.id=score_disc.data_id')->join('category', 'category.id=data.category_id', 'left')->order_by('data.The_game_time asc, score_disc.id desc, score_size.id desc')->get('data');

The->where($where)Removed condition

As long as data.id=score_disc.data_id and data.id=score_size.data_id are valid, all will be queried out.

So Example 2 will look up duplicate values

Try this.
Use the color table as a reference to join other tables.

$this->load->database();
 $this->db->select('T.TID,T.name,C.color,T.ccid as color_code,S.style');
 $this->db->from('color as C');
 $this->db->join('tshit as T', 'T.TID = C.tid');
 $this->db->join('color_code as CC', 'T.ccid = CC.CCID');
 $this->db->join('style as S', 'T.TID = S.tid');
 $this->db->order_by("time", "desc");
 $query = $this->db->get();
 $result = $query->result();
 $this->LOG($query);
 $this->LOG($result);
 // SELECT `T`.`TID`, `T`.`name`, `C`.`color`, `T`.`ccid` as color_code, `S`.`style`,   `S`.`s_date` as time FROM (`color` as C) JOIN `tshit` as T ON `T`.`TID` = `C`.`tid` JOIN `color_code` as CC ON `T`.`ccid` = `CC`.`CCID` JOIN `style` as S ON `T`.`TID` = `S`.`tid` ORDER BY `time`

According to your data, there are 9, 3 (style) x 3 (color) x 1 (tsit) = 9.

Array
 (
 [0] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > gray
 [color_code] => 1
 [style] = > European and American style
 [time] => 2014-07-30 00:00:00
 )
 
 [1] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > white
 [color_code] => 1
 [style] = > European and American style
 [time] => 2014-07-30 00:00:00
 )
 
 [2] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > green
 [color_code] => 1
 [style] = > European and American style
 [time] => 2014-07-30 00:00:00
 )
 
 [3] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > white
 [color_code] => 1
 [style] = > nostalgic
 [time] => 2014-07-29 00:00:00
 )
 
 [4] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > green
 [color_code] => 1
 [style] = > nostalgic
 [time] => 2014-07-29 00:00:00
 )
 
 [5] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > gray
 [color_code] => 1
 [style] = > nostalgic
 [time] => 2014-07-29 00:00:00
 )
 
 [6] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > gray
 [color_code] => 1
 [style] = > Chinese style
 [time] => 2014-07-24 00:00:00
 )
 
 [7] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > white
 [color_code] => 1
 [style] = > Chinese style
 [time] => 2014-07-24 00:00:00
 )
 
 [8] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > green
 [color_code] => 1
 [style] = > Chinese style
 [time] => 2014-07-24 00:00:00
 )
 
 [9] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > gray
 [color_code] => 1
 [style] = > Sweet Dating Outfit
 [time] => 2014-07-17 00:00:00
 )
 
 [10] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > white
 [color_code] => 1
 [style] = > Sweet Dating Outfit
 [time] => 2014-07-17 00:00:00
 )
 
 [11] => stdClass Object
 (
 [TID] => 5
 [name] = > men's t-shirt
 [color] = > green
 [color_code] => 1
 [style] = > Sweet Dating Outfit
 [time] => 2014-07-17 00:00:00
 )
 
 )