SQL左外连接与n：m连接表-Java 学习之路

我希望左外连接与n：m之间的连接表 .

Table A 
   column: id_a

Table A:B
   column: id_a
   column: id_b

Table B
   column: id_b

表b包含所有可能的行 . 所以B列必须是左边的 .

我无法弄清楚如何呈现以显示1表A的所有可能值 . 我想显示所有的entires和误导的（为什么离开外部）

使用MySql

样本数据 .

篮子（表A）

1 | basket x
 2 | basket y

水果（表B）

1 | apple
2 | strawberries
3 | grapes
4 | lemon

连接表

篮子X查询的结果结果

1 | 1
1 | 2
1 | 3  (something which indicates it is not assigned . since there is no connection )
1 | 4  (something which indicates it is not assigned . since there is no connection )

1 回答

我想你需要 cartesian 结果 . 考虑以下示例，

declare @baskets table(basket_id int not null primary key identity, basketName varchar(255));
declare @fruits table(fruit_id int not null primary key identity, fruitName varchar(255));
declare @basketsFruits table(basket_id int not null, fruit_id int not null);

insert into @baskets(basketName)
    values('basket x'), ('basket y');

insert into @fruits(fruitName)
    values('apple'), ('strawberries'), ('grapes'), ('lemon');

insert into @basketsFruits(basket_id, fruit_id)
    values(1, 1), (1, 2), (2, 1), (2, 2), (2, 3);


select  b.*, f.fruitName
        , case when exists(select 1 from @basketsFruits as bf where bf.basket_id = b.basket_id and bf.fruit_id = f.fruit_id) then
            'Fruit Present'
        else
            'Fruit not Present'
        end as fruitStatus
from    @baskets as b, @fruits as f     -- cartesian all the fruits and all the baskets
where   b.basket_id = 1

Results:

query results

回复于 2024-04-25T21:07:55+08:00

SQL左外连接与n：m连接表

1 回答

相关问题