Fetch data from a database from two different tables

Question

I am using C#(asp.net). I have two tables(data and details) in a same database.

Table "data"

id | chap | unit |
  ----------------
  1| chap1|unit1 |
  2| chap2|unit2 |
  3| chap3|unit3 |

Table "details"

id| code| num |
----------------
 1|abc  |2    |
 2|efg  |3    |
 3|hij  |1    |

Now I want to fetch a value from "num" where code="efg" (in table "details"). And use the same value (3) to fetch data from table "data" by id. I am using this code.

OleDbConnection conn = new OleDbConnection(*** ...... *****); 
       OleDbCommand cmd;
       OleDbDataReader reader;
        String query = String.Format("select num from details where code="efg");
        cmd = new OleDbCommand(query, conn);
        reader = cmd.ExecuteReader();
        int num = int.Parse(reader.GetValue(0).ToString());

        query = String.Format("select chap from data where id={0}",num);
         cmd = new OleDbCommand("select lesson from data where id=3", conn);
        reader = cmd.ExecuteReader();


        Label1.Text = reader.GetValue(0).ToString();

But it shows error. It shows "No data exists for the row/column."

Marco · Accepted Answer · 2012-04-11 14:22:35Z

2

You can use

SELECT d.chap, d.unit
FROM data d INNER JOIN details de
    ON d.id = de.num
WHERE de.code = 'efg'

or

SELECT d.chap, d.unit
FROM data d INNER JOIN details de
    ON d.id = de.num
   AND de.code = 'efg'

More: if you're using SQL-Server, use SqlConnection in place of OleDbConnection.
More: don't format your query joining strings, numbers, dates and so on; use SqlParameter so you don't have to worry about types and formatting!!

edited Apr 11, 2012 at 14:22

answered Apr 11, 2012 at 14:16

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sagar Over a year ago

Actually i m using Microsoft access for database, that's why I am using 'OleDbConnection'. Sorry I mistakely tag sql server.

Marco Over a year ago

@Viktor: did my answer solved your problem? If so you should accept it if you think it's fine.

Alex · Accepted Answer · 2012-04-11 14:21:06Z

-1

Or you can use a subquery

SELECT * FROM data INNER JOIN 
    (SELECT num FROM details WHERE code='efg') det
ON data.id = det.num

answered Apr 11, 2012 at 14:21

Alex

23.3k4 gold badges43 silver badges76 bronze badges

2 Comments

Alex Over a year ago

This query is exactly the same as both of the two you provided in your answer, when it comes to execution, due to the Query Optimizer. Non-correlated is the keyword here. In fact, we have provided three different ways to make SQL perform the exact same task (execution plan can confirm this). Downvoter probably doesn't have a clue about how the optimizer works.

Marco Over a year ago

alex, what I wanted to show you is not that query itself is wrong, nor that query optmizer does not translate it in my same query; I'm sure you understand that a newbie like OP must be shown simpler and default way to write queries, because he is not able to understand what an execution plan is or how query optiomizer works :) Do you agree with me?

Collectives™ on Stack Overflow

Fetch data from a database from two different tables

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related