Hi Fellows,
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
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
--
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Wednesday, March 28, 2012
Jump to next record insertion.
Hi Fellows,
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
--
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
--
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
--
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
--
Friday, March 23, 2012
joining two tables
Hi
let say I have record:s in two tables, namely tblTable1 and tblTable2
tblTable1:
Year SerialA SerialB
1998 1 3
2000 3 2
1999 2 2
2001 5 3
1998 1 1
1999 2 1
2001 3 2
tblTable2:
Year SerialA SerialB
1998 2 0
1999 1 2
1999 0 2
2001 3 3
1998 2 2
1999 0 1
2001 4 2
I want to have it "Group by Year Order by Year" by joining these two tables
and sum each serial for each year.
The output will be
Year SerialA SerialB
1998 6 6
1999 5 8
2000 3 2
2001 15 10
What will be the SELECT statement to achieve the ouput as mentioned above ?
Thank you.
Regards.SELECT Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
FROM (select * from tblTable1
UNION ALL
select * from tblTable2) as T
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:
>Hi
>let say I have record:s in two tables, namely tblTable1 and tblTable2
>tblTable1:
>Year SerialA SerialB
>1998 1 3
>2000 3 2
>1999 2 2
>2001 5 3
>1998 1 1
>1999 2 1
>2001 3 2
>tblTable2:
>Year SerialA SerialB
>1998 2 0
>1999 1 2
>1999 0 2
>2001 3 3
>1998 2 2
>1999 0 1
>2001 4 2
>
>I want to have it "Group by Year Order by Year" by joining these two tables
>and sum each serial for each year.
>The output will be
>Year SerialA SerialB
>1998 6 6
>1999 5 8
>2000 3 2
>2001 15 10
>
>What will be the SELECT statement to achieve the ouput as mentioned above ?
>
>Thank you.
>Regards.
>|||are you sure this is working ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
> SELECT Year,
> SUM(SerialA) as SerialA,
> SUM(SerialB) as SerialB
> FROM (select * from tblTable1
> UNION ALL
> select * from tblTable2) as T
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:
>|||Did you try it and get unexpected results? Perhaps I do not
understand your requirements. I did not build your tables and write
INSERTS for your test data to test it, but yes, I think it works.
It is certainly not the only way to write it. If both tables always
have exactly the same years, one alternative would be:
SELECT T1.Year,
T1.SerialA + T2.SerialA as SerialA,
T1.SerialB + T2.SerialB as SerialB
FROM (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable1
group by Year) as T1
JOIN (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable2
group by Year) as T2
ON T1.Year = T2.Year
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 23:33:55 +0800, "magix" <magix@.asia.com> wrote:
>are you sure this is working ?
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>|||How could we possible know?
You did not provide table DDL, and you did not provide data in the form of
INSERT statements. so the best you should expect is suggestions in the
'right' direction.
If you want better help, provide better information.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"magix" <magix@.asia.com> wrote in message news:449ab86a_1@.news.tm.net.my...
> are you sure this is working ?
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>sql
let say I have record:s in two tables, namely tblTable1 and tblTable2
tblTable1:
Year SerialA SerialB
1998 1 3
2000 3 2
1999 2 2
2001 5 3
1998 1 1
1999 2 1
2001 3 2
tblTable2:
Year SerialA SerialB
1998 2 0
1999 1 2
1999 0 2
2001 3 3
1998 2 2
1999 0 1
2001 4 2
I want to have it "Group by Year Order by Year" by joining these two tables
and sum each serial for each year.
The output will be
Year SerialA SerialB
1998 6 6
1999 5 8
2000 3 2
2001 15 10
What will be the SELECT statement to achieve the ouput as mentioned above ?
Thank you.
Regards.SELECT Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
FROM (select * from tblTable1
UNION ALL
select * from tblTable2) as T
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:
>Hi
>let say I have record:s in two tables, namely tblTable1 and tblTable2
>tblTable1:
>Year SerialA SerialB
>1998 1 3
>2000 3 2
>1999 2 2
>2001 5 3
>1998 1 1
>1999 2 1
>2001 3 2
>tblTable2:
>Year SerialA SerialB
>1998 2 0
>1999 1 2
>1999 0 2
>2001 3 3
>1998 2 2
>1999 0 1
>2001 4 2
>
>I want to have it "Group by Year Order by Year" by joining these two tables
>and sum each serial for each year.
>The output will be
>Year SerialA SerialB
>1998 6 6
>1999 5 8
>2000 3 2
>2001 15 10
>
>What will be the SELECT statement to achieve the ouput as mentioned above ?
>
>Thank you.
>Regards.
>|||are you sure this is working ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
> SELECT Year,
> SUM(SerialA) as SerialA,
> SUM(SerialB) as SerialB
> FROM (select * from tblTable1
> UNION ALL
> select * from tblTable2) as T
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:
>|||Did you try it and get unexpected results? Perhaps I do not
understand your requirements. I did not build your tables and write
INSERTS for your test data to test it, but yes, I think it works.
It is certainly not the only way to write it. If both tables always
have exactly the same years, one alternative would be:
SELECT T1.Year,
T1.SerialA + T2.SerialA as SerialA,
T1.SerialB + T2.SerialB as SerialB
FROM (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable1
group by Year) as T1
JOIN (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable2
group by Year) as T2
ON T1.Year = T2.Year
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 23:33:55 +0800, "magix" <magix@.asia.com> wrote:
>are you sure this is working ?
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>|||How could we possible know?
You did not provide table DDL, and you did not provide data in the form of
INSERT statements. so the best you should expect is suggestions in the
'right' direction.
If you want better help, provide better information.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"magix" <magix@.asia.com> wrote in message news:449ab86a_1@.news.tm.net.my...
> are you sure this is working ?
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>sql
Wednesday, March 21, 2012
Joining to large tables to perfrom update
I have 2 large tables that are over 11 million records each. I need to join
them on 1 field and then update 4 fields. So my script is this
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b
on a.field5= b.field5
This query is taking a long time to run and I am wondering if there are any
join hints or lock hints that I can put in there to make it more efficient.
Any help is appreciated.an index on b(field5, field1, field2, field3, field4) might help with
this particular update.
Considering the performance of the whole system, it might or might not
be worth keeping, depending on your priorities.|||You can use
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b with (nolock)
on a.field5= b.field5
however, for 11 million rows, it will still take a lot of time.
I would create script that executes the update in batches (Example: 1
million per batch based on field5). In other words, I would create a
"control" table where I can store the field5, the bacth number and when was
updated. This way even if any of the batch updates do not complete (for any
reason), you can start where you left off rather than start all over again.
"Andy" wrote:
> I have 2 large tables that are over 11 million records each. I need to jo
in
> them on 1 field and then update 4 fields. So my script is this
> update a
> set a.field1= b.field1,
> a.field2= b.field2,
> a.field3 = b.field3,
> a.field4 = bfield4
> from a inner join b
> on a.field5= b.field5
> This query is taking a long time to run and I am wondering if there are an
y
> join hints or lock hints that I can put in there to make it more efficient
.
> Any help is appreciated.
them on 1 field and then update 4 fields. So my script is this
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b
on a.field5= b.field5
This query is taking a long time to run and I am wondering if there are any
join hints or lock hints that I can put in there to make it more efficient.
Any help is appreciated.an index on b(field5, field1, field2, field3, field4) might help with
this particular update.
Considering the performance of the whole system, it might or might not
be worth keeping, depending on your priorities.|||You can use
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b with (nolock)
on a.field5= b.field5
however, for 11 million rows, it will still take a lot of time.
I would create script that executes the update in batches (Example: 1
million per batch based on field5). In other words, I would create a
"control" table where I can store the field5, the bacth number and when was
updated. This way even if any of the batch updates do not complete (for any
reason), you can start where you left off rather than start all over again.
"Andy" wrote:
> I have 2 large tables that are over 11 million records each. I need to jo
in
> them on 1 field and then update 4 fields. So my script is this
> update a
> set a.field1= b.field1,
> a.field2= b.field2,
> a.field3 = b.field3,
> a.field4 = bfield4
> from a inner join b
> on a.field5= b.field5
> This query is taking a long time to run and I am wondering if there are an
y
> join hints or lock hints that I can put in there to make it more efficient
.
> Any help is appreciated.
Monday, March 19, 2012
joining table on on last entered record
Dear All,
What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.
I'd like to select every record in table A but only joining the last relevant record from table B. So:
Table A:
A1 Prj1
A2 Prj2
Table B:
B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007
So I'd like to list using the most efficient way this:
A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007
I'm assuming this is NOT the most efficient way:
select A, (select top 1 date from B orderBy ...)
Any suggestions?Maybe this:
select A, B
from A
inner join A on A.Aid = B.Aid
where B.date = (select max(B.date) from B where...) ...
this works faster but is there a better way? (I'm sure there is)
Anyone?|||I use this:
select A,
B
from A
inner join --LastRecords
select A,
max(date) as date
from A
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date
...but I can't promise that it is faster.|||Another possibility is:
SELECT A.aid
,A.prj
,MAX(B.DATE)
FROM A
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj
Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.|||Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?|||use a LEFT OUTER JOIN instead of INNER JOIN|||Thanks for that
What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.
I'd like to select every record in table A but only joining the last relevant record from table B. So:
Table A:
A1 Prj1
A2 Prj2
Table B:
B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007
So I'd like to list using the most efficient way this:
A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007
I'm assuming this is NOT the most efficient way:
select A, (select top 1 date from B orderBy ...)
Any suggestions?Maybe this:
select A, B
from A
inner join A on A.Aid = B.Aid
where B.date = (select max(B.date) from B where...) ...
this works faster but is there a better way? (I'm sure there is)
Anyone?|||I use this:
select A,
B
from A
inner join --LastRecords
select A,
max(date) as date
from A
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date
...but I can't promise that it is faster.|||Another possibility is:
SELECT A.aid
,A.prj
,MAX(B.DATE)
FROM A
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj
Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.|||Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?|||use a LEFT OUTER JOIN instead of INNER JOIN|||Thanks for that
JOINing ORDER and Performance
Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay t
he same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics sta
y the same. You might
see a small difference, since optimizer has "early out" strategies, but if y
ou do see a big
difference, you have found a weakness in the optimizer and MS would like to
know about it
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more co
lumns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materia
lized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index
is a non-clustered
index containing all the information that a query need from a table. This wa
y, SQL Server don't have
to access each page for each row, all information is already in the non-clus
tered index. This can
make a huge performance difference.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay
the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics st
ay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if
you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know ab
out it
Ha.
J.|||> Ha.
Get your point... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.
4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Ha.
> J.
>
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay t
he same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics sta
y the same. You might
see a small difference, since optimizer has "early out" strategies, but if y
ou do see a big
difference, you have found a weakness in the optimizer and MS would like to
know about it
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more co
lumns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materia
lized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index
is a non-clustered
index containing all the information that a query need from a table. This wa
y, SQL Server don't have
to access each page for each row, all information is already in the non-clus
tered index. This can
make a huge performance difference.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay
the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics st
ay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if
you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know ab
out it
Ha.
J.|||> Ha.
Get your point... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.
4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Ha.
> J.
>
JOINing ORDER and Performance
Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
see a small difference, since optimizer has "early out" strategies, but if you do see a big
difference, you have found a weakness in the optimizer and MS would like to know about it
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more columns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materialized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index is a non-clustered
index containing all the information that a query need from a table. This way, SQL Server don't have
to access each page for each row, all information is already in the non-clustered index. This can
make a huge performance difference.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.|||> Ha.
Get your point... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>It shouldn't make a difference, as long as the semantics of the query stay the same (consider
>>outer
>>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>>difference, you have found a weakness in the optimizer and MS would like to know about it
> Ha.
> J.
>
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
see a small difference, since optimizer has "early out" strategies, but if you do see a big
difference, you have found a weakness in the optimizer and MS would like to know about it
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more columns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materialized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index is a non-clustered
index containing all the information that a query need from a table. This way, SQL Server don't have
to access each page for each row, all information is already in the non-clustered index. This can
make a huge performance difference.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.|||> Ha.
Get your point... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>It shouldn't make a difference, as long as the semantics of the query stay the same (consider
>>outer
>>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>>difference, you have found a weakness in the optimizer and MS would like to know about it
> Ha.
> J.
>
JOINing ORDER and Performance
Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
Dutt
OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...
|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
Dutt
OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...
|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.
Joining from two different tables
Hi all,
I have a test sales table with 12 records showing sale_id, sale_date, prod_i
d, cust_id, unit_price, qty. Then I have two other tables, customer and pro
ducts from where I would like to extract product description and customer na
me. When joining the sales the table with either customer or product, I obta
in the 12 original records with the desired description (product or customer
). However, when attempting to obtain both descriptions, 2 records are left
out. Here is the first query:
SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price, sale
s.qty
FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
The problem is when the following is attempted:
SELECT sales.date, customers.cust_desc, products.prod_desc, sales.unit_price
, sales.qty
FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id = p.
prod_id) ON c.cust_id = s.cust_id
What am I missing? Thanks,give this a try...
SELECT sales.date, customers.cust_desc, products.prod_desc,
sales.unit_price, sales.qty
FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
= p.prod_id) x ON c.cust_id = x.cust_id
also do all sales have all products if not then you have to do a left join
on the sales table
"itmex" <itmex.1mhd5p@.mail.codecomments.com> wrote in message
news:itmex.1mhd5p@.mail.codecomments.com...
> Hi all,
> I have a test sales table with 12 records showing sale_id, sale_date,
> prod_id, cust_id, unit_price, qty. Then I have two other tables,
> customer and products from where I would like to extract product
> description and customer name. When joining the sales the table with
> either customer or product, I obtain the 12 original records with the
> desired description (product or customer). However, when attempting to
> obtain both descriptions, 2 records are left out. Here is the first
> query:
> SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price,
> sales.qty
> FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
>
> The problem is when the following is attempted:
> SELECT sales.date, customers.cust_desc, products.prod_desc,
> sales.unit_price, sales.qty
> FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
> = p.prod_id) ON c.cust_id = s.cust_id
> What am I missing? Thanks,
>
> --
> itmex
> ---
> Posted via http://www.codecomments.com
> ---
>|||This is same as what you had, and should work, unless there are some sales
records with cust_id not in Customers table...
SELECT S.date, S.cust_id, P.prod_desc,
S.unit_price,S.qty
FROM Sales S
Join Products P
On P.prod_id = S.prod_id
Join Customers C
On C.cust_id = S.cust_id
If above does not work, please post the actual data, in all three tables,
and the "wrong" output of the query (from which you believe two records are
missing)
-- oh and specifiy Which two records you think are "missing". (SHould be in
there )
"itmex" wrote:
> Hi all,
> I have a test sales table with 12 records showing sale_id, sale_date,
> prod_id, cust_id, unit_price, qty. Then I have two other tables,
> customer and products from where I would like to extract product
> description and customer name. When joining the sales the table with
> either customer or product, I obtain the 12 original records with the
> desired description (product or customer). However, when attempting to
> obtain both descriptions, 2 records are left out. Here is the first
> query:
> SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price,
> sales.qty
> FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
>
> The problem is when the following is attempted:
> SELECT sales.date, customers.cust_desc, products.prod_desc,
> sales.unit_price, sales.qty
> FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
> = p.prod_id) ON c.cust_id = s.cust_id
> What am I missing? Thanks,
>
> --
> itmex
> ---
> Posted via http://www.codecomments.com
> ---
>|||Why did you use parens and the infixed join syntax? The most this can
do is force an order of execution if you do not have a smart optimizer.
And you have to use the alias table names in the SELECT clause:
SELECT S.date, C.cust_desc, P.prod_desc, S.unit_price, S.qty
FROM Customers AS C,
Sales AS S,
Products AS P
WHERE S.prod_id = P.prod_id
AND C.cust_id = S.cust_id;
This should work unless you have no DRI between Sales and Products, so
people can sell stuff you don't stock. Likewise, sales to people who do
not exist will be a another problem.
DDL would have shown us that, which is why it is the netiquette here
to post it.
Add the DRI actions you need after you do a data audit.
I have a test sales table with 12 records showing sale_id, sale_date, prod_i
d, cust_id, unit_price, qty. Then I have two other tables, customer and pro
ducts from where I would like to extract product description and customer na
me. When joining the sales the table with either customer or product, I obta
in the 12 original records with the desired description (product or customer
). However, when attempting to obtain both descriptions, 2 records are left
out. Here is the first query:
SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price, sale
s.qty
FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
The problem is when the following is attempted:
SELECT sales.date, customers.cust_desc, products.prod_desc, sales.unit_price
, sales.qty
FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id = p.
prod_id) ON c.cust_id = s.cust_id
What am I missing? Thanks,give this a try...
SELECT sales.date, customers.cust_desc, products.prod_desc,
sales.unit_price, sales.qty
FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
= p.prod_id) x ON c.cust_id = x.cust_id
also do all sales have all products if not then you have to do a left join
on the sales table
"itmex" <itmex.1mhd5p@.mail.codecomments.com> wrote in message
news:itmex.1mhd5p@.mail.codecomments.com...
> Hi all,
> I have a test sales table with 12 records showing sale_id, sale_date,
> prod_id, cust_id, unit_price, qty. Then I have two other tables,
> customer and products from where I would like to extract product
> description and customer name. When joining the sales the table with
> either customer or product, I obtain the 12 original records with the
> desired description (product or customer). However, when attempting to
> obtain both descriptions, 2 records are left out. Here is the first
> query:
> SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price,
> sales.qty
> FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
>
> The problem is when the following is attempted:
> SELECT sales.date, customers.cust_desc, products.prod_desc,
> sales.unit_price, sales.qty
> FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
> = p.prod_id) ON c.cust_id = s.cust_id
> What am I missing? Thanks,
>
> --
> itmex
> ---
> Posted via http://www.codecomments.com
> ---
>|||This is same as what you had, and should work, unless there are some sales
records with cust_id not in Customers table...
SELECT S.date, S.cust_id, P.prod_desc,
S.unit_price,S.qty
FROM Sales S
Join Products P
On P.prod_id = S.prod_id
Join Customers C
On C.cust_id = S.cust_id
If above does not work, please post the actual data, in all three tables,
and the "wrong" output of the query (from which you believe two records are
missing)
-- oh and specifiy Which two records you think are "missing". (SHould be in
there )
"itmex" wrote:
> Hi all,
> I have a test sales table with 12 records showing sale_id, sale_date,
> prod_id, cust_id, unit_price, qty. Then I have two other tables,
> customer and products from where I would like to extract product
> description and customer name. When joining the sales the table with
> either customer or product, I obtain the 12 original records with the
> desired description (product or customer). However, when attempting to
> obtain both descriptions, 2 records are left out. Here is the first
> query:
> SELECT sales.date, sales.cust_id, products.prod_desc, sales.unit_price,
> sales.qty
> FROM sales s INNER JOIN products p ON s.prod_id = p.prod_id
>
> The problem is when the following is attempted:
> SELECT sales.date, customers.cust_desc, products.prod_desc,
> sales.unit_price, sales.qty
> FROM customers c INNER JOIN (sales s INNER JOIN products p ON s.prod_id
> = p.prod_id) ON c.cust_id = s.cust_id
> What am I missing? Thanks,
>
> --
> itmex
> ---
> Posted via http://www.codecomments.com
> ---
>|||Why did you use parens and the infixed join syntax? The most this can
do is force an order of execution if you do not have a smart optimizer.
And you have to use the alias table names in the SELECT clause:
SELECT S.date, C.cust_desc, P.prod_desc, S.unit_price, S.qty
FROM Customers AS C,
Sales AS S,
Products AS P
WHERE S.prod_id = P.prod_id
AND C.cust_id = S.cust_id;
This should work unless you have no DRI between Sales and Products, so
people can sell stuff you don't stock. Likewise, sales to people who do
not exist will be a another problem.
DDL would have shown us that, which is why it is the netiquette here
to post it.
Add the DRI actions you need after you do a data audit.
Monday, March 12, 2012
Joining and Conditional Column Data Help
I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.
employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)
If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!
SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc
SELECT
e.empid,e
.empname,COUNT
(t.taskid)as'tcount'FROM
tasks tLEFT
JOIN employee eON e.empid= t.empidWHERE
t.resolutionISNULLGROUP
BY e.empid, e.empnameOrder
by tcountDesc|||limno's query doesn't quite work the way you'd expect. Because it is being filtered by where the resolution is null from within the WHERE clause, it will eliminate employees that have no records where resolution is null from the output. If you want the employees listed even if they have no tasks, then use this instead:
SELECT empid, empname, (SELECTCOUNT(*)FROM tasksWHERE tasks.empid=employee.empidAND resolutionISNULL)as'tcount'FROM employeeOrder by tcountDesc|||
Thanks. That helped me put together someting else
selecte.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution isnull and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc
Wednesday, March 7, 2012
Join takes too long to execute
This join takes 6 seconds to execute, and that's too long. There are several
hundred thousand records in all tables with and index on the ID field of eac
h
table. The plan is to have this in a SP with clients calling it repeatedly s
o
it needs to be fast.
I'm wondering if i can permanently related these tables outside of the SP?
Then the join wouldn't be necessary and i could just do a SELECT
..where...etc?
select distinct m.* from main m
left join sym2 s on s.id = m.id
left join cat2 c on c.id = m.id
left join servid2 i on i.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
Thanks,
Don
SQL 2000Hi
You may want to look at the query plan for this, it may help if you use
dynamic SQL or a union.
e.g. (untested!)
select m.*
from main m
join sym2 s on s.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
UNION
select m.*
from main m
join cat2 c on c.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
select m.*
from main m
join servid2 i on i.id = m.id
where i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
For production code you should not use SELECT *
John
"DonSQL2222" wrote:
> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
> Thanks,
> Don
> SQL 2000|||Don,
Does this query really execute? I always thought you could only call
UDF's (in this case iter_charlist_to_table) if you mention the UDF
owner.
How many rows does the table valued UDF return in these three cases? If
it returns more than say 1000 rows, then you should try to remove it.
You only select rows from table main. If main is a regular table with a
primary key, then you can move the joins to EXISTS clauses, remove the
DISTINCT keyword, and change the outer joins to inner joins.
For example:
SELECT *
FROM main m
WHERE EXISTS (
SELECT 1
FROM sym2 s
WHERE s.id=m.id
AND s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM cat2 c
WHERE c.id=m.id
AND c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM servid2 i
WHERE i.id = m.id
AND i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
)
If the table valued UDF is in fact the main problem, then you could
consider rewriting the query to this:
SELECT DISTINCT *
FROM main m
INNER JOIN (
SELECT id FROM sym2 s
WHERE s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
UNION ALL
SELECT id FROM cat2 c
WHERE c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
UNION ALL
SELECT id FROM servid2 i
WHERE i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
) AS T1 ON T1.id = m.id
Hope this helps,
Gert-Jan
DonSQL2222 wrote:
> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFA
ULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT
) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFA
ULT))
> Thanks,
> Don
> SQL 2000
hundred thousand records in all tables with and index on the ID field of eac
h
table. The plan is to have this in a SP with clients calling it repeatedly s
o
it needs to be fast.
I'm wondering if i can permanently related these tables outside of the SP?
Then the join wouldn't be necessary and i could just do a SELECT
..where...etc?
select distinct m.* from main m
left join sym2 s on s.id = m.id
left join cat2 c on c.id = m.id
left join servid2 i on i.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
Thanks,
Don
SQL 2000Hi
You may want to look at the query plan for this, it may help if you use
dynamic SQL or a union.
e.g. (untested!)
select m.*
from main m
join sym2 s on s.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
UNION
select m.*
from main m
join cat2 c on c.id = m.id
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
select m.*
from main m
join servid2 i on i.id = m.id
where i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
For production code you should not use SELECT *
John
"DonSQL2222" wrote:
> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFAULT))
> Thanks,
> Don
> SQL 2000|||Don,
Does this query really execute? I always thought you could only call
UDF's (in this case iter_charlist_to_table) if you mention the UDF
owner.
How many rows does the table valued UDF return in these three cases? If
it returns more than say 1000 rows, then you should try to remove it.
You only select rows from table main. If main is a regular table with a
primary key, then you can move the joins to EXISTS clauses, remove the
DISTINCT keyword, and change the outer joins to inner joins.
For example:
SELECT *
FROM main m
WHERE EXISTS (
SELECT 1
FROM sym2 s
WHERE s.id=m.id
AND s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM cat2 c
WHERE c.id=m.id
AND c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
) OR EXISTS (
SELECT 1
FROM servid2 i
WHERE i.id = m.id
AND i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
)
If the table valued UDF is in fact the main problem, then you could
consider rewriting the query to this:
SELECT DISTINCT *
FROM main m
INNER JOIN (
SELECT id FROM sym2 s
WHERE s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT)
)
UNION ALL
SELECT id FROM cat2 c
WHERE c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT)
)
UNION ALL
SELECT id FROM servid2 i
WHERE i.servid in (select str from iter_charlist_to_table(@.msid,
DEFAULT))
) AS T1 ON T1.id = m.id
Hope this helps,
Gert-Jan
DonSQL2222 wrote:
> This join takes 6 seconds to execute, and that's too long. There are sever
al
> hundred thousand records in all tables with and index on the ID field of e
ach
> table. The plan is to have this in a SP with clients calling it repeatedly
so
> it needs to be fast.
> I'm wondering if i can permanently related these tables outside of the SP?
> Then the join wouldn't be necessary and i could just do a SELECT
> ..where...etc?
> select distinct m.* from main m
> left join sym2 s on s.id = m.id
> left join cat2 c on c.id = m.id
> left join servid2 i on i.id = m.id
> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFA
ULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT
) )
> or i.servid in (select str from iter_charlist_to_table(@.msid, DEFA
ULT))
> Thanks,
> Don
> SQL 2000
Join tables and exclude records...
Hi
I am trying to write a SQL query to run against 2 Oracle tables - tblNames and tblAbsence.
Let's say the tables look like this:
tblNames:
Andrew
David
John
Michael
and tblAbsence:
Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment
What I need to do is to create a report that lists all of the absences from tblAbsence, plus a row for anyone in the tblNames table with no date and "In Office" in the third column if they don't have an entry in tblAbsence.
To use the above values, what I should have in my resultset is:
Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment
John Null In Office
Michael Null In Office
I'm not sure how to do this. I did try a UNION of two queries, but the nearest I have been able to get is to reproduce the entries in the tblAbsence table plus all of the records in tblNames. I need to exclude the names from tblNames if they have an entry in tblAbsence.
Any ideas?
Thanks
MichaelDifferent versions of Oracle have different levels of support for standard SQL, but the way that I'd do it would be:SELECT n.Name
, a.date, Coalesce(a.comment, 'In Office)
FROM tblNames AS n
LEFT OUTER JOIN tblAbsence AS a
ON (a.name = n.name)-PatP|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);Good point, but if the poster is just starting out I think it would be better to start them with standard SQL and only use eingine specific features if they are required. The closer we can keep the new users to standards, the less likely they are to get hurt by the odd quirks that we've come to know and love!
-PatP|||I agree, Pat ... however, Robojan said it is about Oracle tables so I guess this Oracle specific code won't hurt much :)|||Thanks guys - this helped me to write the query. I simplified it greatly for this (removing joins, etc.), and using the joins correctly fixed it for me.
Thanks
I am trying to write a SQL query to run against 2 Oracle tables - tblNames and tblAbsence.
Let's say the tables look like this:
tblNames:
Andrew
David
John
Michael
and tblAbsence:
Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment
What I need to do is to create a report that lists all of the absences from tblAbsence, plus a row for anyone in the tblNames table with no date and "In Office" in the third column if they don't have an entry in tblAbsence.
To use the above values, what I should have in my resultset is:
Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment
John Null In Office
Michael Null In Office
I'm not sure how to do this. I did try a UNION of two queries, but the nearest I have been able to get is to reproduce the entries in the tblAbsence table plus all of the records in tblNames. I need to exclude the names from tblNames if they have an entry in tblAbsence.
Any ideas?
Thanks
MichaelDifferent versions of Oracle have different levels of support for standard SQL, but the way that I'd do it would be:SELECT n.Name
, a.date, Coalesce(a.comment, 'In Office)
FROM tblNames AS n
LEFT OUTER JOIN tblAbsence AS a
ON (a.name = n.name)-PatP|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);Good point, but if the poster is just starting out I think it would be better to start them with standard SQL and only use eingine specific features if they are required. The closer we can keep the new users to standards, the less likely they are to get hurt by the odd quirks that we've come to know and love!
-PatP|||I agree, Pat ... however, Robojan said it is about Oracle tables so I guess this Oracle specific code won't hurt much :)|||Thanks guys - this helped me to write the query. I simplified it greatly for this (removing joins, etc.), and using the joins correctly fixed it for me.
Thanks
Friday, February 24, 2012
Join records of each group
Hello, thanks in advance for your help / comments
I have 2 tables:
SampleInfo contains 2 columns: SampleID & SampleName
Analysis contains 2 columns: SampleID & Elements
I link these 2 tables, get the SampleName & Elements out by the code:
SELECT SampleInfo.SampleName, Analysis.Elements
FROM SampleInfo INNER JOIN Analysis ON (SampleInfo.SampleID = Analysis.SampleID)
It would display
SampleName | Elements
A | a
A | b
A | c
A | f
B | a
B | g
B | l
C | c
C | s
C | o
C | m
C | n
I need to display the report as following:
SampleName | Elements
A | a, b, c, f
B | a, g, l
C | c, s, o, m, n
QUESTION: is it possible? If it is, how should I do this?
FYI, I use CR10 & SQLServer 2000 database
Regards,
tHi,
I found some solution to yor post.
I created a report with excel as datasource.
Grouped the report on Sample Name field.
Created two formula fields to get the results.
1. Elements -- Formula Field
Code for formula filed as follows :
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
2.ResetElementValue -- Formula Field
whileprintingrecords;
shared stringvar Elements;
Elements:="";
-- Place the @.Elements fromula filed in detail section and suppress the section.
-- Place the Group Name in Group Footer section
-- Place @.Elements in the same group Footer section
-- Place the ResetElementValue formula filed in the Group Header section and suppres
Try with the following format and let me know will it fulfills your requirement.
Thanks,
Vidu.
-- Group|||Vidu,
Thanks for your help. It was a great start. I only had to change the Elements (formula field) a little as below. If I don't have the ELSE IF, it will double the last element in each group.
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else IF Elements <> Right(Elements, length({Sheet1_.Elements}) then
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
Again, your help is truly appreciated.|||or place the formula in group footer and suppress the details and group header
I have 2 tables:
SampleInfo contains 2 columns: SampleID & SampleName
Analysis contains 2 columns: SampleID & Elements
I link these 2 tables, get the SampleName & Elements out by the code:
SELECT SampleInfo.SampleName, Analysis.Elements
FROM SampleInfo INNER JOIN Analysis ON (SampleInfo.SampleID = Analysis.SampleID)
It would display
SampleName | Elements
A | a
A | b
A | c
A | f
B | a
B | g
B | l
C | c
C | s
C | o
C | m
C | n
I need to display the report as following:
SampleName | Elements
A | a, b, c, f
B | a, g, l
C | c, s, o, m, n
QUESTION: is it possible? If it is, how should I do this?
FYI, I use CR10 & SQLServer 2000 database
Regards,
tHi,
I found some solution to yor post.
I created a report with excel as datasource.
Grouped the report on Sample Name field.
Created two formula fields to get the results.
1. Elements -- Formula Field
Code for formula filed as follows :
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
2.ResetElementValue -- Formula Field
whileprintingrecords;
shared stringvar Elements;
Elements:="";
-- Place the @.Elements fromula filed in detail section and suppress the section.
-- Place the Group Name in Group Footer section
-- Place @.Elements in the same group Footer section
-- Place the ResetElementValue formula filed in the Group Header section and suppres
Try with the following format and let me know will it fulfills your requirement.
Thanks,
Vidu.
-- Group|||Vidu,
Thanks for your help. It was a great start. I only had to change the Elements (formula field) a little as below. If I don't have the ELSE IF, it will double the last element in each group.
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else IF Elements <> Right(Elements, length({Sheet1_.Elements}) then
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
Again, your help is truly appreciated.|||or place the formula in group footer and suppress the details and group header
Join Question
I am trying to join two tables and one table could have multiple records.
Is there a way to limit to one record on the JOIN statement?
ThanksSELECT TOP 1 t1.col1, t1.col2, t1.colN
FROM
table1 AS t1 JOIN table2 AS t2 ON t1.col1 = t2.col1
"jack" wrote:
> I am trying to join two tables and one table could have multiple records.
> Is there a way to limit to one record on the JOIN statement?
> Thanks
>
>|||On Fri, 14 Oct 2005 13:11:47 -0600, jack wrote:
>I am trying to join two tables and one table could have multiple records.
>Is there a way to limit to one record on the JOIN statement?
>Thanks
>
Hi Jack,
Unfortunately, SQL Server has no "GimmeOneDontCareWhich" function. If
you want one from the group, you'll have to specify which one.
Here's a possible way to do what you want. I'll assume that you join on
col1 and want to join on only the row with the lowest col2.
SELECT ....
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t1.col1 = t2.col1
WHERE t2.col2 = (SELECT MIN(col2)
FROM table2 AS t2b
WHERE t2b.col1 = t2.col1)
BTW, it's easier to reply if you provide some tables and sample data to
work with. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Is there a way to limit to one record on the JOIN statement?
ThanksSELECT TOP 1 t1.col1, t1.col2, t1.colN
FROM
table1 AS t1 JOIN table2 AS t2 ON t1.col1 = t2.col1
"jack" wrote:
> I am trying to join two tables and one table could have multiple records.
> Is there a way to limit to one record on the JOIN statement?
> Thanks
>
>|||On Fri, 14 Oct 2005 13:11:47 -0600, jack wrote:
>I am trying to join two tables and one table could have multiple records.
>Is there a way to limit to one record on the JOIN statement?
>Thanks
>
Hi Jack,
Unfortunately, SQL Server has no "GimmeOneDontCareWhich" function. If
you want one from the group, you'll have to specify which one.
Here's a possible way to do what you want. I'll assume that you join on
col1 and want to join on only the row with the lowest col2.
SELECT ....
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t1.col1 = t2.col1
WHERE t2.col2 = (SELECT MIN(col2)
FROM table2 AS t2b
WHERE t2b.col1 = t2.col1)
BTW, it's easier to reply if you provide some tables and sample data to
work with. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Join Problems
Hi,
The following query has failed to return all the records.
SELECT b.Account_desc, b.Account,
IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
where a.source_type = 'LY01'
There are total 143 records in Actual_account_Tbl. But the above query
returned only 135 records i.e., only those records satisfy the condition
"a.Account_desc = b.Account_desc" are returned.
As per right outerjoin in the above statement I suppose to get all the
records from table 'b', and blank data from table 'a' if it doesn't
satisfy the condition.
Why it is not consistant?
Pls help me.
Thanks and Regards.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
On Thu, 27 May 2004 23:47:15 -0700, k k wrote:
>Hi,
>
>The following query has failed to return all the records.
>SELECT b.Account_desc, b.Account,
>IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
>a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
>IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
>outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
>where a.source_type = 'LY01'
>There are total 143 records in Actual_account_Tbl. But the above query
>returned only 135 records i.e., only those records satisfy the condition
>"a.Account_desc = b.Account_desc" are returned.
>As per right outerjoin in the above statement I suppose to get all the
>records from table 'b', and blank data from table 'a' if it doesn't
>satisfy the condition.
>Why it is not consistant?
>Pls help me.
>Thanks and Regards.
Hi k k,
See my reply in comp.databases.ms-sqlserver.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
The following query has failed to return all the records.
SELECT b.Account_desc, b.Account,
IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
where a.source_type = 'LY01'
There are total 143 records in Actual_account_Tbl. But the above query
returned only 135 records i.e., only those records satisfy the condition
"a.Account_desc = b.Account_desc" are returned.
As per right outerjoin in the above statement I suppose to get all the
records from table 'b', and blank data from table 'a' if it doesn't
satisfy the condition.
Why it is not consistant?
Pls help me.
Thanks and Regards.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
On Thu, 27 May 2004 23:47:15 -0700, k k wrote:
>Hi,
>
>The following query has failed to return all the records.
>SELECT b.Account_desc, b.Account,
>IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
>a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
>IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
>outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
>where a.source_type = 'LY01'
>There are total 143 records in Actual_account_Tbl. But the above query
>returned only 135 records i.e., only those records satisfy the condition
>"a.Account_desc = b.Account_desc" are returned.
>As per right outerjoin in the above statement I suppose to get all the
>records from table 'b', and blank data from table 'a' if it doesn't
>satisfy the condition.
>Why it is not consistant?
>Pls help me.
>Thanks and Regards.
Hi k k,
See my reply in comp.databases.ms-sqlserver.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Join Problem..........
Hi,
I need help. I have one Parent table P1 and a Child Table C1. I have 3 records in table P1 and 9 records in C1 (3 records for each records of P1).
When I am doing the inner join of these tables i am getting 9 records, where as actually I want only 3 records. I need all 3 rows from P1 and one row each from the C1 against the corresponding rows of P1. Single row from C1 will come from the criteria based on the Date column of the C1 table. Like the row that will be selected from the table C1 for the row from tbale P1 will have the MAX(DATE) value among all the rows in it C1).
By inner join i am able to extract all the 3 rows where as i need only the row that contains the MAX(DATE).
Kindly help me in this regard.
Thanks,
Rahul Jhaselect P1.foo
, P1.bar
, M.qux
, M.date
from P1
inner
join C1 as M
on M.flim = P1.flam
and M.date =
( select max(date)
from C1
where flim = P1.flam )|||Thanks. :-)
This Will Work. Donno y this din click in my mind.
Thnaks Once Again
Rahul Jha
I need help. I have one Parent table P1 and a Child Table C1. I have 3 records in table P1 and 9 records in C1 (3 records for each records of P1).
When I am doing the inner join of these tables i am getting 9 records, where as actually I want only 3 records. I need all 3 rows from P1 and one row each from the C1 against the corresponding rows of P1. Single row from C1 will come from the criteria based on the Date column of the C1 table. Like the row that will be selected from the table C1 for the row from tbale P1 will have the MAX(DATE) value among all the rows in it C1).
By inner join i am able to extract all the 3 rows where as i need only the row that contains the MAX(DATE).
Kindly help me in this regard.
Thanks,
Rahul Jhaselect P1.foo
, P1.bar
, M.qux
, M.date
from P1
inner
join C1 as M
on M.flim = P1.flam
and M.date =
( select max(date)
from C1
where flim = P1.flam )|||Thanks. :-)
This Will Work. Donno y this din click in my mind.
Thnaks Once Again
Rahul Jha
Monday, February 20, 2012
Join problem
Hi - I have a problem with an update (with a join) that I'm attempting to run on a table with 53 million records in.
Basically I set this query off for the first time and it chugged away for 96 hours before I killed it - something was awry.
I've restored the database to another server (just in case we have an issue with disks, compress, memory etc) and run some tests on samples of the 53 million and for low samples, the update runs in a reasonable time which increases in time directly in line with the increase in sample size:
Top # sample from database tests:
1,000,000 - 36 seconds
2,000,000 - 71 seconds
3,000,000 - 122 seconds
but once I try 4,000,000 it runs and runs - got up to 36 minutes before I cancelled it.
Can anyone see any reason for this? I don't think the query size is going up exponentially - because if you graph the sample size & time from 1-3 million, the line is linear.
I'm currently adding a record ID to the table so I can select the bottom 4,000,000 so I can be sure that there's not some weird data somewhere between 3-4mill that is making the query go whoopsie.
Here are the tables & query I am attemping to run:
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM ListCategoryHierarchy2
WHERE MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Each table is clustered on SourceCode ID/Sourcecode
(varchars are COLLATE Latin1_General_CI_AS)
CREATE TABLE [ListCategoryHierarchy2] (
[Campaign ID] [varchar] (255) NULL,
[Media ID] [varchar] (255) NULL,
[Media Description] [varchar] (255) NULL ,
[Media Selections] [varchar] (255) NULL ,
[SourceCode ID] [varchar] (7) NULL ,
[List ID] [varchar] (255) NULL ,
[Mailing Date] [smalldatetime] NULL ,
[List Category] [varchar] (255) NULL ,
[Source Code Offer] [varchar] (255) NULL ,
[ERI Value] [float] NULL
) ON [PRIMARY]
(9,923 records)
CREATE TABLE [MailingHistory_sample] (
[MatchKey] [binary] (20) NULL ,
[SourceCode] [varchar] (6) NULL ,
[ListID] [varchar] (7) NULL ,
[StationeryCode] [varchar] (5) NULL ,
[PDYear] [varchar] (2) NULL ,
[NaadID] [varchar] (10) NULL ,
[OrderNumber] [char] (9) NOT NULL ,
[CustomerNumber] [binary] (8) NULL ,
[CampaignCode] [varchar] (4) NULL ,
[ProductCode] [varchar] (4) NULL ,
[ResponseType] [varchar] (1) NULL ,
[HouseholdNumber] [bigint] NULL ,
[IndividualNumber] [bigint] NULL ,
[DataType] [varchar] (1) NULL ,
[AddressNumber] [bigint] NULL ,
[DateStamp] [char] (8) NULL ,
[CountColumn] [int] NULL ,
[MailingInstance] [int] NULL ,
[PCPrizm] [char] (5) NULL ,
[Postcode] [char] (7) NULL ,
[PostalArea] [varchar] (2) NULL ,
[TVRegion] [varchar] (3) NULL ,
[ERIRange] [int] NULL ,
[ERIValue] [float] NULL ,
[MediaID] [varchar] (10) NULL
) ON [PRIMARY]
SQL 2000
Any thoughts? Could there be some critical mass of temp table size etc that I am hitting?
thx
wAh, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?|||Well, I'm not too fond of your syntax. You should link your tables in a join rather than the WHERE clause, though I don't know that this would be impacting your execution time. It might.
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM MailingHistory_Sample
inner join ListCategoryHierarchy2 on MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Also, drop any indexes on MailingHistory_Sample except one on [SourceCode]. This column should be indexed in both tables.|||Ah, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?
Well that's one problem...
Where are the Indexes for these tables?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?
If your database is running in Full Recovery mode, a complete before and after image of the update must be stored in the transaction log. If the log file is situated on the same physical drive as the primary data store, has too small an auto-grow value, or is badly physically fragmented, the overall update time can become very, very long.
Options include: issuing an ALTER DATABASE command and backup to take the database into Simple Recovery before the update; relocate the transaction log to a dedicated physical drive; set the transaction log to very, very big and do not truncate its space to filing system when backing up.|||Ah thanks - that makes sense. I only learnt this morning about putting data files and transaction logs on different physical disks. That is the case for this database so we are going to slap in a new drive and split the two.
thanks
w
Basically I set this query off for the first time and it chugged away for 96 hours before I killed it - something was awry.
I've restored the database to another server (just in case we have an issue with disks, compress, memory etc) and run some tests on samples of the 53 million and for low samples, the update runs in a reasonable time which increases in time directly in line with the increase in sample size:
Top # sample from database tests:
1,000,000 - 36 seconds
2,000,000 - 71 seconds
3,000,000 - 122 seconds
but once I try 4,000,000 it runs and runs - got up to 36 minutes before I cancelled it.
Can anyone see any reason for this? I don't think the query size is going up exponentially - because if you graph the sample size & time from 1-3 million, the line is linear.
I'm currently adding a record ID to the table so I can select the bottom 4,000,000 so I can be sure that there's not some weird data somewhere between 3-4mill that is making the query go whoopsie.
Here are the tables & query I am attemping to run:
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM ListCategoryHierarchy2
WHERE MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Each table is clustered on SourceCode ID/Sourcecode
(varchars are COLLATE Latin1_General_CI_AS)
CREATE TABLE [ListCategoryHierarchy2] (
[Campaign ID] [varchar] (255) NULL,
[Media ID] [varchar] (255) NULL,
[Media Description] [varchar] (255) NULL ,
[Media Selections] [varchar] (255) NULL ,
[SourceCode ID] [varchar] (7) NULL ,
[List ID] [varchar] (255) NULL ,
[Mailing Date] [smalldatetime] NULL ,
[List Category] [varchar] (255) NULL ,
[Source Code Offer] [varchar] (255) NULL ,
[ERI Value] [float] NULL
) ON [PRIMARY]
(9,923 records)
CREATE TABLE [MailingHistory_sample] (
[MatchKey] [binary] (20) NULL ,
[SourceCode] [varchar] (6) NULL ,
[ListID] [varchar] (7) NULL ,
[StationeryCode] [varchar] (5) NULL ,
[PDYear] [varchar] (2) NULL ,
[NaadID] [varchar] (10) NULL ,
[OrderNumber] [char] (9) NOT NULL ,
[CustomerNumber] [binary] (8) NULL ,
[CampaignCode] [varchar] (4) NULL ,
[ProductCode] [varchar] (4) NULL ,
[ResponseType] [varchar] (1) NULL ,
[HouseholdNumber] [bigint] NULL ,
[IndividualNumber] [bigint] NULL ,
[DataType] [varchar] (1) NULL ,
[AddressNumber] [bigint] NULL ,
[DateStamp] [char] (8) NULL ,
[CountColumn] [int] NULL ,
[MailingInstance] [int] NULL ,
[PCPrizm] [char] (5) NULL ,
[Postcode] [char] (7) NULL ,
[PostalArea] [varchar] (2) NULL ,
[TVRegion] [varchar] (3) NULL ,
[ERIRange] [int] NULL ,
[ERIValue] [float] NULL ,
[MediaID] [varchar] (10) NULL
) ON [PRIMARY]
SQL 2000
Any thoughts? Could there be some critical mass of temp table size etc that I am hitting?
thx
wAh, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?|||Well, I'm not too fond of your syntax. You should link your tables in a join rather than the WHERE clause, though I don't know that this would be impacting your execution time. It might.
UPDATE MailingHistory_Sample
SET MailingHistory_Sample.ERIValue = ListCategoryHierarchy2.[ERI Value]
FROM MailingHistory_Sample
inner join ListCategoryHierarchy2 on MailingHistory_Sample.[SourceCode] = ListCategoryHierarchy2.[SourceCode ID]
Also, drop any indexes on MailingHistory_Sample except one on [SourceCode]. This column should be indexed in both tables.|||Ah, just got the (PRIMARY' filegroup is full) message after trying to insert an ID into the large table - could this be the bigger problem?
Well that's one problem...
Where are the Indexes for these tables?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?|||As I've mentioned, each table is index (clustered) on SourceCode ID/Sourcecode.
We've actually managed to update all the 54 million rows in this table by adding an identity, and running this update in batches of 3million each - this took only a few hours.
Its definitely a volume issue which I hit sometime after 3million where the query just runs for days and doesn't (as far as I can see) complete.
Any ideas what this might be?
If your database is running in Full Recovery mode, a complete before and after image of the update must be stored in the transaction log. If the log file is situated on the same physical drive as the primary data store, has too small an auto-grow value, or is badly physically fragmented, the overall update time can become very, very long.
Options include: issuing an ALTER DATABASE command and backup to take the database into Simple Recovery before the update; relocate the transaction log to a dedicated physical drive; set the transaction log to very, very big and do not truncate its space to filing system when backing up.|||Ah thanks - that makes sense. I only learnt this morning about putting data files and transaction logs on different physical disks. That is the case for this database so we are going to slap in a new drive and split the two.
thanks
w
Join only returns the read rows :|
Hi all,
I am trying to build a association table (t2) to store a list of usershave viewed an item in my records table (t1). My goal is to send theUserID parameter to the query and return to the user a read / not readmarker from the query so I can handle the read ones differently in my.net code. The problem is that I cannot work out how to return anythingbut the read data to the client. So far my stored proc looks like this
DECLARE @.UserID AS Int -- FOR TESTING
SET @.UserID = 219 -- FOR TESTING
SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID
FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @.UserID
GROUP BY t1.MemoID, t1.strTitle,t2.UserID
It works fine but only returns those records from t1 that are read. Ineed to return the records with null values also! I may have built theassoc table wrong and would really appreciate some pointers on what Iam doing wrong. (assoc table has rID, MemoID and UserID columns)
Please help!
Many thanks
Instead of this:
Do this:
|||Thank you ever so much!
I was racking my brains on how to get around the WHERE limitation I had imposed.
Easy when you see how.
Thank you again!
I am trying to build a association table (t2) to store a list of usershave viewed an item in my records table (t1). My goal is to send theUserID parameter to the query and return to the user a read / not readmarker from the query so I can handle the read ones differently in my.net code. The problem is that I cannot work out how to return anythingbut the read data to the client. So far my stored proc looks like this
DECLARE @.UserID AS Int -- FOR TESTING
SET @.UserID = 219 -- FOR TESTING
SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID
FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @.UserID
GROUP BY t1.MemoID, t1.strTitle,t2.UserID
It works fine but only returns those records from t1 that are read. Ineed to return the records with null values also! I may have built theassoc table wrong and would really appreciate some pointers on what Iam doing wrong. (assoc table has rID, MemoID and UserID columns)
Please help!
Many thanks
Instead of this:
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @.UserID
Do this:
LEFT OUTER JOINPlacing the t2.UserID = @.UseriD in the WHERE limits your results toonly those rows where there is a match in t2. You could havealternately coded it as:
t2 ON t1.MemoID = t2.MemoID AND t2.UserID = @.UserID
WHERE t2.UserID = @.UserID OR t2.UserID IS NULL
|||Thank you ever so much!
I was racking my brains on how to get around the WHERE limitation I had imposed.
Easy when you see how.
Thank you again!
Join on agragate function between tables
A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:
Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2
where dato > '2006-08-11'
Group By DatePart(dw,dato)
A typical result:
DOW NOR
3 8934
6 22397
7 23328
1 23401
4 1938
2 24399
5 1112
Trying to join two or more tables in all sorts of variants of this:
Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1],
datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2]
FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato)
Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11'
Group By datePart(dw,a1.dato), datePart(dw,a2.dato)
Here I get this as a typical result:
DOW1 Amount 1 DOW2 Amount 2
6 332953802 6 332953802
3 42248886 3 42248886
1 330281714 1 330281714
7 335759904 7 335759904
4 1232568 4 1232568
5 210168 5 210168
2 366985359 2 366985359
Where the numbers are way off.
Any suggestions?
When you Join the tables, based only on the day of the week, your resultset will have each record in Table1 for a day of the week combined with each record in Table2 for the same day of the week. That is why your numbers are so far off.
Here is a simple example that shows what's happening.
Drop Table Table1Create Table Table1(
pkid int not null Identity(1,1),
dato datetime not null,
valCol varchar(10)
)
Drop Table Table2
Create Table Table2(
pkid int not null Identity(1,1),
dato datetime not null,
valCol varchar(10)
)
insert Table1 values( '1/1/2006', 'Fred' )
insert Table1 values( '1/2/2006', 'Barney' )
insert Table2 values( '1/1/2006', 'Wilma' )
insert Table2 values( '1/1/2006', 'Pebbles' )
insert Table2 values( '1/2/2006', 'Betty' )
insert Table2 values( '1/2/2006', 'BamBam' )
Select DatePart(dw, dato ), Count(*)
From Table1
Group
By DatePart(dw, dato )
Select DatePart(dw, dato ), Count(*)
From Table2
Group
By DatePart(dw, dato )
Select DatePart(dw, t1.dato ),
DatePart(dw, t2.dato),
t1.valCol,
t2.valCol
From Table1 t1
Join Table2 t2
On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )
valCol valCol
-- -- - -
1 1 Fred Wilma
1 1 Fred Pebbles
2 2 Barney Betty
2 2 Barney BamBam
Select DatePart(dw, t1.dato ),
DatePart(dw, t2.dato),
Count(t1.dato),
Count(t2.dato)
From Table1 t1
Join Table2 t2
On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )
Group
By DatePart(dw, t1.dato ),
DatePart(dw, t2.dato)
-- -- -- --
1 1 2 2
2 2 2 2
Select Coalesce( t1.dow, t2.dow ),
t1.howmany,
t2.howmany
From (
Select DatePart(dw, dato ) dow,
Count(*) howmany
From Table1
Group by DatePart(dw, dato )
) t1
Full Outer Join
(
Select DatePart(dw, dato ) dow,
Count(*) howmany
From Table2
Group by DatePart(dw, dato )
) t2
On t1.dow = t2.dow
howmany howmany
-- -- --
1 1 2
2 1 2
Select dayofweek,
(Select Count(*) from Table1 where DatePart(dw, dato ) = dayofweek ),
(Select Count(*) from Table2 where DatePart(dw, dato ) = dayofweek )
From (
Select 1 as dayofweek
Union All
Select 2 as dayofweek
Union All
Select 3 as dayofweek
Union All
Select 4 as dayofweek
Union All
Select 5 as dayofweek
Union All
Select 6 as dayofweek
Union All
Select 7 as dayofweek
)t1
dayofweek
-- -- --
1 1 2
2 1 2
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0
There are several ways to get the days of the week. If you know that one of the tables will have records for every day, you can use that. There is a system table that has lists of numbers, that is another good source.
|||Excellent stuff. I allready guessed the reason for my results but no idea how to stop it.
Join not returning records if one missing.
How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
TomTry:
Select PositionID,JobTitle,Category
from Position p
Left Join JobCategory j on p.categoryCode = j.categoryCode
where p.PositionID = 54
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u4uWz2gRFHA.3296@.TK2MSFTNGP15.phx.gbl...
How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
Tom|||Witout DDL for the tables, it's hard to be sure, but I think what you are
trying to do would require an Outer Join. In an Outer Join, all the records
from one side will be produced, even if there's no match from the other side
on the Join condidtions
Select PositionID,JobTitle,Category
From Position p
Left Outer Join JobCategory j
On j.categoryCode = p.categoryCode
Where PositionID = 54
"tshad" wrote:
> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>|||Dear tshad,
Try following .....
-- If U want all records from Position
Select P.PositionID,J.JobTitle,J.Category from Position P
Left Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
-- If U want all records from JobCategory
Select P.PositionID,J.JobTitle,J.Category from Position P
Right Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
With Regards,
Rakesh Ranjan
Mail me on -- rakesh.ranjan@.3i-infotech.com
"tshad" wrote:
> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
TomTry:
Select PositionID,JobTitle,Category
from Position p
Left Join JobCategory j on p.categoryCode = j.categoryCode
where p.PositionID = 54
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u4uWz2gRFHA.3296@.TK2MSFTNGP15.phx.gbl...
How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
Tom|||Witout DDL for the tables, it's hard to be sure, but I think what you are
trying to do would require an Outer Join. In an Outer Join, all the records
from one side will be produced, even if there's no match from the other side
on the Join condidtions
Select PositionID,JobTitle,Category
From Position p
Left Outer Join JobCategory j
On j.categoryCode = p.categoryCode
Where PositionID = 54
"tshad" wrote:
> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>|||Dear tshad,
Try following .....
-- If U want all records from Position
Select P.PositionID,J.JobTitle,J.Category from Position P
Left Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
-- If U want all records from JobCategory
Select P.PositionID,J.JobTitle,J.Category from Position P
Right Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
With Regards,
Rakesh Ranjan
Mail me on -- rakesh.ranjan@.3i-infotech.com
"tshad" wrote:
> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>
Subscribe to:
Posts (Atom)