|
Perhaps you want a UNION ?
|
|
|
|
|
Correct something like that. I tried few in google but didn't find anything helpful. So I want to ask experts in here to find a solution.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
I also read something below
the number of columns appears in the corresponding SELECT statements must be equal.
The columns appear in the corresponding positions of each SELECT statement must have the same data type or at least convertible data type.
I don't have same number of columns that must be selected. Neither of same datatype
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
Put in nulls for the tables that do not have enough columns.
Change the data type by using CAST or CONVERT on the column within the select statement.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Normally you would use union or union all. So in this case
Select T1CL1,T1CL2 from T1 where T1CL3 = 'Something'
union all
Select T2CL1,T2CL3 from T2 where T1CL2 = 'prm1'
union all
Select T3CL3,T3CL4 from T3 where T3CL2 = 'prm2'
union all
Select T4CL1,T4CL3 from T4 where T4CL2 = 'prm3'
union all
Select T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'
However you said that you actually have different amount of columns and the data types are do not match or cannot be converted. If that really is the case, then you need to fetch multiple result sets from a single procedure.
Also if you can execute the statement as a batch from client side, then have a read at Executing multiple SQL statements as one against SQL Server[^]
|
|
|
|
|
Can I use Join if there is a Table T6 which has all the primary keys of T1,T2,T3,T4 and T5?
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
You can use join in a normal way on each select
|
|
|
|
|
You don't need to enable MARS to fetch multiple result-sets from a single query; you just load each result-set sequentially. A SqlDataAdapter will take care of that for you, or you can call IDataReader.NextResult in a loop, loading each result-set as you go.
You only need MARS if you're going to have multiple active data-readers open on the same connection at the same time.
In other words:
using (var connection = new SqlConnection("..."))
using (var outerCommand = new SqlCommand("...", connection))
{
connection.Open();
using (IDataReader outerReader = outerCommand.ExecuteReader())
{
while (outerReader.Read())
{
using (var innerCommand = new SqlCommand("...", connection))
{
using (IDataReader innerReader = innerCommand.ExecuteReader())
{
while (innerReader.Read())
{
...
}
}
}
}
}
}
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
connection.Open();
using (IDataReader reader = command.ExecuteReader())
{
do
{
while (reader.Read())
{
...
}
}
while (reader.NextResult())
}
}
var dataSet = new DataSet();
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
var adapter = new SqlDataAdapter(command);
adapter.Fill(dataSet);
}
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you Rich
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
That's true. Don't know what I was thinking... Thanks for pointing that out.
|
|
|
|
|
Hello,
i had done this in oracle. see what i had did.
created the tables
create table tqw1(t1c1 varchar2(10),t1c2 varchar2(10),t1c3 varchar2(10),t1c4 varchar2(10));
create table tqw2(t2c1 varchar2(10),t2c2 varchar2(10),t2c3 varchar2(10),t2c4 varchar2(10));
create table tqw3(t3c1 varchar2(10),t3c2 varchar2(10),t3c3 varchar2(10),t3c4 varchar2(10));
create table tqw4(t4c1 varchar2(10),t4c2 varchar2(10),t4c3 varchar2(10),t4c4 varchar2(10));
insert some random data into those tables
insert into tqw1(t1c1,t1c2,t1c3,t1c4) values (7,2,3,4);
insert into tqw2(t2c1,t2c2,t2c3,t2c4) values (8,3,4,5);
insert into tqw3(t3c1,t3c2,t3c3,t3c4) values (9,4,5,6);
insert into tqw4(t4c1,t4c2,t4c3,t4c4) values (10,5,6,7);
see there was no relation among these tables. so i had used alias with the tables to select a particular data set
select Q.T1C1,W.T2C2,E.T3C3,R.T4C4 from tqw1 q,TQW2 w,TQW3 e, TQW4 r ; need the use where clause to filter the data
|
|
|
|
|
add one column of to identify the table name like
Select 'T1' as tbl, T1CL1 as col1,T1CL2 as col2 from T1 where T1CL3 = 'Something'
union all
Select 'T2', T2CL1,T2CL3 from T2 where T1CL2 = 'prm1'
union all
Select 'T3', T3CL3,T3CL4 from T3 where T3CL2 = 'prm2'
union all
Select 'T4',T4CL1,T4CL3 from T4 where T4CL2 = 'prm3'
union all
Select 'T5',T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'
|
|
|
|
|
Try to use cross join with the tables & select recpective column which you require.
hi
|
|
|
|
|
I have mysql table which stores students marks. The first column represent the unique Regno For each student, the second reprsents TotalScore and the rest of the rows represent units name. Note...The columns representing different can be increased if a new unit is added. Therefore i wish to know how i can loop through the records of each student ,add them and insert into TotalScore column...am still new to vb.net. Kindly assist
the table is as follows:
REGNO TOTALSCORE UNIT1,unit2,unit3,unit4,...UNITn
1
2
3
.
.
n
-- modified 17-Feb-15 23:49pm.
|
|
|
|
|
KipkoechE wrote: i wish to know how i can loop through the records of each student and insert into TotalScore column
What do you want to insert into the TotalScore column?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
The sum of each row as identified by the regno
|
|
|
|
|
How about executing the query:
UPDATE tablename SET TotalScore = (unit1 + unit2 + unit3 + unit4) // and so on?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
AM trying to avoid that method because the number of columns can be changed at runtime
|
|
|
|
|
KipkoechE wrote: the number of columns can be changed at runtime
How is that possible? Are you creating a new table with a different number of columns and then deleting the first table?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
By altering the table when a new unit is added
|
|
|
|
|
Column1 Column2
-------- ----------
Vaugh William
Vaugh Smith
Woods Jane
Expected Output :
Vaugh, William-Smith/Woods,Jane
Condition : There could be n number of rows in the sample table, there could be n number of woods too.
display all the common surname having different names meaning repeated Last names will be once then all the first names associated with it.
truncated at 30 characters max.
How can I get this output in SQL query ?
|
|
|
|
|
This is not clear.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
What extra information will make it clear ?
|
|
|
|
|
It looks like you want a single record as your output? What are the conditions?
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
yes there is just single cell output.
but conditions are that cell can not have more than 30 characters.
Even there are 10 or 15 rows in a table, the SQL query should give single cell output.
|
|
|
|