想要显示来自具有相同ID的两个不同表的不同记录 [英] want to display distinct record from two different table having same id

问题描述

嗨
我有两个表，其中一个是具有ID，名称，价格，说明的产品信息，另一个具有与1个ID相关的图像
想要只显示ID为ID且ID为1的图像的唯一记录，请不要重复
我正在使用此查询，但是获得重复ID的任何帮助

Hi
i have two tables one is having product info that is id,name, price, description other having different images related to 1 id
want to display only distinct record that is 1 image with id and id shouldnot repeat
i am using this query but getting repetative id''s any help

string cmdstr = "SELECT Productname,Price,Description,images,id,Sellername FROM Products AS a INNER JOIN Productimage AS p ON a.id = p.productid where Category='" + Label1.Text + "' ORDER BY a.id DESC";

推荐答案

在您的查询中，获得相同产品ID的次数与获得该产品图片的次数相同.

您需要做的是添加一个条件，以过滤掉您不感兴趣的图像.此条件取决于表的结构和要求.

例如，假设图像的序号对于所有产品均从1开始.然后查询可能类似于:

In your query you get the same product id as many times as you have images for the product.

What you need to do is to add a condition which filters out the images you''re not interested in. This condition depends on the structure of your tables and the requirements.

For example let''s say that the images have an ordinal number which starts from 1 for all products. Then the query could be something like:

string cmdstr = @"SELECT Productname,Price,Description,images,id,Sellername 
FROM Products AS a INNER JOIN Productimage AS p ON a.id = p.productid 
where Category='" + Label1.Text + "' 
AND p.Ordinal = 1
ORDER BY a.id DESC";

附带说明，您永远不要将UI字段中的值连接到SQL语句.这使您可以进行SQL注入.有关更多信息，请参见正确执行数据库操作，版本3 [ ^ ]

加法:

例如:如果您想显示具有最小"名称的图像(无论该字符数据实际上意味着什么)，那么您可以使用类似
的图像

As a side-note, you should never concatenate values from UI fields to the SQL statement. This leaves you wide open to SQL injections. For more information, see Properly executing database operations, version 3[^]

Addition:

As an example: If you would like to show the image having the ''minimum'' name (whatever that actually means with character data) then you could have something like

string cmdstr = @"SELECT Productname,Price,Description,images,id,Sellername
FROM Products AS a INNER JOIN Productimage AS p ON a.id = p.productid
where Category='" + Label1.Text + "'
AND NOT EXISTS (SELECT 1
                FROM ProductImage p2
                WHERE p2.ProductId = a.Id
                AND   p2.Images > p.Images) 
ORDER BY a.id DESC";

我不想获取1个id的所有图像，我只需要一个id的图像
喜欢
表A
id产品
1辆车
2电视
3个手机
表B
产品编号图片
1张image1.jpg
1张image2.jpg
1张image3，jpg
2 image4.jpg
2 car.jpg
我需要输出为
id产品图片
1汽车image1.jpg
2电视image3，jpg
不重复的ID
任何帮助

i don''t want to get all images for 1 id i need only one image for an id
like
Table A
id product
1 car
2 tv
3 mobile
Table B
productid images
1 image1.jpg
1 image2.jpg
1 image3,jpg
2 image4.jpg
2 car.jpg
i need output to be
id product image
1 car image1.jpg
2 tv image3,jpg
not repetitive ids
any help

这篇关于想要显示来自具有相同ID的两个不同表的不同记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！