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

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

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

Join tables, count problems

Hi,

I have the following problem: I want to join 2 tables but the table that I want to join on has duplicates of the same record.

This is what my query looks like:
SELECT a.account, e.account AS Expr1, COUNT(e.ord_status) AS SentOrders, MONTH(e.datetime_added) AS Month, YEAR(e.datetime_added) AS Year
FROM executionreports AS e INNER JOIN
accounts AS a ON e.account = a.account
WHERE (e.ord_status = '0')
GROUP BY a.account, e.account, MONTH(e.datetime_added), YEAR(e.datetime_added)
ORDER BY Expr1

and the output looks like this:

1AA1AA328420061CC1CC45320061CD1CD8420061MA1MA1167320061MA1MA828420067TR7TR2420067TS7TS3696320067TS7TS2676420067TW7TW34420067TW7TW18320067UW7UW3320067VE7VE4320067YP7YP405320067YP7YP23142006TESTTEST142006

The problem is that the count is too high. This is because the account table has several entries with 1MA and 7TS for example. How can I correct it so i basically joins on a distinct set of the account table records?

Thanks,

Tom

take a look at this maybe this will help you

create table #test(id int)
insert into #test values(1)
insert into #test values(1)
insert into #test values(1)
insert into #test values(2)
insert into #test values(3)
insert into #test values(4)

select id,count(id)
from #test
group by id

select id,count(distinct id)
from #test
group by id

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Isn't there an easy way to do it in one statement?
|||

what happens when you change COUNT(e.ord_status) to COUNT(DISTINCT e.ord_status)?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||no change because because I don't care about the e.ord_status here but about the a.account?
|||

Ok,

what about this then

SELECT a.account, e.account AS Expr1, COUNT(e.ord_status) AS SentOrders, MONTH(e.datetime_added) AS Month, YEAR(e.datetime_added) AS Year
FROM executionreports AS e INNER JOIN
(select account from accounts group by account) AS a ON e.account = a.account
WHERE (e.ord_status = '0')
GROUP BY a.account, e.account, MONTH(e.datetime_added), YEAR(e.datetime_added)
ORDER BY Expr1

Can you post some sample data and DDL if this doesn't work?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||aaahh why didn't I try that ;) That did it, thx a lot

Join Question

Hi,
I am trying to do a JOIN on two tables where one table could have more than
one record however I only want the top one. Is there a way to do this in
the JOIN statement or do I have to seperate the JOIN into a sub select
statement?
ThanksWhat do you mean by "top one"? Tables have no inherent order so that phrase
doesn't mean anything on its own. You'll most likely need a self-join or a
subquery to achieve this but it does depend on requirements.
David Portas
SQL Server MVP
--
"Tim" <timmy@.timemail.com> wrote in message
news:OQBAvt6wFHA.2652@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am trying to do a JOIN on two tables where one table could have more
> than one record however I only want the top one. Is there a way to do
> this in the JOIN statement or do I have to seperate the JOIN into a sub
> select statement?
> Thanks
>

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)

Join question

SQL 7.0
How do I do this in Transact SQL? Is this a join issue?
If the record in B has a flag = 0, then that record
should be deleted from A?
Thx,
Don
Something like this perhaps:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE flag = 0
AND B.key_col = A.key_col)
David Portas
SQL Server MVP
|||Perfect!
Thank you very much,
Don

>--Original Message--
>Something like this perhaps:
>DELETE FROM A
> WHERE EXISTS
> (SELECT *
> FROM B
> WHERE flag = 0
> AND B.key_col = A.key_col)
>--
>David Portas
>SQL Server MVP
>--
>
>.
>

Join question

SQL 7.0
How do I do this in Transact SQL? Is this a join issue?
If the record in B has a flag = 0, then that record
should be deleted from A?
Thx,
DonSomething like this perhaps:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE flag = 0
AND B.key_col = A.key_col)
David Portas
SQL Server MVP
--|||Perfect!
Thank you very much,
Don

>--Original Message--
>Something like this perhaps:
>DELETE FROM A
> WHERE EXISTS
> (SELECT *
> FROM B
> WHERE flag = 0
> AND B.key_col = A.key_col)
>--
>David Portas
>SQL Server MVP
>--
>
>.
>

Join question

SQL 7.0
How do I do this in Transact SQL? Is this a join issue?
If the record in B has a flag = 0, then that record
should be deleted from A?
Thx,
DonSomething like this perhaps:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE flag = 0
AND B.key_col = A.key_col)
--
David Portas
SQL Server MVP
--|||Perfect!
Thank you very much,
Don
>--Original Message--
>Something like this perhaps:
>DELETE FROM A
> WHERE EXISTS
> (SELECT *
> FROM B
> WHERE flag = 0
> AND B.key_col = A.key_col)
>--
>David Portas
>SQL Server MVP
>--
>
>.
>