Database table An and table B match, but only the length of table A data is output

demand is roughly as above:

if you have two tables, table order and table item
in which table order can match the data of multiple tables item
but some of the data in table item have prices that are zero and some are not zero
now you need to output data with zero price of item in order data

I use the thinkphp3 code as follows:

    $res=D("youzan_order")->alias("a")
    ->field("a.tid")
    ->join("cel_youzan_order_item b on a.tid=b.youzan_tid","LEFT")
    ->where("a.tid="E20180608105403003500005" and b.points_price="0"")->select();

the results are as follows:

I just want to return order data that meets the criteria, and I don"t need to add the number of item

ask for the boss"s answer, thank you

Mysql sql thinkphp php

Mar.16,2022

you have an one-to-many relationship. There must be multiple records after JOIN. If you don't care about the contents of the item table, you can add distinct (true) to remove the weight:

$res=D('youzan_order')->alias('a')
    ->distinct(true)
    ->field('a.tid')
    ->join("cel_youzan_order_item b on a.tid=b.youzan_tid",'LEFT')
    ->where("a.tid='E20180608105403003500005' and b.points_price='0'")->select();

or use the exists subquery, which is a bit more troublesome to write in tp.

Previous: Laravel-mix displays this.setDynamic is not a function error

Next: ElementUI date component