Click here to Skip to main content
15,922,315 members
Please Sign up or sign in to vote.
2.33/5 (2 votes)
See more:
Is it possible to have 2 table and select the information from both in the same MSSQL statment?

I have try this:

C#
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
Posted

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:
SQL
SELECT
  [frugt].[frugtid]
 ,[frugt].[whatever]
 ,[groent].[groentid]
 ,[groent].[whatever]
FROM [frugt] INNER JOIN [groent] ON [frugt].[frugtid] = [groent].[frugtid]

or
SQL
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:

SQL
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]
 
Share this answer
 
v3
Comments
tina_overgaard 14-Oct-13 10:44am    
My table could not be Inner join

CREATE TABLE [dbo].[groent] (
[groent_id] INT IDENTITY (1, 1) NOT NULL,
[groent_navn] NVARCHAR (50) NOT NULL,
[groent_vaerdi] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([groent_id] ASC)
);

CREATE TABLE [dbo].[frugt] (
[frugt_id] INT IDENTITY (1, 1) NOT NULL,
[frugt_navn] NVARCHAR (50) NOT NULL,
[vaerdi] NVARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([frugt_id] ASC)
);
phil.o 14-Oct-13 10:55am    
So you want to get all records from frugt table union all records from groent table?
See my updated answer.
tina_overgaard 14-Oct-13 10:56am    
Yes
tina_overgaard 14-Oct-13 11:22am    
Now it is mixed together, could I unmixed it.

Now it is like this:

frugt
groent
frugt
groent

And I want to do this:
frugt
frugt
frugt

groent
groent
groent
phil.o 14-Oct-13 16:04pm    
See my re-updated answer.
hi friend
Try inner joins query hope this will help u
 
Share this answer
 
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900