two table in one mssql statement?

Question

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 14-Oct-13 4:18am

tina_overgaard

Add a Solution

3 solutions

Solution 1

hi friend
Try inner joins query hope this will help u

Posted 14-Oct-13 4:26am

Balav4

Solution 3

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

Posted 14-Oct-13 15:10pm

sp_suresh

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

phil.o · Accepted Answer · 2013-10-14T04:28:00

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]