从 2 个表中选择。数据库

发布于 2024-08-06 06:47:16 字数 578 浏览 3 评论 0原文

如何在不写长全名的情况下选择 p.Quota？现在我正在做

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
  FROM CUSTOMERS AS c, 
       PRODUCTS AS p 
 LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
 LIMIT 1 ;

我收到错误：

ERROR 1054 (42S22): Unknown column 'c.StatusId' in 'on clause'

但是该列确实退出并且此代码有效：

SELECT c.id, 
       c.UserName, 
       cs.StatusName 
  FROM CUSTOMERS AS c
  JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId = cs.CustomerStatusId 
 LIMIT 1 ;

原文

How do i do select p.Quota without writing the long full name? right now i am doing

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
  FROM CUSTOMERS AS c, 
       PRODUCTS AS p 
 LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
 LIMIT 1 ;

I get the error:

ERROR 1054 (42S22): Unknown column 'c.StatusId' in 'on clause'

However the column does exit and this code works:

SELECT c.id, 
       c.UserName, 
       cs.StatusName 
  FROM CUSTOMERS AS c
  JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId = cs.CustomerStatusId 
 LIMIT 1 ;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迟到的我 2024-08-13 06:47:16

您将 ANSI 和非 ANSI JOIN 语法与以下内容混合使用：

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
  FROM CUSTOMERS AS c, 
       PRODUCTS AS p 
  LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
  LIMIT 1 ;

使用 ANSI 连接编写：

     SELECT c.id, 
            c.UserName, 
            p.Quota, 
            cs.StatusName 
       FROM CUSTOMERS AS c 
       JOIN PRODUCTS AS p ON --JOIN criteria goes here
  LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId = cs.CustomerStatusId 
      LIMIT 1;

...但我不知道您使用什么标准将 PRODUCTS 连接到 CUSTOMERS 表。

You're mixing ANSI and non ANSI JOIN syntax with:

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
  FROM CUSTOMERS AS c, 
       PRODUCTS AS p 
  LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
  LIMIT 1 ;

Written using ANSI joins:

     SELECT c.id, 
            c.UserName, 
            p.Quota, 
            cs.StatusName 
       FROM CUSTOMERS AS c 
       JOIN PRODUCTS AS p ON --JOIN criteria goes here
  LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId = cs.CustomerStatusId 
      LIMIT 1;

...but I don't know what criteria you are using to join PRODUCTS to the CUSTOMERS table.

回复收藏 0 原文

德意的啸 2024-08-13 06:47:16

问题是您的隐式内部联接后跟左联接。 MySQL 正在尝试将 PRODUCTS p 加入 CUSTOMERSTATUSTYPES cs 上，而不考虑 CUSTOMERS c。

试试这个：

SELECT 
 c.id, c.UserName, p.Quota, cs.StatusName 
FROM 
 CUSTOMERS AS c 
 INNER JOIN PRODUCTS AS p 
 LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
LIMIT 1

此外，现在您没有将 CUSTOMERS 中的记录与 PRODUCTS 中的记录相关联的子句...您只是在执行完全联接。这是你想做的事吗？

The problem is your implicit inner join followed by a left join. MySQL is trying to join PRODUCTS p on CUSTOMERSTATUSTYPES cs, without consideration for CUSTOMERS c.

Try this:

SELECT 
 c.id, c.UserName, p.Quota, cs.StatusName 
FROM 
 CUSTOMERS AS c 
 INNER JOIN PRODUCTS AS p 
 LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
LIMIT 1

Also, right now you have no clause relating records in CUSTOMERS to those in PRODUCTS... you're just doing a full join. Is this what you want to be doing?

回复收藏 0 原文

玩套路吗 2024-08-13 06:47:16

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
FROM (CUSTOMERS AS c, PRODUCTS AS p) 
LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
LIMIT 1 ;

SELECT c.id, 
       c.UserName, 
       p.Quota, 
       cs.StatusName 
FROM (CUSTOMERS AS c, PRODUCTS AS p) 
LEFT JOIN CUSTOMERSTATUSTYPES as cs ON c.StatusId=cs.CustomerStatusId 
LIMIT 1 ;

回复收藏 0 原文

~没有更多了~