两个表在一个mssql语句中？ [英] two table in one mssql statement?

问题描述

是否可以有2个表并从同一个MSSQL语句中选择两个信息？



我试试这个：

 SqlConnection conn =  new  SqlConnection（）; 
 conn.ConnectionString = ConfigurationManager.ConnectionStrings [  ConnectionString]。ToString（）; 
 
 SqlCommand cmd =  new  SqlCommand（）; 
 cmd.Connection = conn; 
 cmd.CommandText =   SELECT frugt *，groent * FROM frugt，groent; 
 
 
 
 conn.Open（）; 
 DataTable visProdukter =  new  DataTable（）; 
 SqlDataAdapter adapter =  new  SqlDataAdapter（cmd）; 
 adapter.Fill（visProdukter）; 
 cmd.ExecuteNonQuery（）; 
 conn.Close（）; 
 VisProdukt.DataSource = visProdukter; 
 VisProdukt.DataBind（）; 
 

但它使用数据库中的所有信息返回10次或更多相同的ID



希望有人可以帮助我。我做错了什么



/ Tina

解决方案

SELECT * 是一个非常坏的习惯;最好只是询问你需要的列，并明确地命名它们。



此外，使用MS SQL Server，你必须指定两个表之间的连接;如果不知道确切的列名，很难说，但这可能是这样的：

  SELECT  
 [frugt]。[frugtid] 
，[frugt]。[无论如何] 
，[groent]。[groentid] 
，[groent]。[无论如何] 
  FROM  [frugt]  INNER   JOIN  [ groent]  ON  [frugt]。[frugtid] = [groent]。[frugtid] 

或

  FROM  [frugt]  INNER   JOIN  [groent]  ON  [frugt]。[groentid] = [groent]。[groentid] 

取决于两个表彼此之间的关系。



解决方案 UNION 查询：

  SELECT  
 '  frugt'  AS  类型 
，[frugt]。[frugt_id]  AS  Id 
，[frugt]。[frugt_navn]  AS 导航
，[frugt]。[vaerdi]  AS  Vaerdi 
  FROM  [frugt] 
 
  UNION  
 
  SELECT  
 '  groent' 
，[groent]。 [groent_id] 
，[groent]。[groent_navn] 
，[groent]。[groent_vaerdi] 
  FROM  [groent] 
 
  ORDER   BY  类型 

[/ edit]

嗨朋友

尝试内部联接查询希望这将有助于你

我建议您使用UNION ALL，其中两个表记录值都将显示，无论是否加入

例如 -

选择sTpNoFk，来自ETp的iPurchaseCodeFk

Union ALL

从BottleSizeMaster中选择iBottleSize，iPerCaseBottles

Is it possible to have 2 table and select the information from both in the same MSSQL statment?

I have try this:

SqlConnection conn = new SqlConnection();
           conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

           SqlCommand cmd = new SqlCommand();
           cmd.Connection = conn;
           cmd.CommandText = "SELECT frugt*, groent* FROM frugt, groent";



           conn.Open();
           DataTable visProdukter = new DataTable();
           SqlDataAdapter adapter = new SqlDataAdapter(cmd);
           adapter.Fill(visProdukter);
           cmd.ExecuteNonQuery();
           conn.Close();
           VisProdukt.DataSource = visProdukter;
           VisProdukt.DataBind();

But it return the same ID 10 times or more with all the information in the database

Hope someone could help me. What I am doing wrong

/Tina

解决方案

SELECT * is a very bad habit ; better just ask for the columns you need, and name them explicitely.

Moreover, with MS SQL Server you have to specify the joins between both tables ; without knowing the exact column names, it's hard to tell, but this could be something like:

SELECT
  [frugt].[frugtid]
 ,[frugt].[whatever]
 ,[groent].[groentid]
 ,[groent].[whatever]
FROM [frugt] INNER JOIN [groent] ON [frugt].[frugtid] = [groent].[frugtid]

or

FROM [frugt] INNER JOIN [groent] ON [frugt].[groentid] = [groent].[groentid]

depending on how both tables are related to each other.

[edit] Solution for an UNION query:

SELECT
  'frugt' AS Type
 ,[frugt].[frugt_id] AS Id
 ,[frugt].[frugt_navn] AS Navn
 ,[frugt].[vaerdi] AS Vaerdi
FROM [frugt]

UNION

SELECT
  'groent'
 ,[groent].[groent_id]
 ,[groent].[groent_navn]
 ,[groent].[groent_vaerdi]
FROM [groent]

ORDER BY Type

[/edit]

hi friend
Try inner joins query hope this will help u

I would suggest you to use UNION ALL where both table records values will be displayed irespective of join
for e.g-
select sTpNoFk ,iPurchaseCodeFk from ETp
Union ALL
select iBottleSize,iPerCaseBottles from BottleSizeMaster

这篇关于两个表在一个mssql语句中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！