选择数据表格二表 [英] Select data form two table

问题描述

CREATE TABLE [dbo].[tbl_ticket](
[nid] [int] IDENTITY(1,1) NOT NULL,
[ticketno] [bigint] NULL,
[memberid] [varchar](50) NULL,
[name] [varchar](200) NULL,
[category] [varchar](50) NULL,
[ticketstatus] [varchar](50) NULL CONSTRAINT [DF_tbl_ticket_ticketstatus]  DEFAULT ('Open'),
[bstatus] [bit] NULL CONSTRAINT [DF_tbl_ticket_bstatus]  DEFAULT ((1))
)

CREATE TABLE [dbo].[tbl_ticket_message](
[nid] [int] IDENTITY(1,1) NOT NULL,
[ticketno] [bigint] NULL,
[subject] [varchar](500) NULL,
[message] [varchar](2000) NULL,
[attachment] [varchar](50) NULL,
[messageType] [varchar](50) NULL CONSTRAINT [DF_tbl_ticket_message_messageby]  DEFAULT ('Question'),
[messageby] [varchar](200) NULL,
[bstatus] [bit] NULL CONSTRAINT [DF_tbl_ticket_message_bstatus]  DEFAULT ((1))
)

这是两个表，我只想选择 ticketno，主题，消息，附件列.

条件:
首先，我选择票证编号形式为tbl_ticket，其中memberid等于给定成员ID，然后选择主题消息附件形式tbl_ticket_message，其中票证编号等于，以选择先前的查询.

在此结果中，ticketno可以重复，但是我希望ticketno不重复，只返回每个ticketno的单个记录.

请帮助我.

These are two table, I want to select only ticketno, subject, message, attachment columns.

condition:
first I select ticket no form tbl_ticket where memberid is equels to given memberid then then select subject message attachment form tbl_ticket_message where ticketno is equals to select form previous query.

In this result ticketno may repeat but I want ticketno not not repeat return only single record of each ticketno.

Please help me.

推荐答案

您可以使用 Join 概念二从两个表中获取价值.

当我收到您的问题时，您希望基于单个公共匹配值获取两个表的数据.
试试这个代码

you can use Join Concept two get value from two table.

as I getting your question, you want to get data of two table based on single common matching value.
try this code

select e1.ticketno , e1.memberid,
e2.subject, e2.message,e2.attachment
from tbl_ticket e1 join
tbl_ticket_message e2
on e1.ticketno = e2.ticketno

如果只想要一个，您可以这样做:

If you want only one you could do this:

SELECT TOP 1 ticketno, subject, message, attachment columns
FROM tbl_ticket_message tm
WHERE tm.ticketno = @whatEverYouSelectedInYourFirstSelectForTicketNo

但这对我来说似乎有点随机.如果您只希望返回许多可能的消息之一，则实际上取决于哪个消息对您来说最重要.

干杯

Manfred

But that seems a bit random to me. It really dependes on what message is most important to you, if you only want one of possibly many to be returned.

Cheers

Manfred

CREATE PROCEDURE dbo.GetResult
@MemberID       VARCHAR(50)
AS
BEGIN
    SELECT DISTINCT ([ticketno]),
            [subject],
            [message],
            [attachment]
    FROM    [tbl_ticket_message]
    WHERE   [ticketno] = (SELECT [ticketno] FROM [dbo].[tbl_ticket] WHERE [memberid] = @MemberID)
END

希望对您有帮助.
一切顺利.

Hope this will help you.
All the best.

这篇关于选择数据表格二表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！