Multiple queries as one table
-
Hi guys, I have these three queries and i would like to join them as one table so that i can read from one table using my DataReader(). I don't want to use subqueries because certain queries will return more than one result and Sql Server does not like that one.
(
SELECT [number], [dateTime]
FROM [Database].[dbo].[table]
WHERE [dateTime]
BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59' AND
[number] NOT IN ('227000000', '2274000000', '2770000000')
)
(
SELECT RIGHT ([number_pair], 11) AS [number_pair] FROM [Database].[dbo].[table]
WHERE [number_pair] != '' AND [used] = '1' AND [number_pair] IN
(
SELECT [Database].[dbo].[table].[num_pair] FROM [Database].[dbo].[table]
WHERE [dateTime] BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59' AND
[number] NOT IN ('227000000', '2274000000', '2770000000')
)
)
(
SELECT column FROM [Database].[dbo].[table]
WHERE [sent] = '1' AND
[sent_date] BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59'
)Please anyone help me :doh: Thanks, Morgs
-
Hi guys, I have these three queries and i would like to join them as one table so that i can read from one table using my DataReader(). I don't want to use subqueries because certain queries will return more than one result and Sql Server does not like that one.
(
SELECT [number], [dateTime]
FROM [Database].[dbo].[table]
WHERE [dateTime]
BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59' AND
[number] NOT IN ('227000000', '2274000000', '2770000000')
)
(
SELECT RIGHT ([number_pair], 11) AS [number_pair] FROM [Database].[dbo].[table]
WHERE [number_pair] != '' AND [used] = '1' AND [number_pair] IN
(
SELECT [Database].[dbo].[table].[num_pair] FROM [Database].[dbo].[table]
WHERE [dateTime] BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59' AND
[number] NOT IN ('227000000', '2274000000', '2770000000')
)
)
(
SELECT column FROM [Database].[dbo].[table]
WHERE [sent] = '1' AND
[sent_date] BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59'
)Please anyone help me :doh: Thanks, Morgs
The three queries don't return the same number of columns so they can't be combined using "union". So first you need to make the three queries to return the same number of columns with corresponding columns having the same data types. Then you can use "union" or "union all" to combine them into one big table.
-
The three queries don't return the same number of columns so they can't be combined using "union". So first you need to make the three queries to return the same number of columns with corresponding columns having the same data types. Then you can use "union" or "union all" to combine them into one big table.
Hi Loyal, Thanks for your reply, well the problem is that these queries will return different number of columns and rows.e.g 1st query will return 3 columns, 2nd and 3rd will return 1 column
-
Hi Loyal, Thanks for your reply, well the problem is that these queries will return different number of columns and rows.e.g 1st query will return 3 columns, 2nd and 3rd will return 1 column
How do you expect to read from one table if they all have different number of columns? You can have 3 select queries in 1 stored procedure and still use
DataReader. The DataReader class contains aNextReader()method which moves onto the next table in the resultset. -
How do you expect to read from one table if they all have different number of columns? You can have 3 select queries in 1 stored procedure and still use
DataReader. The DataReader class contains aNextReader()method which moves onto the next table in the resultset.Thanks alot, i will try this one
-
Hi Loyal, Thanks for your reply, well the problem is that these queries will return different number of columns and rows.e.g 1st query will return 3 columns, 2nd and 3rd will return 1 column
That is not a problem. Simply determine which query returns the most columns, and add dummy columns to the other queries, such that each query returns the same amount of columns. use 0 for columns with numerical values, and '' for columns with text values. So for example:
SELECT NumericalColumn1, NumericalColumn1, textColumn1
FROM Table1
WHERE blabla
UNION ALL
SELECT NumericalColumn1, 0, ''
FROM Table2
WHERE blabla
UNION ALL
SELECT 0, 0, textColumn1
FROM Table3
WHERE blablaMy advice is free, and you may get what you paid for.