Database

10-Oct-08 5:57

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.

Tim Carmichael10-Oct-08 6:08

10-Oct-08 6:08

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

polishprogrammer10-Oct-08 6:28

10-Oct-08 6:28

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.

Tim Carmichael10-Oct-08 6:55

10-Oct-08 6:55

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

polishprogrammer10-Oct-08 8:31

10-Oct-08 8:31

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 Carmichael10-Oct-08 8:33

10-Oct-08 8:33

You're welcome...

polishprogrammer13-Oct-08 6:07

13-Oct-08 6:07

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);

Tim Carmichael13-Oct-08 9:49

13-Oct-08 9:49

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

polishprogrammer13-Oct-08 11:07

13-Oct-08 11:07

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.

Re: Left Outter JOIN

David Mujica10-Oct-08 6:19

David Mujica

10-Oct-08 6:19

I believe what you are looking for is known as an "Outer Join".

Re: Left Outter JOIN

polishprogrammer10-Oct-08 6:47

sql hierarchal data retrieval

10-Oct-08 6:47

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.

sana1710-Oct-08 5:11

sana17

10-Oct-08 5:11

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@

Wendelius10-Oct-08 6:40

Wendelius

10-Oct-08 6:40

What database are you using? SQL Server, Oracle, something else?

The need to optimize rises from a bad design

sana1710-Oct-08 6:53

sana17

10-Oct-08 6:53

i am using SQL server

$@N@

Wendelius10-Oct-08 6:58

Wendelius

10-Oct-08 6:58

Then you should get familiar with Common Table Expression (CTE). Have a look at Recursive Queries Using Common Table Expressions[^]

The need to optimize rises from a bad design

Giorgi Dalakishvili10-Oct-08 8:36

Giorgi Dalakishvili

10-Oct-08 8:36

Here you are: CTE[^]

Giorgi Dalakishvili

#region signature
My Articles / My Latest Article[^] / My blog[^]
#endregion

Performance Issue in Query

sana1711-Oct-08 4:27

sana17

11-Oct-08 4:27

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@

ashwanigl10-Oct-08 4:18

ashwanigl

10-Oct-08 4:18

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.

Ashfield10-Oct-08 4:29

Ashfield

10-Oct-08 4:29

You are calling the UDF for every single record, hence the extended run time.

Bob
Ashfield Consultants Ltd

ashwanigl10-Oct-08 4:32

ashwanigl

10-Oct-08 4:32

Thanks. Got it.

Can you please suggest how to call UDF only one time...is there any way?

Ashfield10-Oct-08 4:45

Ashfield

10-Oct-08 4:45

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