|
I believe what you are looking for is known as an "Outer Join".
|
|
|
|
|
Thanks, but I believe LEFT JOIN and LEFT OUTER JOIN are interchangeable with the database I am using. I believe, though, that with the LEFT JOIN, if there is more than one record in the 'right' table that matches the fields in the left table, then the additional records are also included in the result set. What I need is just one particular matching record from the 'right' table; in this case, the last matching record from the 'right' table.
|
|
|
|
|
ASALAMOALEKUM All,
i am working on virtual community and i am struct in btw...
actuall i have to maintain friend network, in this regard i have a Friend table in my sql database which contain columns of User_id and Friens_id (none of them is primary)
example: if table view is:
serial# User_id Friend_id
0 001 007
1 001 003
2 002 009
3 003 005
4 004 002
5 005 010
now if user 001 visits the profile 0f user with id 010 so as a friend network information it shud be shown "001->003->005->010"
the retrieval of this(which is between quotes) information is creating problem for me, can anyone suggest me a sql query for this... ??
reply as soon as possible!
i wud be very thankful!
Sana
$@N@
|
|
|
|
|
What database are you using? SQL Server, Oracle, something else?
The need to optimize rises from a bad design
|
|
|
|
|
i am using SQL server
$@N@
|
|
|
|
|
|
|
Thanks alot!
i will read it and will inform u if my problem is get resolved through this...!!
thnks again!
if you know any ebook based on virtual community making using asp.net with c# and sql server please recommend me!
regards,
God bless you!
SANA AHMAD
$@N@
|
|
|
|
|
There is a "ent.tbl_NetworkProfileTemplate" table in the system
with the following columns
PK_NetworkProfileTemplateId,
ProfileTemplateName,
ProfileTemplateDescription,
FK_TenantId
For TenantId =1 there are about 50000 records and for TenantId = 2 there are 1000000 records.
There is also another table tbl_Tenant which contains information about the tenant.
Now When I execute the following statement by logging in with tenant = 1
select * from ent.tbl_NetworkProfileTemplate
where ent.tbl_NetworkProfileTemplate.FK_TenantId
= ( select PK_TenantId FROM ent.tbl_Tenant WHERE TenantDBUserName = System_User)
This query runs very fast takes less than second to execute and returns number of records = 50000
But if I execute the following statement it takes 45 seconds
select * from ent.tbl_NetworkProfileTemplate
where ent.tbl_NetworkProfileTemplate.FK_TenantId
= [ent].GetTenantId()
The only thing I am doing in this statement is calling a UDF GetTenantId which actually executes the statement
select PK_TenantId FROM ent.tbl_Tenant WHERE TenantDBUserName = System_User
I am not able to understand why the response time is so different.
|
|
|
|
|
You are calling the UDF for every single record, hence the extended run time.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks. Got it.
Can you please suggest how to call UDF only one time...is there any way?
|
|
|
|
|
It all depends on what it does. I have, where the UDF is not too complex, extracted the code and used it either in the select or used a temp table and done an update.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Assuming the UDF return 1 value then run the UDF first and load the return value into a variable. Or go back to the original version
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i was working with SQL Server 2000.recently i purchased a new laptop wherin i got preinstalled Windows Vista home with SQL Server 2005 installed.i find it very uncomfortable to work on it.
can any one tell me how can i install SQL Server 2000 in windows vista or otherwise how to create Databases and Tables in Vista.
Thanx a Lot
|
|
|
|
|
You can install SQL Server 2000 on Vista without problems, and without removing SQL Server 2005, but why not bite the bulet and stop using old technology - unless there is a very good reason.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Good Morning
i have Migrated a DTS package from SQL 2000 to SSIS, and i have used BI to make changes and deploy it and i have set the Option of "Run64bitRuntime" to False and have deplyed my packages and when i run the packages i got an Error that said.
This task does not support native Win64 environment. Please run the package in 32-bit WOW environment instead.
i have Googled that and took the Solution that people had and on the Steps in the Job "Type" i choosed "Operating System (CmdExec)" and in the command i have
"F:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn" /SQL \RECREATE_PROPERTY_SUMMARY" /SERVER cenetsqld001 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
and when i run the Job, i get the following Error
Message
Executed as user: TSHWANE\cenetsqld001sql. The process could not be created for step 1 of job 0x049B2E7908970C49A9BA5A5796E5AC30 (reason: 5). The step failed.
What is wrong
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
hello everyone!
I am trying to convert the below sql into oracle but i am having some issues with the variable declarations.
ex.
declare @md date
set @md='7/1/2008'
select @md
in oracle how?
any ideas?
Thank you!
|
|
|
|
|
run this is SQLDeveloper and
set serveroutput on;
declare
md date default '01-JUL-08';
begin
select '01-JUL-08' into md from dual;
dbms_output.put_line( md );
end;
hth
Al
|
|
|
|
|
thank you for reply ,that was what i want!
|
|
|
|
|
thank you for reply ,that is what i want!
|
|
|
|
|
All of that just to output a date  I'll stick to SQL Server
|
|
|
|
|
They both have their advantages and disadvantages...
hth
Al
|
|
|
|
|
Is this simplified if you use something like TOAD? While I have not used Oracle in a decade it was always lacking a good UI and we found TOAD the equivalent of the old SQL Query Analyser. Is this still the case.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You mean SQLDeveloper- it's an IDE from Oracle (FREE) - Never been a TOAD guy either but it does do explain plans, intellisense, run scripts and even do a diff between schemas.
hth
Al
|
|
|
|
|
J4amieC wrote: All of that just to output a date WTF I'll stick to SQL Server
 I'll second that 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
