Joins

I am really stuck trying to get a join working.. I am not even sure it is
possible. I have 2 tables. One containing Purchase Orders and another
containing Vendor Transactions. I need to link both. The follow
Transact-SQL is an example of what I currently get.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Purchase Orders]
GO
CREATE TABLE [dbo].[Purchase Orders] (
[po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_Invoiced)
VALUES ('00000008', 'ABC', 'ITEM123', '40')
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_Invoiced)
VALUES ('00000008', 'ABC', 'ITEM123', '50')
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_Invoiced)
VALUES ('00000008', 'ABC', 'ITEM123', '60')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Vendor
Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Vendor Transactions]
GO
CREATE TABLE [dbo].[Vendor Transactions] (
[voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
VALUES ('0009', 'ABC', '00000008')
INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
VALUES ('0010', 'ABC', '00000008')
INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
VALUES ('0011', 'ABC', '00000008')
SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
dbo.[Vendor Transactions].po_no AS EXPR1, dbo.[Vendor
Transactions].voucher_no
FROM dbo.[Vendor Transactions] INNER JOIN
dbo.[Purchase Orders] ON dbo.[Vendor
Transactions].po_no = dbo.[Purchase Orders].po_no AND
dbo.[Vendor Transactions].Vend_no = dbo.[Purchase
Orders].vend_no
Query Results are
00000008 ABC ITEM123 40 00000008 0009
00000008 ABC ITEM123 40 00000008 0010
00000008 ABC ITEM123 40 00000008 0011
00000008 ABC ITEM123 50 00000008 0009
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 50 00000008 0011
00000008 ABC ITEM123 60 00000008 0009
00000008 ABC ITEM123 60 00000008 0010
00000008 ABC ITEM123 60 00000008 0011
What I actually want to see is
00000008 ABC ITEM123 40 00000008 0009
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 60 00000008 0011
Any help gratefully received. Thanks SarahGoing from your data, I was trying to solve your problem,
Thought I had to eliminate the lower numbers. (Incorrect).
Why is the following correct ? :
00000008 ABC ITEM123 40 00000008 0009
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 60 00000008 0011
Or is this result set also correct ? :
00000008 ABC ITEM123 40 00000008 0011
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 60 00000008 0009
If you are only joining on po_no and vend_no, then you
get 9 result rows because all po_no's and vend_no's are
the same.
If you only want to join one [Purchase Orders] row with
one [Vendor Transactions] row you have to supply a criterium
on which the join should be made.
This question has to be answered before a solution can be given.
(A rough guess would be that voucher_no is missing in the [Purchase Orders]
table and this should be added to the on clause.).
I started of by shortening your code but got stuck on the above
question. I know it is a little impolite to rewrite somebody else's work,
but I did this for clearity for myself. (In our organisation we try to
avoid spaces in identifier names, because there are a lot of
systems which can not handle spaces in identifiers. In your
code the example was broken on several of those spaces for
example.)
SELECT
B.po_no,
B.vend_no,
B.Item_no,
B.Qty_Invoiced,
A.po_no ,
A.voucher_no
FROM
dbo.[Vendor Transactions] A
INNER JOIN
dbo.[Purchase Orders] B
ON
A.po_no = B.po_no AND
A.Vend_no = B.vend_no
ben brugman
"Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> I am really stuck trying to get a join working.. I am not even sure it is
> possible. I have 2 tables. One containing Purchase Orders and another
> containing Vendor Transactions. I need to link both. The follow
> Transact-SQL is an example of what I currently get.
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[Purchase Orders]
> GO
> CREATE TABLE [dbo].[Purchase Orders] (
> [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
Qty_Invoiced)
> VALUES ('00000008', 'ABC', 'ITEM123', '40')
> INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
Qty_Invoiced)
> VALUES ('00000008', 'ABC', 'ITEM123', '50')
> INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
Qty_Invoiced)
> VALUES ('00000008', 'ABC', 'ITEM123', '60')
> if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Vendor
> Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Vendor Transactions]
> GO
> CREATE TABLE [dbo].[Vendor Transactions] (
> [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> VALUES ('0009', 'ABC', '00000008')
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> VALUES ('0010', 'ABC', '00000008')
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> VALUES ('0011', 'ABC', '00000008')
> SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
> dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> dbo.[Vendor Transactions].po_no AS EXPR1,
dbo.[Vendor
> Transactions].voucher_no
> FROM dbo.[Vendor Transactions] INNER JOIN
> dbo.[Purchase Orders] ON dbo.[Vendor
> Transactions].po_no = dbo.[Purchase Orders].po_no AND
> dbo.[Vendor Transactions].Vend_no = dbo.[Purchase
> Orders].vend_no
> Query Results are
> 00000008 ABC ITEM123 40 00000008 0009
> 00000008 ABC ITEM123 40 00000008 0010
> 00000008 ABC ITEM123 40 00000008 0011
> 00000008 ABC ITEM123 50 00000008 0009
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 50 00000008 0011
> 00000008 ABC ITEM123 60 00000008 0009
> 00000008 ABC ITEM123 60 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0011
> What I actually want to see is
> 00000008 ABC ITEM123 40 00000008 0009
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0011
> Any help gratefully received. Thanks Sarah
>|||Ben
Thanks for looking at this.
The problem is the Voucher Number is not stored on the Purchase Order Table.
So the 9 records will be returned in my query. I don't think I can do this
without having the voucher number on the Purchase Order Table.
"ben brugman" <ben@.niethier.nl> wrote in message
news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> Going from your data, I was trying to solve your problem,
> Thought I had to eliminate the lower numbers. (Incorrect).
> Why is the following correct ? : THIS IS BECAUSE THE FIRST RECORD FOR
ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC ETC
> 00000008 ABC ITEM123 40 00000008 0009
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0011
> Or is this result set also correct ? : NO THIS IS NOT CORRECT
> 00000008 ABC ITEM123 40 00000008 0011
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0009
> If you are only joining on po_no and vend_no, then you
> get 9 result rows because all po_no's and vend_no's are
> the same.
> If you only want to join one [Purchase Orders] row with
> one [Vendor Transactions] row you have to supply a criterium
> on which the join should be made.
> This question has to be answered before a solution can be given.
> (A rough guess would be that voucher_no is missing in the [Purchase
Orders]
> table and this should be added to the on clause.).
> I started of by shortening your code but got stuck on the above
> question. I know it is a little impolite to rewrite somebody else's work,
> but I did this for clearity for myself. (In our organisation we try to
> avoid spaces in identifier names, because there are a lot of
> systems which can not handle spaces in identifiers. In your
> code the example was broken on several of those spaces for
> example.)
> SELECT
> B.po_no,
> B.vend_no,
> B.Item_no,
> B.Qty_Invoiced,
> A.po_no ,
> A.voucher_no
> FROM
> dbo.[Vendor Transactions] A
> INNER JOIN
> dbo.[Purchase Orders] B
> ON
> A.po_no = B.po_no AND
> A.Vend_no = B.vend_no
>
> ben brugman
>
>
> "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > I am really stuck trying to get a join working.. I am not even sure it
is
> > possible. I have 2 tables. One containing Purchase Orders and another
> > containing Vendor Transactions. I need to link both. The follow
> > Transact-SQL is an example of what I currently get.
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> N'IsUserTable')
> > = 1)
> > drop table [dbo].[Purchase Orders]
> > GO
> >
> > CREATE TABLE [dbo].[Purchase Orders] (
> > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> Qty_Invoiced)
> > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> Qty_Invoiced)
> > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> Qty_Invoiced)
> > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> >
> > if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Vendor
> > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[Vendor Transactions]
> > GO
> >
> > CREATE TABLE [dbo].[Vendor Transactions] (
> > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > VALUES ('0009', 'ABC', '00000008')
> > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > VALUES ('0010', 'ABC', '00000008')
> > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > VALUES ('0011', 'ABC', '00000008')
> >
> > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
> > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > dbo.[Vendor Transactions].po_no AS EXPR1,
> dbo.[Vendor
> > Transactions].voucher_no
> > FROM dbo.[Vendor Transactions] INNER JOIN
> > dbo.[Purchase Orders] ON dbo.[Vendor
> > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > dbo.[Vendor Transactions].Vend_no = dbo.[Purchase
> > Orders].vend_no
> >
> > Query Results are
> > 00000008 ABC ITEM123 40 00000008 0009
> > 00000008 ABC ITEM123 40 00000008 0010
> > 00000008 ABC ITEM123 40 00000008 0011
> > 00000008 ABC ITEM123 50 00000008 0009
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 50 00000008 0011
> > 00000008 ABC ITEM123 60 00000008 0009
> > 00000008 ABC ITEM123 60 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0011
> >
> > What I actually want to see is
> >
> > 00000008 ABC ITEM123 40 00000008 0009
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0011
> >
> > Any help gratefully received. Thanks Sarah
> >
> >
>|||"Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
news:eDusoH7$DHA.4012@.tk2msftngp13.phx.gbl...
> Ben
> Thanks for looking at this.
> The problem is the Voucher Number is not stored on the Purchase Order
Table.
> So the 9 records will be returned in my query. I don't think I can do
this
> without having the voucher number on the Purchase Order Table.
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> > Going from your data, I was trying to solve your problem,
> > Thought I had to eliminate the lower numbers. (Incorrect).
> >
> > Why is the following correct ? :
> THIS IS BECAUSE THE FIRST RECORD FOR
> ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC ETC
(If answering in line, please start on a new line, I almost missed this,
the capital writing made it visible).
Problem with databases there is no first or second rows, in
a database there is no order of rows. Although in
the implementation in a real database the database does
'order' the rows in the fysical table, this can not be used.
To get an order you have to order the fields yourself
by using 'order by', offcourse this requires a field on
which you want to order.
Now suppose you have ordered both tables. Then to
make a join on the table is quite complex, because you want
to join the first rows the second rows etc. But this join
depends on the rows allready joined or on the exact number
in the roworder.
If you have an order within the rows you could add an
extra column to signify the order of rows and use that
on both tables to perform the join on.
But I doubt that this will work in the end, because I still
think some information is lacking. If the number of row
is not equal what are you going to join. Then if the number
of rows is equal, why are they in sepparate tables.
ben brugman
> >
> > 00000008 ABC ITEM123 40 00000008 0009
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0011
> >
> > Or is this result set also correct ? : NO THIS IS NOT CORRECT
> > 00000008 ABC ITEM123 40 00000008 0011
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0009
> >
> > If you are only joining on po_no and vend_no, then you
> > get 9 result rows because all po_no's and vend_no's are
> > the same.
> >
> > If you only want to join one [Purchase Orders] row with
> > one [Vendor Transactions] row you have to supply a criterium
> > on which the join should be made.
> > This question has to be answered before a solution can be given.
> > (A rough guess would be that voucher_no is missing in the [Purchase
> Orders]
> > table and this should be added to the on clause.).
> >
> > I started of by shortening your code but got stuck on the above
> > question. I know it is a little impolite to rewrite somebody else's
work,
> > but I did this for clearity for myself. (In our organisation we try to
> > avoid spaces in identifier names, because there are a lot of
> > systems which can not handle spaces in identifiers. In your
> > code the example was broken on several of those spaces for
> > example.)
> >
> > SELECT
> > B.po_no,
> > B.vend_no,
> > B.Item_no,
> > B.Qty_Invoiced,
> > A.po_no ,
> > A.voucher_no
> > FROM
> > dbo.[Vendor Transactions] A
> > INNER JOIN
> > dbo.[Purchase Orders] B
> > ON
> > A.po_no = B.po_no AND
> > A.Vend_no = B.vend_no
> >
> >
> > ben brugman
> >
> >
> >
> >
> > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > > I am really stuck trying to get a join working.. I am not even sure it
> is
> > > possible. I have 2 tables. One containing Purchase Orders and
another
> > > containing Vendor Transactions. I need to link both. The follow
> > > Transact-SQL is an example of what I currently get.
> > >
> > > if exists (select * from dbo.sysobjects where id => > > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> > N'IsUserTable')
> > > = 1)
> > > drop table [dbo].[Purchase Orders]
> > > GO
> > >
> > > CREATE TABLE [dbo].[Purchase Orders] (
> > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > Qty_Invoiced)
> > > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > Qty_Invoiced)
> > > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > Qty_Invoiced)
> > > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> > >
> > > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[Vendor
> > > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > > drop table [dbo].[Vendor Transactions]
> > > GO
> > >
> > > CREATE TABLE [dbo].[Vendor Transactions] (
> > > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > VALUES ('0009', 'ABC', '00000008')
> > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > VALUES ('0010', 'ABC', '00000008')
> > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > VALUES ('0011', 'ABC', '00000008')
> > >
> > > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
> > > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > > dbo.[Vendor Transactions].po_no AS EXPR1,
> > dbo.[Vendor
> > > Transactions].voucher_no
> > > FROM dbo.[Vendor Transactions] INNER JOIN
> > > dbo.[Purchase Orders] ON dbo.[Vendor
> > > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > > dbo.[Vendor Transactions].Vend_no =dbo.[Purchase
> > > Orders].vend_no
> > >
> > > Query Results are
> > > 00000008 ABC ITEM123 40 00000008 0009
> > > 00000008 ABC ITEM123 40 00000008 0010
> > > 00000008 ABC ITEM123 40 00000008 0011
> > > 00000008 ABC ITEM123 50 00000008 0009
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 50 00000008 0011
> > > 00000008 ABC ITEM123 60 00000008 0009
> > > 00000008 ABC ITEM123 60 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0011
> > >
> > > What I actually want to see is
> > >
> > > 00000008 ABC ITEM123 40 00000008 0009
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0011
> > >
> > > Any help gratefully received. Thanks Sarah
> > >
> > >
> >
> >
>|||For Oracle the following construct is possible :
SELECT
B.po_no,
B.vend_no,
B.Item_no,
B.Qty_Invoiced,
A.po_no ,
A.voucher_no
FROM
(select ..., rownum r1 from VendorTransactions) A
INNER JOIN
(select ..., rownum r2 from Purchase Orders) B
ON
R1 = R2
But here the other criteria are left out.
In SQL-server it would be easier to add a (temporary) extra column,
to both tables,
fill that with the required numbers and do the join on those numbers.
The filling of numbers offcourse depends on what you actually want.
Say the extra column is called X
alter table [vendor transactions] add x int
UPDATE [vendor transactions]
SET X = (select count(*) from [vendor transactions] as T2
where [vendor transactions].po_no = T2.po_no and
( [vendor transactions].voucher_no >= T2.voucher_no) )
select * from [vendor transactions]
-- will result in :
voucher_no Vend_no po_no x
-- -- -- --
0009 ABC 00000008 1
0010 ABC 00000008 2
0011 ABC 00000008 3
I do not know if the counting has to be done on Vend_no or po_no or both,
adjust
the example for that. The ordering here is done on the value of voucher_no.
For purchase orders the ordering probably has to be done on Qty_Invoiced,
this does not make sense to me, but it is the only column which
discriminates
in the example.
If the extra column is done on both tables, one can join on that column x.
(And afterwards drop the column x).
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:c208fo$91$1@.reader08.wxs.nl...
> "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> news:eDusoH7$DHA.4012@.tk2msftngp13.phx.gbl...
> > Ben
> >
> > Thanks for looking at this.
> >
> > The problem is the Voucher Number is not stored on the Purchase Order
> Table.
> > So the 9 records will be returned in my query. I don't think I can do
> this
> > without having the voucher number on the Purchase Order Table.
> >
>
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> > > Going from your data, I was trying to solve your problem,
> > > Thought I had to eliminate the lower numbers. (Incorrect).
> > >
> > > Why is the following correct ? :
> > THIS IS BECAUSE THE FIRST RECORD FOR
> > ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC ETC
> (If answering in line, please start on a new line, I almost missed this,
> the capital writing made it visible).
> Problem with databases there is no first or second rows, in
> a database there is no order of rows. Although in
> the implementation in a real database the database does
> 'order' the rows in the fysical table, this can not be used.
> To get an order you have to order the fields yourself
> by using 'order by', offcourse this requires a field on
> which you want to order.
> Now suppose you have ordered both tables. Then to
> make a join on the table is quite complex, because you want
> to join the first rows the second rows etc. But this join
> depends on the rows allready joined or on the exact number
> in the roworder.
> If you have an order within the rows you could add an
> extra column to signify the order of rows and use that
> on both tables to perform the join on.
> But I doubt that this will work in the end, because I still
> think some information is lacking. If the number of row
> is not equal what are you going to join. Then if the number
> of rows is equal, why are they in sepparate tables.
> ben brugman
>
> > >
> > > 00000008 ABC ITEM123 40 00000008 0009
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0011
> > >
> > > Or is this result set also correct ? : NO THIS IS NOT CORRECT
> > > 00000008 ABC ITEM123 40 00000008 0011
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0009
> > >
> > > If you are only joining on po_no and vend_no, then you
> > > get 9 result rows because all po_no's and vend_no's are
> > > the same.
> > >
> > > If you only want to join one [Purchase Orders] row with
> > > one [Vendor Transactions] row you have to supply a criterium
> > > on which the join should be made.
> > > This question has to be answered before a solution can be given.
> > > (A rough guess would be that voucher_no is missing in the [Purchase
> > Orders]
> > > table and this should be added to the on clause.).
> > >
> > > I started of by shortening your code but got stuck on the above
> > > question. I know it is a little impolite to rewrite somebody else's
> work,
> > > but I did this for clearity for myself. (In our organisation we try to
> > > avoid spaces in identifier names, because there are a lot of
> > > systems which can not handle spaces in identifiers. In your
> > > code the example was broken on several of those spaces for
> > > example.)
> > >
> > > SELECT
> > > B.po_no,
> > > B.vend_no,
> > > B.Item_no,
> > > B.Qty_Invoiced,
> > > A.po_no ,
> > > A.voucher_no
> > > FROM
> > > dbo.[Vendor Transactions] A
> > > INNER JOIN
> > > dbo.[Purchase Orders] B
> > > ON
> > > A.po_no = B.po_no AND
> > > A.Vend_no = B.vend_no
> > >
> > >
> > > ben brugman
> > >
> > >
> > >
> > >
> > > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > > news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > > > I am really stuck trying to get a join working.. I am not even sure
it
> > is
> > > > possible. I have 2 tables. One containing Purchase Orders and
> another
> > > > containing Vendor Transactions. I need to link both. The follow
> > > > Transact-SQL is an example of what I currently get.
> > > >
> > > > if exists (select * from dbo.sysobjects where id => > > > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> > > N'IsUserTable')
> > > > = 1)
> > > > drop table [dbo].[Purchase Orders]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Purchase Orders] (
> > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > Qty_Invoiced)
> > > > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > Qty_Invoiced)
> > > > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > Qty_Invoiced)
> > > > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> > > >
> > > > if exists (select * from dbo.sysobjects where id => > > object_id(N'[dbo].[Vendor
> > > > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > > > drop table [dbo].[Vendor Transactions]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Vendor Transactions] (
> > > > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > > VALUES ('0009', 'ABC', '00000008')
> > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > > VALUES ('0010', 'ABC', '00000008')
> > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > > VALUES ('0011', 'ABC', '00000008')
> > > >
> > > > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase
Orders].vend_no,
> > > > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > > > dbo.[Vendor Transactions].po_no AS EXPR1,
> > > dbo.[Vendor
> > > > Transactions].voucher_no
> > > > FROM dbo.[Vendor Transactions] INNER JOIN
> > > > dbo.[Purchase Orders] ON dbo.[Vendor
> > > > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > > > dbo.[Vendor Transactions].Vend_no => dbo.[Purchase
> > > > Orders].vend_no
> > > >
> > > > Query Results are
> > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > 00000008 ABC ITEM123 40 00000008 0010
> > > > 00000008 ABC ITEM123 40 00000008 0011
> > > > 00000008 ABC ITEM123 50 00000008 0009
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 50 00000008 0011
> > > > 00000008 ABC ITEM123 60 00000008 0009
> > > > 00000008 ABC ITEM123 60 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0011
> > > >
> > > > What I actually want to see is
> > > >
> > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0011
> > > >
> > > > Any help gratefully received. Thanks Sarah
> > > >
> > > >
> > >
> > >
> >
> >
>|||Ben
Thanks again for your time and knowledge on this.. I haven't had a chance to
sit down and go through your postings. I am looking to do this over the
next few days.
Kind Regards Sarah
"ben brugman" <ben@.niethier.nl> wrote in message
news:OD2VYaFAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> For Oracle the following construct is possible :
> SELECT
> B.po_no,
> B.vend_no,
> B.Item_no,
> B.Qty_Invoiced,
> A.po_no ,
> A.voucher_no
> FROM
> (select ..., rownum r1 from VendorTransactions) A
> INNER JOIN
> (select ..., rownum r2 from Purchase Orders) B
> ON
> R1 = R2
> But here the other criteria are left out.
> In SQL-server it would be easier to add a (temporary) extra column,
> to both tables,
> fill that with the required numbers and do the join on those numbers.
> The filling of numbers offcourse depends on what you actually want.
> Say the extra column is called X
> alter table [vendor transactions] add x int
> UPDATE [vendor transactions]
> SET X = (select count(*) from [vendor transactions] as T2
> where [vendor transactions].po_no = T2.po_no and
> ( [vendor transactions].voucher_no >= T2.voucher_no) )
> select * from [vendor transactions]
> -- will result in :
> voucher_no Vend_no po_no x
> -- -- -- --
> 0009 ABC 00000008 1
> 0010 ABC 00000008 2
> 0011 ABC 00000008 3
> I do not know if the counting has to be done on Vend_no or po_no or both,
> adjust
> the example for that. The ordering here is done on the value of
voucher_no.
> For purchase orders the ordering probably has to be done on Qty_Invoiced,
> this does not make sense to me, but it is the only column which
> discriminates
> in the example.
> If the extra column is done on both tables, one can join on that column x.
> (And afterwards drop the column x).
> ben brugman
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c208fo$91$1@.reader08.wxs.nl...
> >
> > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > news:eDusoH7$DHA.4012@.tk2msftngp13.phx.gbl...
> > > Ben
> > >
> > > Thanks for looking at this.
> > >
> > > The problem is the Voucher Number is not stored on the Purchase Order
> > Table.
> > > So the 9 records will be returned in my query. I don't think I can do
> > this
> > > without having the voucher number on the Purchase Order Table.
> > >
> >
> >
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> > > > Going from your data, I was trying to solve your problem,
> > > > Thought I had to eliminate the lower numbers. (Incorrect).
> > > >
> > > > Why is the following correct ? :
> >
> > > THIS IS BECAUSE THE FIRST RECORD FOR
> > > ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC
ETC
> > (If answering in line, please start on a new line, I almost missed this,
> > the capital writing made it visible).
> >
> > Problem with databases there is no first or second rows, in
> > a database there is no order of rows. Although in
> > the implementation in a real database the database does
> > 'order' the rows in the fysical table, this can not be used.
> >
> > To get an order you have to order the fields yourself
> > by using 'order by', offcourse this requires a field on
> > which you want to order.
> >
> > Now suppose you have ordered both tables. Then to
> > make a join on the table is quite complex, because you want
> > to join the first rows the second rows etc. But this join
> > depends on the rows allready joined or on the exact number
> > in the roworder.
> >
> > If you have an order within the rows you could add an
> > extra column to signify the order of rows and use that
> > on both tables to perform the join on.
> >
> > But I doubt that this will work in the end, because I still
> > think some information is lacking. If the number of row
> > is not equal what are you going to join. Then if the number
> > of rows is equal, why are they in sepparate tables.
> >
> > ben brugman
> >
> >
> > > >
> > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0011
> > > >
> > > > Or is this result set also correct ? : NO THIS IS NOT CORRECT
> > > > 00000008 ABC ITEM123 40 00000008 0011
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0009
> > > >
> > > > If you are only joining on po_no and vend_no, then you
> > > > get 9 result rows because all po_no's and vend_no's are
> > > > the same.
> > > >
> > > > If you only want to join one [Purchase Orders] row with
> > > > one [Vendor Transactions] row you have to supply a criterium
> > > > on which the join should be made.
> > > > This question has to be answered before a solution can be given.
> > > > (A rough guess would be that voucher_no is missing in the [Purchase
> > > Orders]
> > > > table and this should be added to the on clause.).
> > > >
> > > > I started of by shortening your code but got stuck on the above
> > > > question. I know it is a little impolite to rewrite somebody else's
> > work,
> > > > but I did this for clearity for myself. (In our organisation we try
to
> > > > avoid spaces in identifier names, because there are a lot of
> > > > systems which can not handle spaces in identifiers. In your
> > > > code the example was broken on several of those spaces for
> > > > example.)
> > > >
> > > > SELECT
> > > > B.po_no,
> > > > B.vend_no,
> > > > B.Item_no,
> > > > B.Qty_Invoiced,
> > > > A.po_no ,
> > > > A.voucher_no
> > > > FROM
> > > > dbo.[Vendor Transactions] A
> > > > INNER JOIN
> > > > dbo.[Purchase Orders] B
> > > > ON
> > > > A.po_no = B.po_no AND
> > > > A.Vend_no = B.vend_no
> > > >
> > > >
> > > > ben brugman
> > > >
> > > >
> > > >
> > > >
> > > > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > > > news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > > > > I am really stuck trying to get a join working.. I am not even
sure
> it
> > > is
> > > > > possible. I have 2 tables. One containing Purchase Orders and
> > another
> > > > > containing Vendor Transactions. I need to link both. The follow
> > > > > Transact-SQL is an example of what I currently get.
> > > > >
> > > > > if exists (select * from dbo.sysobjects where id => > > > > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> > > > N'IsUserTable')
> > > > > = 1)
> > > > > drop table [dbo].[Purchase Orders]
> > > > > GO
> > > > >
> > > > > CREATE TABLE [dbo].[Purchase Orders] (
> > > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > > Qty_Invoiced)
> > > > > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > > Qty_Invoiced)
> > > > > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > > Qty_Invoiced)
> > > > > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> > > > >
> > > > > if exists (select * from dbo.sysobjects where id => > > > object_id(N'[dbo].[Vendor
> > > > > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > > > > drop table [dbo].[Vendor Transactions]
> > > > > GO
> > > > >
> > > > > CREATE TABLE [dbo].[Vendor Transactions] (
> > > > > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no,
po_no)
> > > > > VALUES ('0009', 'ABC', '00000008')
> > > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no,
po_no)
> > > > > VALUES ('0010', 'ABC', '00000008')
> > > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no,
po_no)
> > > > > VALUES ('0011', 'ABC', '00000008')
> > > > >
> > > > > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase
> Orders].vend_no,
> > > > > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > > > > dbo.[Vendor Transactions].po_no AS EXPR1,
> > > > dbo.[Vendor
> > > > > Transactions].voucher_no
> > > > > FROM dbo.[Vendor Transactions] INNER JOIN
> > > > > dbo.[Purchase Orders] ON dbo.[Vendor
> > > > > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > > > > dbo.[Vendor Transactions].Vend_no => > dbo.[Purchase
> > > > > Orders].vend_no
> > > > >
> > > > > Query Results are
> > > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > > 00000008 ABC ITEM123 40 00000008 0010
> > > > > 00000008 ABC ITEM123 40 00000008 0011
> > > > > 00000008 ABC ITEM123 50 00000008 0009
> > > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > > 00000008 ABC ITEM123 50 00000008 0011
> > > > > 00000008 ABC ITEM123 60 00000008 0009
> > > > > 00000008 ABC ITEM123 60 00000008 0010
> > > > > 00000008 ABC ITEM123 60 00000008 0011
> > > > >
> > > > > What I actually want to see is
> > > > >
> > > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > > 00000008 ABC ITEM123 60 00000008 0011
> > > > >
> > > > > Any help gratefully received. Thanks Sarah
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>