SQL Query

[choudhmh]

i have two database table, from one table i am selecting all fields to be displayed on a form. From another database i'm trying to display just one field. Both database use delegation to relate to each other.
Instead of writting another sql query out i was hoping will it be possible to merge the present query and retrive the data from the seceond query.
this is how the first one is done:

String sQuery = "SELECT * FROM License WHERE Name = '"+ label1.Text +"' AND LicenseGroupID = '"+ label2.Text +"'";

now i'm trying to retrive the LicenseGroup Name from the table LicenseGroup.
I dont want to write another query out. as you can see LicenseGroupID is used on both table to match fields to each other.

Is there any way i could use a combination factor tin the script to retrive the name from licengroup table.

Thanks
Mac

 

[bobisto]

I don't now what you mean with delegation.
If you want to get the field from two tables you should use JOIN.
String sQuery = "SELECT L.* FROM License L, LicenseGroup LG WHERE L.LicenseGroupID = LG.LicenseGroupID AND L.Name = '"+ label1.Text +"' AND L.LicenseGroupID = '"+ label2.Text +"'";

 

[obislavu]

Your code e.g. SQL statement should be independent on where the data is stored, in one database or two databases.
Use stored procedures to retreive the DataSet that you need by providing the value you need in the where clause as parameters.
The link between the two databases are defined in databases and you refer to in that stored procedure as well the select that will return the DataSet.
The bobisto's code should be in that stored procedure.
The query string should look like:

String sQuery ="EXEC sp_GetLicense 'name1', 'group1'");
//One example m_SQLConnection is a live connection
System.Data.SqlClient.SqlDataAdapter vSQLAdapter = new SqlDataAdapter(sQuery,m_SQLConnection); 
System.Data.DataSet vReturn = new System.Data.DataSet();   
vSQLAdapter.Fill(vReturn); 
// Here process the vReturn DataSet

obislavu

 

[choudhmh]

Guys thanks for your help.

In the data reader i have a field where the attribute "Name" is retrived from the database. But with the joint two database i have to retrive two name from two database. I cannot find a solution of declaring two different name from two different database on the same script.

Thisa is how it looks:
String sQuery = "SELECT * FROM License L, LicenseGroup LG WHERE L.LicenseGroupID = LG.LicenseGroupID AND L.Name = '"+ label1.Text +"' AND L.LicenseGroupID = '"+ label2.Text +"'";

SqlCommand cmd = new SqlCommand(sQuery, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();

if( dr.Read() )

{
label25.Text = dr[ "Name"].ToString();
label26.Text = dr[ "Name" ].ToString();
label27.Text = dr[ "Description" ].ToString();

The labe25.Text should retrive the name field from the LicenseGroup database, but i dont kow how to declare it in a way that the script undersatnd the name should be retrive from the LicenseGroup.

Thanks