mysql - SQL 嵌套查询不返回任何内容

Question

我是 SQL 初学者，很难理解逻辑查询。在这个特定问题中，我试图找到在加利福尼亚州购买的赛道。

这是表格。

CREATE TABLE [InvoiceLine]
(
[InvoiceLineId] INTEGER  NOT NULL,
[InvoiceId] INTEGER  NOT NULL,
[TrackId] INTEGER  NOT NULL,
[UnitPrice] NUMERIC(10,2)  NOT NULL,
[Quantity] INTEGER  NOT NULL,
CONSTRAINT [PK_InvoiceLine] PRIMARY KEY  ([InvoiceLineId]),
FOREIGN KEY ([InvoiceId]) REFERENCES [Invoice] ([InvoiceId]) 
    ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId]) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE [Invoice]
(
[InvoiceId] INTEGER  NOT NULL,
[CustomerId] INTEGER  NOT NULL,
[InvoiceDate] DATETIME  NOT NULL,
[BillingAddress] NVARCHAR(70),
[BillingCity] NVARCHAR(40),
[BillingState] NVARCHAR(40),
[BillingCountry] NVARCHAR(40),
[BillingPostalCode] NVARCHAR(10),
[Total] NUMERIC(10,2)  NOT NULL,
CONSTRAINT [PK_Invoice] PRIMARY KEY  ([InvoiceId]),
FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([CustomerId]) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE [Track]
(
[TrackId] INTEGER  NOT NULL,
[Name] NVARCHAR(200)  NOT NULL,
[AlbumId] INTEGER,
[MediaTypeId] INTEGER  NOT NULL,
[GenreId] INTEGER,
[Composer] NVARCHAR(220),
[Milliseconds] INTEGER  NOT NULL,
[Bytes] INTEGER,
[UnitPrice] NUMERIC(10,2)  NOT NULL,
CONSTRAINT [PK_Track] PRIMARY KEY  ([TrackId]),
FOREIGN KEY ([AlbumId]) REFERENCES [Album] ([AlbumId]) 
    ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([GenreId]) REFERENCES [Genre] ([GenreId]) 
    ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([MediaTypeId]) REFERENCES [MediaType] ([MediaTypeId]) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
);

这是我尝试过的。

SELECT T.name
FROM Track T, InvoiceLine L
WHERE T.trackid=L.trackid AND
L.invoiceid IN (SELECT I.invoiceid
FROM Invoice I
WHERE I.billingcity="California");

查询没有返回任何内容。这逻辑有什么问题吗？

Answer 1

SELECT
      T.name
FROM Track T
      INNER JOIN InvoiceLine L ON T.trackid = L.trackid
      INNER JOIN invoice I ON L.InvoiceId = I.InvoiceId
WHERE I.BillingState = 'California'

Track 连接到 InvoiceLine，而 InvoiceLine 连接到 Invoice

请不要使用通过 where 子句进行连接的古老方式。最简单的技巧是不允许表之间有任何逗号，例如请注意以下逗号:FROM Track T, InvoiceLine L - 避免使用这些。

我假设您希望 BillingState 中包含加利福尼亚州，而不是同名的城市(如果存在)。