Alternate rows from different table

Dear All,
I have a requirement in which I have to display a row from one
table & the corresponding row from the another table. e.g. say there
are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
corresponding record in T2 as R1' then the display would come as
R1 /* Data from Table 1 */
R1' /* Data from Table 2 */
R2
R2'
& so on.....
This is possible by manipulating the resultset in a program. But I
would like to know if it is possible in the SQL query.
Thanks & Regards,
Praveenpkb wrote:

> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
Looks like a UNION to me. Assuming r is the common column that
determines R1, R2, etc, try:
SELECT r, col1, col2, ...
FROM
(SELECT r, 1 AS tbl, col1, col2, ...
FROM tbl1
UNION ALL
SELECT r, 2 AS tbl, col1, col2, ...
FROM tbl2) AS T
ORDER BY r, tbl ;
If that's not what you wanted then my signature explains how to post
better questions so that you can get better answers. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"pkb" <praveen.bhushan@.gmail.com> wrote in message
news:1138803314.265069.114980@.g49g2000cwa.googlegroups.com...
> Dear All,
> I have a requirement in which I have to display a row from one
> table & the corresponding row from the another table. e.g. say there
> are 2 tables T1 & T2. Suppose there is a record in T1 say R1 & the
> corresponding record in T2 as R1' then the display would come as
> R1 /* Data from Table 1 */
> R1' /* Data from Table 2 */
> R2
> R2'
> & so on.....
> This is possible by manipulating the resultset in a program. But I
> would like to know if it is possible in the SQL query.
> Thanks & Regards,
> Praveen
You could:
select 1 AS rank, ... from T1 where ...
union
select 2 AS rank, ... from T2 where ...
Order by (you will have to add the corresponding columns, whatever that is),
rank
Sorry, can't give you anything more detailed without your table structure.|||Hi David, Raymond,
Thanks for your quick replies. The only problem in the above
solution will come when there is a record which is present in
one of the tables. Actually I wanted to make pairs from the two tables.
Well I have got the idea.
Regards,
Praveen

Allocate Range of AutoNumber ID's to each user

I have a non standard requirement that about 5 different users from differen
t
departments require sequential numbering for there records, however I want
all records to be in the one central table. This comes about as each
departments records ID's have a 4 letter acronym before the AutoNumber ID.
Is this possible or should I create seperate tables and then join them using
a view with union queries?
Any suggestions would be greatly appreciated.
ThanksHi
You can use INSTEAD OF TRIGGER to achieve this business logic
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Hi,
Not sure how to use this, do you have a link or sample I could work from.
Much appreciated.
"Chandra" wrote:
> Hi
> You can use INSTEAD OF TRIGGER to achieve this business logic
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Hi,
You can try this way
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE>
INSTEAD OF INSERT
AS
INSERT INTO <TABLE>
SELECT <your logic>, required columns
FROM INSERTED
WHERE INSERTED.Key = Key
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Hi,
> Not sure how to use this, do you have a link or sample I could work from.
> Much appreciated.
> "Chandra" wrote:
>|||Thanks.
So best to set up my 5 tables which fulfills the business requirement and
then uses 'Instead of Triggers' to generate a master table with all results.
The only other query I have is how does the key part of the query work.
Thanks
"Chandra" wrote:
> Hi,
> You can try this way
> CREATE TRIGGER <TRIGGER_NAME>
> ON <TABLE>
> INSTEAD OF INSERT
> AS
> INSERT INTO <TABLE>
> SELECT <your logic>, required columns
> FROM INSERTED
> WHERE INSERTED.Key = Key
> GO
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Key is the primary key value in the table. If you are sure that you will be
inserting only one row at a time, then you can avoiding the key.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Thanks.
> So best to set up my 5 tables which fulfills the business requirement and
> then uses 'Instead of Triggers' to generate a master table with all result
s.
> The only other query I have is how does the key part of the query work.
> Thanks
> "Chandra" wrote:
>|||First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that make
s
no sense whatsoever. This is NOT a "non standard" requirement and is is quit
e
common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
mess of a system. This requirement is known as pre-allocated numbers. Most
banks I know do this.
I don't know what the numbers are for, so I'll assume Orders. Here's how
it's typically done:
TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
PROCEDURE Create_New_Order (@.Dept_Id,...) {
BEGIN TRANSACTION
@.Ord_Num =
SELECT Order_Num
FROM Avaiable_Order_Numbers
WHERE Dept_Id = @.Dept_Id
INSERT INTO Orders (@.Ord_Num, ...)
DELETE FROM Avaiable_Order_Numbers
WERE Order_Num = @.Order_Num
COMMIT
}
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Thanks for your assistance Alex. Looked at the Instead of Trigger option an
d
it seemed messy!
Basically if you use 5 tables one for each department with there own
sequential identity value incrementing concatenated with the Dept. acronym
and then put a trigger on Insert, Update (There is no delete facility) to
build a consolidated/master table for analysis, this will provide a robust
solution.
Any comments appreciated!
"Alex Papadimoulis" wrote:
> First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that ma
kes
> no sense whatsoever. This is NOT a "non standard" requirement and is is qu
ite
> common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
> mess of a system. This requirement is known as pre-allocated numbers. Most
> banks I know do this.
> I don't know what the numbers are for, so I'll assume Orders. Here's how
> it's typically done:
> TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
> TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
> PROCEDURE Create_New_Order (@.Dept_Id,...) {
> BEGIN TRANSACTION
> @.Ord_Num =
> SELECT Order_Num
> FROM Avaiable_Order_Numbers
> WHERE Dept_Id = @.Dept_Id
> INSERT INTO Orders (@.Ord_Num, ...)
> DELETE FROM Avaiable_Order_Numbers
> WERE Order_Num = @.Order_Num
> COMMIT
> }
>
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "MarkCapo" wrote:
>

All rows in a Sigle page

Hi guys,
I have requirement to show all the data from a table in a single page
or minimum 100 rows in the first page . How it is possible. Please any
body can solve my query.
Thanks in advance.
ShrinivasYou can go to report properties and adjust the page height in layout
tab.
Shrinivas wrote:
> Hi guys,
>
> I have requirement to show all the data from a table in a single page
> or minimum 100 rows in the first page . How it is possible. Please any
> body can solve my query.
> Thanks in advance.
> Shrinivas