刚来，问个SQL查询

[vivian_zl]

Create table Type
(
TypeId int identity(0,1) primary key, --类别编号　　
TypeName varchar(20), --类别名称
)


Create table Logo
(
LogoId int identity(0,1) primary key,
TypeId int FOREIGN KEY(TypeId) REFERENCES Type(TypeId)
TypeName varchar(20)
)

CREATE TABLE CUSTOMER
(
id int identity(1,1) primary key,
CustomerName varchar(100),
Id int foreign key (AutoTypeId) references AutoType(TypeId),
Quantity int,
Valid bit
)

说明type 两个字段不能为空
logo TypeId　不能为空 typename 可为空

要求，返回Customer 所有 valid 值　为1的字段，并包括其它两表的字段。（无论logoname是否为空）

id customer,logo表的logoname,type表的typename,quantity,valid

求SQL语句

[vivian_zl]

我刚来没分，所以暂时只能请大家帮帮忙了。

[szflower]

select
c.id,
b.TypeName as logoname,
a.typename,
b.quantity,
c.valid
from Type a,Logo b,CUSTOMER
where a.TypeId = b.TypeId
and b.id=c.TypeId
and c.valid = 1

你的表CUSTOMER中2次指定了列名 'Id'.

[zl198816]

你的表CUSTOMER中2次指定了列名 'Id'.

[vivian_zl]

更正Customer表
这里Id int foreign key (AutoTypeId) references AutoType(TypeId),

应是LogoId int foreign key (LogoId) references Logo(LogoId),

[vivian_zl]

create database test

use test
go

Create table Type
(
TypeId int identity(0,1) primary key, --类别编号　　
TypeName varchar(20), --类别名称
)


Create table Logo
(
LogoId int identity(0,1) primary key,
TypeId int FOREIGN KEY(TypeId) REFERENCES Type(TypeId),
LogoName varchar(20)
)

CREATE TABLE CUSTOMER
(
id int identity(1,1) primary key,
CustomerName varchar(100),
LogoId int foreign key (LogoId) references Logo(LogoId),
Quantity int,
Valid bit
)



INSERT INTO TYPE VALUES('电器')
INSERT INTO TYPE VALUES('家具')

INSERT INTO LOGO VALUES(0,'海尔')
INSERT INTO LOGO VALUES(1,'宜家')
INSERT INTO LOGO(LogoName) VALUES('其它')

SELECT * FROM TYPE
SELECT * FROM LOGO

/*
说明type 两个字段不能为空
logo TypeId　不能为空
typename 可为空

要求，返回Customer 所有 valid 值　为1的字段，并包括其它两表的字段。（无论logoname是否为空）
id customer,logo表的logoname,type表的typename,quantity,valid
求SQL语句*/

[vivian_zl]

INSERT INTO CUSTOMER VALUES('张三','0',1,1)
INSERT INTO CUSTOMER(CustomerName,Quantity,Valid) VALUES('李四',2,1)

往Customer表插入两行测试数据

这样查询只能返回一行数据

select id,customerName,logoname,typename,quantity,Valid
from
type as a,logo as b,customer as c
where
a.TypeId = b.typeid
and
b.logoid = c.logoid
and
valid
求改进方法

[hdzdw]

select distinct c.id,
c.customerName,
c.LogoId,
b.LogoName,
a.TypeName,
c.quantity,
c.Valid
from
type as a,logo as b,customer as c
where
a.TypeId = b.TypeId
and
b.LogoId in(select LogoId from Customer)
and
valid = 1

[vivian_zl]

再顶上去

[合金猫]

学习

[hyde100]

try--》

select 
	id,
	CustomerName,
	Logo.LogoName,
	Type.TypeName
from CUSTOMER 
	left outer join Logo on Logo.LogoId = CUSTOMER.LogoId
	left outer join Type on Type.TypeId = Logo.TypeId
where Valid = 1

[水族杰纶]

if object_id('CUSTOMER')is not null drop table CUSTOMER
if object_id('Logo')is not null drop table Logo
if object_id('Type')is not null drop table Type
go
Create table Type 
( TypeId int identity(0,1)  primary key, --类别编号　　 
TypeName   nvarchar(20)) --类别名称 ) 
Create table Logo 
( LogoId int identity(0,1) primary key, 
TypeId int FOREIGN KEY(TypeId) REFERENCES Type(TypeId), 
LogoName Nvarchar(20) ) 
CREATE TABLE CUSTOMER 
(id int identity(1,1)    primary key, 
CustomerName nvarchar(100), 
LogoId int foreign key (LogoId) references Logo(LogoId), 
Quantity int, 
Valid bit )
INSERT INTO TYPE VALUES(N'电器') 
INSERT INTO TYPE VALUES(N'家具') 
INSERT INTO LOGO VALUES(0,N'海尔') 
INSERT INTO LOGO VALUES(1,N'宜家') 
INSERT INTO LOGO(LogoName) VALUES(N'其它') 
INSERT INTO CUSTOMER VALUES(N'张三','0',1,1) 
INSERT INTO CUSTOMER(CustomerName,Quantity,Valid) VALUES(N'李四',2,1) 
select id,customerName,logoname,typename,quantity,Valid 
from customer as c  LEFT JOIN logo as b ON b.logoid = c.logoid 
                                 LEFT JOIN type as a ON a.TypeId = b.typeid AND C.valid=1
/*(影響 1 個資料列)

id          customerName                                                                                         logoname             typename             quantity    Valid 
----------- ---------------------------------------------------------------------------------------------------- -------------------- -------------------- ----------- ----- 
1           张三                                                                                                   海尔                   电器                   1           1
2           李四                                                                                                   NULL                 NULL                 2           1
*/

[幸运的意外]

select C.id,L.Typename,T.typename,C.quantity,1
from
CUSTOMER C
left join
Logo L
on
C.LogoId=L.TypeId
left join
Type T
on
T.TypeId=L.TypeId
where
C.valid=1

注:在第一行出现了常数1,是因为楼主只选取valid=1的行,所以直接用1就可以,三个表连接速度会有点慢,减少检索字段会让查询变得快一点.

[lihuanmei]

这是您的代码：指正一点是最好把表用连接关联上，最好不用where来关联表，where主要表示字段的条件。

select id,customerName,logoname,typename,quantity,Valid 
from 
type as a,logo as b,customer as c 
where 
a.TypeId = b.typeid 
and 
b.logoid = c.logoid 
and 
valid 

整理：

select c.*
from customer as c  LEFT JOIN logo as b ON b.logoid = c.logoid 
                                 LEFT JOIN type as a ON a.TypeId = b.typeid 
where C.valid=1

您的要求：返回Customer 所有 valid 值　为1的字段，并包括其它两表的字段。（无论logoname是否为空）
id customer,logo表的logoname,type表的typename,quantity,valid
还有：我看到您定义的Customer表中 少了与type关联的字段
我的解释：
看哪个是主表，在这里customer 是主表。
left join 表示customer 无空数据而logo和type 可以有空数据，主要满足customer 条件。