|
I need to be able to pull information from two tables. The first table is the master table that contains the essential information about some object, wherease the second table has some other data related to that object. I need to pull information from the first table regardless of whether there is a match in the second table (class LEFT JOIN), but if there is a record in the second table, I need to pull data only from the latest record in the second table. So, the query would look something like:
SELECT Table1.*, Table2.* FROM Table1 LEFT JOIN Table2 ON table1.field1=table2.field1 WHERE ????
I have an autonumber field in the 2nd table that can be used to retrieve the latest record from table2. Could that be used to retrieve the latest record, and, if so, how?
Thanks.
|
|
|
|
|
Your concept is correct, but a better table representation would help. What is the data in table2? What fields do you need if present and what is the autonumber field?
Tim
|
|
|
|
|
Thanks. Let's see.
From table2 at present I only need to retrieve one field of table, so I don't actually need table2.* in the select statement. Rather, I would just need something like table2.VE. The data in table 2 is simply the results of some process run on the object described in table1. They are linked by some unique object identifier. I need to pull information about the object regardless of whether the process has been run or not. If the process has been run, I need to retrieve the VE field for that object. The basic problem is that there may be multiple records for the proces run on the object, so multiple records in table2 for the object it is linked to in table1. Some of those records may be from the same day, while others may be from 3 months, 6 months, or 10 years ago. I need the latest when pulling up the data.
The autonumber field in table2 is called ID and the name of the field in table2 that I need to retrieve now is called VE (but I may need to retrieve other fields later).
I appreciate the help.
|
|
|
|
|
select t1.*, t2.ve
from table1 t1 left outer join
(select id, VE, max(recorddate) from table2
group by id, VE) t2
on t1.id = t2.id
Does that help?
Tim
|
|
|
|
|
Thanks, Tim. The query, modified to best match my situation, seemed to work as it should for the most part, though the result set still had more records than the number of records in table1. Table1 has 10080 records, whereas the result set from the query had 10087. I'll have to sort through it and find out where those additional records are coming from, but this helped me to get started.
Thanks again.
|
|
|
|
|
|
Tim,
I don't know if you're still paying attention to this thread, but this is what I tried based on your advice:
SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
It worked for the most part, but there were 7 more records in this result table from this query than are present in the 'LEFT' table. I scanned through the result table to find the duplications and it seems that the duplications were instances in the 'RIGHT' table where more than one record was present for the object stored in the left table.
As I mentioned Friday, there can be more than one process result in the RIGHT table for the object stored in the LEFT table, but I only want the latest process result. In most cases, this query eliminates the duplicate process results, but if there were two process results with different VE data, then it seems the subquery in the following query returns both records. If you could help me understand why, I would appreciate it. Thanks.
SELECT Details.*, Results.VE
FROM Details LEFT JOIN [SELECT Results.VE, Results.SerialNumber, Max(Results.ID) From Results Group By Results.VE, Results.SerialNumber]. AS Results ON Details.SN = Results.SerialNumber);
|
|
|
|
|
You are getting multiple values because you are selecting VE, SerialNumber and ID from results. If you only want the lastest VE, remove SerialNumber from your query. That should give you only the latest VE value.
If you have more questions, ask away.
Tim
|
|
|
|
|
Thanks, but I don't need just the latest VE, but the latest VE tied to that serial number. The VE field is simply a text field that can be PASS, FAIL, or an empty string; there is nothing remotely unique about it. I need the VE value, but it only matters insofar as it's "attached" to the serial number.
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
