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

Alphanumeric Paging on GridView?

Hello,

I have a SQL database with about 300 company names and corresponding phone numbers. I would like to show a list of linkbuttons titled A-Z and when pressed, rebind the sqldatasource so that my GridView will only show company names that start with that letter.

I know there are some examples on codeproject.com, but they are a bit over my head... besides, I don't mind writing a custom select statement for the OnClick of every linkbutton if that's what I have to do. Problem is I haven't a clue how to write a select statement that will return items who's first letter matches my desired letter?

Any idea?

Thanks,

-Derek

The basic sql statement looks like this:

SELECT *FROM CompanyTableWHERE CompanyNameLIKE'A%';

The percentage is the sql wildcard character that matches any number of characters when combined with the LIKE operator

Since you need to not hard code the letter you are searching for, you'll want to use a parameterized sql statement and you would put the wildcard into the parameters value leaving you with a sql statement like this:

SELECT *FROM CompanyTableWHERE CompanyNameLIKE @.P1;

|||

Hey Thanks!

That worked like a charm! I can now filter it based on what linkbutton is pressed. I'm still a bit fuzzy on the parametrized statement though... I understand it and all, but where and how would I set the parameter to each letter? On the OnClick of each linkbutton? If so, I would be still be writing out all 26 OnClick events?

On a side note, I cant believe how simple this filtering thing really is. I think the fellas over at codeproject are really over complicating it ;) Thanks again for your response,

-Derek

|||

add a usercontrol to encapsulate the Alphabet linkbuttons

PartialClass AlphabetBarInherits System.Web.UI.UserControlPublic Event Click(ByVal valueAs String)Protected Sub Page_Init(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Init'dynamically create a series of linkbuttonsFor keycodeAs Integer = 65To 90'one for each letter in the alphabetDim lnkAs New LinkButton lnk.Text = Chr(keycode) lnk.CommandArgument = Chr(keycode)AddHandler lnk.Click,AddressOf onClick'have them all use the same event handlerMe.Controls.Add(lnk)Me.Controls.Add(New LiteralControl(" "))'space them outNext End Sub Private Sub onClick(ByVal senderAs Object,ByVal eAs EventArgs)Dim lnkAs LinkButton =DirectCast(sender, LinkButton)'raise a single event 'using the clicked links commandargument as our events argumentRaiseEvent Click(lnk.CommandArgument)End SubEnd Class

Now drag that control onto your page and add a handler for its new Click event

Protected Sub AlphabetBar1_Click(ByVal valueAs String)Handles AlphabetBar1.Click'value is the alphabet letter that was clicked on in the usercontrol 'now we can build our filterDim filterParameterAs String = value &"%"'... '...End Sub

All SP's slow than corresponding query

I am working on a database the eventually hold a large amount of data.
For some reason, now, all my stored procedures that used to run quickly, now
a lot slower than the exact same as a query int he analyzer. The plans show
exactly the same statistics, but SP takes about 10 times longer.
Any ideas out there?
TIA1. Check the query plans to see if they are the same..
2. One common potential problem is using local variables in a where clause
in a SP... At the time the sp is optimized, the optimizer CAN see all of the
sp parameter values, but local variables do not get a value until run
time... Therefore the optimizer can NOT use index statistics on these
queries to determine the most useful index, which can lead to performance
problems in sps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Is it possible that they are recompiling for some reason? Check with
Profiler, you have SP:Recompile and some other events.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"DWinter" <dwinter@.attbi.com> wrote in message
news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> I am working on a database the eventually hold a large amount of data.
> For some reason, now, all my stored procedures that used to run quickly,
now
> a lot slower than the exact same as a query int he analyzer. The plans
show
> exactly the same statistics, but SP takes about 10 times longer.
> Any ideas out there?
> TIA
>|||Yes, the plan is the same, an no local variables used. When I say All, I
mean all stored procedures are slower. Something is wrong. Don't know if
some setting got set somehow or what.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> 1. Check the query plans to see if they are the same..
> 2. One common potential problem is using local variables in a where clause
> in a SP... At the time the sp is optimized, the optimizer CAN see all of
the
> sp parameter values, but local variables do not get a value until run
> time... Therefore the optimizer can NOT use index statistics on these
> queries to determine the most useful index, which can lead to performance
> problems in sps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > I am working on a database the eventually hold a large amount of data.
> > For some reason, now, all my stored procedures that used to run quickly,
> now
> > a lot slower than the exact same as a query int he analyzer. The plans
> show
> > exactly the same statistics, but SP takes about 10 times longer.
> >
> > Any ideas out there?
> >
> > TIA
> >
> >
>|||does the execution plan show a hash join or a hash match
operation?
if so, are the more than 10,000 rows involved in that op?
read BOL for join types, at some point the hash join
switches from in memory to grace or recursive.
the setting is different for RPC and SQL Batch.
From an ADO client program, if you append parameters, the
proc will show up in Profiler as RPC, anything you do in
QA shows up as SQL Batch.
if this is the case, there are a few things you can do to
avoid this
>--Original Message--
>I am working on a database the eventually hold a large
amount of data.
>For some reason, now, all my stored procedures that used
to run quickly, now
>a lot slower than the exact same as a query int he
analyzer. The plans show
>exactly the same statistics, but SP takes about 10 times
longer.
>Any ideas out there?
>TIA
>
>.
>|||It was a has issue. Sorry to inconvenience you guys.
I should have seen it earlier.
"Yovan Fernandez" <yfernandez@.sai-inc.com> wrote in message
news:mwm9b.2723$ev2.1916726@.newssrv26.news.prodigy.com...
> I have experienced this problem before on another DB i had, it has to do
> with Recompiling like Dejan said, one way to stop recompiling is to turn
> auto statistics off I really think having DB option AUTO_UPDATE_STATISTIC
on
> And when you said all are you including system sp or just user sps?
> Yovan Fernandez
>
> "DWinter" <dwinter@.attbi.com> wrote in message
> news:%23zA79w6eDHA.3216@.tk2msftngp13.phx.gbl...
> > Yes, the plan is the same, an no local variables used. When I say All, I
> > mean all stored procedures are slower. Something is wrong. Don't know if
> > some setting got set somehow or what.
> >
> > "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> > news:%235dcMr6eDHA.1736@.TK2MSFTNGP12.phx.gbl...
> > > 1. Check the query plans to see if they are the same..
> > > 2. One common potential problem is using local variables in a where
> clause
> > > in a SP... At the time the sp is optimized, the optimizer CAN see all
of
> > the
> > > sp parameter values, but local variables do not get a value until run
> > > time... Therefore the optimizer can NOT use index statistics on these
> > > queries to determine the most useful index, which can lead to
> performance
> > > problems in sps.
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Computer Education Services Corporation (CESC), Charlotte, NC
> > > www.computeredservices.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > >
> > > "DWinter" <dwinter@.attbi.com> wrote in message
> > > news:uPOjaf6eDHA.560@.tk2msftngp13.phx.gbl...
> > > > I am working on a database the eventually hold a large amount of
data.
> > > > For some reason, now, all my stored procedures that used to run
> quickly,
> > > now
> > > > a lot slower than the exact same as a query int he analyzer. The
plans
> > > show
> > > > exactly the same statistics, but SP takes about 10 times longer.
> > > >
> > > > Any ideas out there?
> > > >
> > > > TIA
> > > >
> > > >
> > >
> > >
> >
> >
>|||> It was a has issue. Sorry to inconvenience you guys.
> I should have seen it earlier.
No inconvenince, we are glad you worked it out, that's why newsgroups are
for :-)
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org