我需要一些帮助来在SQL Server中构建SQL select查询

I have a SQL Server database and I need to get different data from two different tables In a single query, tables names:

我有一个SQL Server数据库,我需要从两个不同的表中获取不同的数据在单个查询中,表名称:

Table #1 name: Barcode

表#1名称:条形码

It contains the following columns:

它包含以下列:

• Num --> this column is a foreign key

Num - >此列是外键

• Barcode ---> this column is the primary key

条形码--->此列是主键

Table #2 name: MatCard

表#2名称:MatCard

It contains the following columns:

它包含以下列:

• Num --> This column is the primary key

Num - >此列是主键

• Name
• Price4 --> This column is for retail price

价格4 - >此栏目是零售价格

• Price24 --> This column is for wholesale price

价格24 - >此栏目是批发价格

I am trying to use this SQL query:

我正在尝试使用此SQL查询:

SELECT 
    MatCard.Name AS name, MatCard.Price4 AS price, 
    Barcode.Barcode AS code 
FROM 
    MatCard, Barcode 
WHERE
    MatCard.Num = Barcode.Num 
    AND Barcode.Barcode  = :code

This query is working well, but this query gets me only on retail price from "Price4 column".

此查询运行良好,但此查询仅从“Price4列”获得零售价。

How can I get wholesale price from the Price4 column when I scan a barcode for a packet, and get retail price from Price24 column" when I scan a barcode for piece?

当我扫描包裹的条形码时,如何从Price4栏获得批发价格,并且当我扫描条形码时,我从Price24栏获得零售价?

Note: Barcode column for packet and piece it a same Barcode column in the Barcode table

注意:数据包的条形码列和条形码表中的条形码列相同

Thanks

1 个解决方案

#1

Assuming that the existing query does work, then I think you are simply asking for an extra (or different?) column in the select clause, like this:

假设现有查询确实有效,那么我认为你只是在select子句中要求一个额外的(或不同的?)列,如下所示:

SELECT
    MatCard.Name as name
  , MatCard.Price24 as wholesale_price  -- just include the column here
  , MatCard.Price4 as retail_price
  , Barcode.Barcode as code 
FROM Barcode
INNER JOIN MatCard ON MatCard.Num = Barcode.Num 
WHERE Barcode.Barcode = :code

But before leaving, please learn to use SQL Standard join syntax. Th trick to help you do this is: STOP using commas between the table names. If you do that then you are forced to include the explicit join.

但在离开之前,请学习使用SQL Standard连接语法。帮助您执行此操作的技巧是:在表名之间使用逗号停止。如果这样做,那么您将*包含显式连接。