如何在CONCAT函数中包含另一个表的列 [英] How do include another table's column on a CONCAT function

问题描述

大家好，我有一张桌子，应该存储有关车牌，型号，品牌和许多其他东西的汽车信息。问题是我有另一个名为品牌的桌子，其中包含所有可能存在的汽车品牌。在表格中，它会插入品牌的ID而不是名称。但是当我想在组合框（License_plate - Brand - Model - Year_month）上展示时，品牌正在设置ID而不是名称。



For例如：



如果我选择丰田品牌，该品牌的ID为1.如果是，表格看起来像这样：



在'汽车'表上插入的数据值

IDCAR：1

品牌：1 - >在表'品牌'上提到TOYOTA

License_plate：22-33-AD

型号：Yaris

Year_month： 17/05





桌子：



km_carro（简称本主题中的汽车）

Hello guys, I have a table that is supposed to store car information regarding license plate, model, brand and many other things. The problem is that I have another table called brand and in it has all the possible car brands that actually exists. On the table, it inserts the ID from the brand instead of the name. But when I want to show on the combobox ("License_plate - Brand - Model - Year_month") the brand is setting up to the ID instead of the name.

For example:

If i select the Toyota brand, the ID for that brand is 1. If so, the table will look something like this:

Data values inserted on `Cars` table
IDCAR: "1"
Brand: "1" -> Referred to TOYOTA on the table `Brands`
License_plate: "22-33-AD"
Model: "Yaris"
Year_month: "17/05"


Tables:

km_carro (referred to `Cars` in this topic)

CREATE TABLE `km_carro` (
  `IDCARRO` int(11) NOT NULL AUTO_INCREMENT,
  `MATRICULA` varchar(50) NOT NULL,
  `ANO_MES` varchar(50) NOT NULL,
  `MODELO` varchar(50) NOT NULL,
  `MARCA` int(11) NOT NULL,
  `OBS` text NOT NULL,
  `COLABORADOR` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`IDCARRO`),
  KEY `FK_MARCA` (`MARCA`),
  KEY `FK_MODELO` (`MODELO`),
  KEY `FK_COLABORADOR` (`COLABORADOR`),
  CONSTRAINT `FK_MARCA` FOREIGN KEY (`MARCA`) REFERENCES `km_marca` (`IDMARCA`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

km_marca（简称品牌）

km_marca ( referred as `Brands`)

CREATE TABLE `km_marca` (
  `IDMARCA` int(11) NOT NULL AUTO_INCREMENT,
  `NOME` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`IDMARCA`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8

我想要的是：



只需在组合框中显示另一种形式的汽车数据。



我尝试过：



实际上，我已根据另一个使用ID-Name的项目编写此代码在组合框上，我设法按照我想要的方式对其进行排序，但不是Brand_name，它给了我'Brand_ID'

What I want:

Simply show the car data on a combobox regarding another form.

What I have tried:

Actually, I have written this code based on another project that is using ID-Name on the combobox and I've managed to sort it like I want, but instead of the Brand_name, it gives me the 'Brand_ID'

da.SelectCommand = New MySqlCommand("SELECT IDCAR, Concat(License_plate ,' - ', Brand, ' - ', Model, '-', Year_month) As 'CAR' from zestagio.cars ", con)
cmbCarro.DataSource = dt
       cmbCarro.DisplayMember = "Carro"
       cmbCarro.ValueMember = "IDCARRO"

推荐答案

你必须加入品牌表格，例如

You have to JOIN to the brand table e.g.

.SelectCommand = New MySqlCommand("SELECT IDCAR, Concat(License_plate ,' - ', BrandName, ' - ', Model, '-', Year_month) As 'CAR' from zestagio.cars AS C LEFT JOIN zestagio.brand AS B ON C.Brand=B.Brand;", con)

我假设汽车牌桌上的外键是[品牌]并且链接到品牌表上的[品牌]栏，并且品牌名称在名为[brandname]的列中。更改这些细节以匹配您的表格



[更新] - 这是关于联接的一篇有用的CodeProject文章 SQL连接的可视化表示 [ ^ ]

I've assumed that the foreign key on the car table is [brand] and links to the [brand] column on the brand table, and that the brand name is in a column called [brandname]. Change those details to match your tables

[UPDATE] - Here is a useful CodeProject article on joins Visual Representation of SQL Joins[^]

关于@CHiLL60及其答案，问题是定义了左连接表。如果是这样，这个语法应该可以胜任。

Regarding @CHiLL60 and his answer, the problem was defining the Left join tables. If so, this syntax should do the job.

"SELECT IDCAR, Concat(License_plate ,' - ', Name, ' - ', Model, '-', Year_Month) As 'CAR' from zestagio.car AS C LEFT JOIN zestagio.brand AS B ON C.brand=B.idbrand;"

这篇关于如何在CONCAT函数中包含另一个表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！