How to create master detail Select query

peter piper

I have a form and 2 sql tables. Table 1 contains header info and table 2 details info
A button that starts the process of writing data from the tables to an application.

private void button1_Click(object sender, EventArgs e)
{
using (SqlCommand cmd = new SqlCommand(@SELECT col1,col2,col3,col4 FROM tbl1,conn))
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
writeHeadData(rdr[“col1”], rdr[“col2”], rdr[“col3”], rdr[“col3”],rdr[“col4”]);
}
}

private void writeHeadData(string col1,string col2,string col3,string col4)
{
//do something here (writeHeadData)
writeDetailData(col1, col2, col3, col4)
}

private void writeDetailData(string col1,string col2,string col3,string col4)
{
//do something here with data from the details table
}

Where do I put the query from the detail table (see below) and ensure that the detail info relates to the header info using the WHERE clause

using (SqlCommand cmd = new SqlCommand(@SELECT * FROM tbl2,conn))
using (var rdr = cmd.ExecuteReader())
while (rdr.Read())
{
writeHeadData(rdr[“col1”], rdr[“col2”], rdr[“col3”], rdr[“col3”],rdr[“col4”]);
}

There are 3 details rows for every header row

I hope whoever reads this understands what I’am trying to achieve and if not clear please tell me and I shall try and elucidate further

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Looks like there is some confusion. How come the parameter values will come from database?

Do you intend to list all orders for all customer in database? That sounds hugely unlikely. Please clarify the desired output of this excercise. May be too late to ask for!
You will at the list have a parameter of a month number. Say you want to list orders for a particular month.
peter piper9y ago
The parameter values surely will come from the database itself ?
Should I insert a query here ?
I am quite a beginner on this front so defer to your better knowledge
peter piper9y ago
Hi Nitin.
Thanks for your response.
Yes, I see the humour

Transplanted your code but I have squiggly red lines (see
attached jpeg) and I don't know how to get rid of them.
Am I missing/omitted something ?
I like the way you handled the query. Never seen it done
that way before. Will change the date variables as you
suggest.
Nitin Sontakke9y ago
Two things:

First, you were calling WriteDetailData from within WriteHeaderData, which I am not doing. I am calling them separately from processOrder method. So remove, line 24 and 25 (in the image) from the code altogether.

Second, you almost definitely will have some sort of parameter values. I had imagined them to be text boxes on the form and given imaginary names there. Feel free to remove it. However, you will somehow need to send three parameter values to processOrder method to work.

Changing the datatype of date from String to Date will also warrant for code modification.

Hope it helps!
peter piper9y ago
Hi Nitin

Please see attached folder with all the info.
Hope you can help me unravel this
peter piper9y ago
Hi Nitin
Apologies for the delay.
I locked myself out of the house.
Had to wait for my wife to return.
Will send over the tables and script to you asap
Nitin Sontakke9y ago
Yes, please send the script of two tables involved.

Of course there should be a way to uniquely identify the row in header table and must be a way to join it to detail table, which may have multiple rows.

I am sure this has already been taken care of.

If there are more than one columns, all you have to do is use them all in the join with and condition as shown below:

left outer join table1 t1 on t1.col1 = t2.col1 and t1.col2 = t2.col2 [and so on...]
peter piper9y ago
I am refering to the common field you mentioned in your initial response, "the column that is common between these two tables"
There might be several orders by the same customer on the same day having an order number that may be the same as an order for another customer on the same day. So shouldn't there be a unique or distinct row of some kind when creating the query ?
Can I send you the 2 tables ? That may help I think
Nitin Sontakke9y ago
Not sure if I understand your question. It would be helpful if you share the table structure.

Index is not mandatory, but highly recommended that you have one.

You should also avoid to write so much code under button click. For all such things write separate classes to perform tasks.
peter piper9y ago
Thank you for your reply Nitin.
Apologies for omiting info about the common columns.
To get a unique field or set of fields there should be an index on the first 3 columns in table 1, ie. customer,orderDate and orderNo, whereas the detail table will also have a customer field as well as the orderNo and orderDate. Is that correct ?

As to your query, please tell me where I should put in relation to the two blocks of code and is it stated only once ?

I shall try out what you have sent as if it were just one query which I shall place behind the button
Nitin Sontakke9y ago
Whenever you say there is header row and a detail row and you say for every one header row there are three detail rows, why would you not mention which is the column that is common between these two tables? Wouldn't that be helpful in replying to your question? While posting questions on the forum, please do not assume anything. Anyway.

Assuming that column is col1 (by the way, I hope these are not the real column names. If they are they are very very bad) you can pass the value of col1 from header row to the function which fetches the detail row.

Assuming the parameter name is col1value, your query to get detail data would be:

using (SqlCommand cmd = new SqlCommand("SELECT col1, col2, col3, col4 FROM tbl2 where col1 = '" + col1value + "'",conn))

If it is possible for you handle properly, it is much better to get all the rows in one go and ignore the repeating header columns. For example,

select t1.col1, t1.col2, t1.col3, t1.col4, t2.col1, t2.col2, t2.col3, t2.col4
from table2 t2 -- This is a detail table
left outer join table1 t1 on t1.col1 = t2.col1
where t2.col1 = ? -- In place of question mark, put the appropriate value.

The above is the best approach.