join or subselect: orders

Showing posts with label orders. Show all posts

Friday, March 23, 2012

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

Joins

Wednesday, March 21, 2012

joining tables on aspnet_Users.UserId

Iwant to join my existing tables such as ORDERS with aspnet_Users.UserId on ORDERS.CustomerId.
But I am confused which data type should I choose for customerId field.
Thedatatype of the of aspnet_Users.UserId is uniqueidentifier, butobviously I cannot assign my customerId field to uniqueidentifier datatype. I found that "The uniqueidentifier data type in SQL Server isstored natively as a 16-byte binary value." I wish aspnet_Users.UserIdwould be an integer IDENTITY field. I also want to ask if I can changeUserId field's datatype to integer?

There are also some other tables that I want to join with aspnet_Users table on its UserId field. Can you suggest any solutions?

alekhine_x9:
obviously I cannot assign my customerId field to uniqueidentifier data type.

Change "cannot" to "should", then you are correct.

|||Hi Motley, then how can I be assure of whether the aspnet_Users.UserId and MyTable.CustomerId will be same since "uniqueidentifier" will be different for every row.|||

uniqueidentifier is not like IDENTITY in that it will automatically generate a value for you. It's more like a very big int, in that it only stores what you tell it to. If you want it to contain the same value, get the value you want from the customer table, and store it in teh row you want.

|||

thanks, you are right, I must be in a very confused mood not to think that:)

I wonder does it affect performance much making joins on a uniqueidentifier instead of int. I know joins on integer value fields are faster than character based data types.

|||

Does it affect performance? Yes.

Much? No.

Joining on an int, is basically a two instruction compare and conditional jump.

Joining on an uniqueidentifier is 8 instructions (4 compare/conditional jump combinations) on 32-bit machines, 4 instructions on 64-bit machines.

Joining on a varchar/char/nvarchar/nchar is essentially a string compare loop, one operation per byte -- load up two pointers, clear flags, iterate once per byte in the shortest string, and a conditional jump. For long strings at best, it'd be a register copy, and AND operation, a shift right operation, clear flags, a 64-byte string compare loop (one iteration for every 4 bytes), followed by 1-3 8-byte string compare iterations, and a conditional jump. It gets even more complex, as for each character in the string, you have to do an index lookup to get the right collalition order if it's a < or > join, or if multiple characters are considered "equal" -- like "m" and "M".

Although, the compare portion of the joins is not usually the most intensive part of the actual join operation, so while the compare itself is 2-4 times as slow, the actual performance hit is usually negligable.

Monday, March 19, 2012

Joining Problem

I am having some problme with a join
There is two tables a customer and orders
The customer table has a unique list of customer
The orders table fills up as they come in

Customer Table
ID Name
1 Cust1
2 Cust2
3 Cust3

Orders Table
ID Customer Product
1 1 Tea
2 1 Coffee
3 1 Milk
4 2 Tea
5 2 Coffee

So there can be multiple orders for one customer

How would I show a list of customer who have never
ordered 'Milk'

This usually does it for me:

select c.ID, c.Name
from Customers c left join Orders o
on c.ID = o.Customer
where c.ID not in
(select o.Customer from Orders o
where o.Product = 'Milk')

It's not the prettiest code but it should work for you.

|||

I think this should be simpler and the output customer will be unique ( no double record )

select c.ID, c.Name
from Customers
where c.ID not in
(select o.Customer from Orders o
where o.Product = 'Milk')

Wednesday, March 7, 2012

Join Styles

Hi,
Are there any difference(in performance) between these two type of join:
SELECT Customers.CustomerID,orders.orderid FROM Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
&
select c.customerid,o.orderid from customers c,orders o where
c.customerid=o.customerid
I tried them many times while STATISTICS IO/TIME were set on and couldn't
find considerable difference.
Thanks,
Leila
Leila...
These are simply two different ANSI standards. One is older than the other
one. In general SQL Server will build the exact same execution plan for
either of them. The only difference between the two is when there are OUTER
JOINS. The newer method of using the JOIN keyword is preferred and is part
of the SQL 99 standard.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||I find the INNER JOIN syntax more readable, it also allows you to separate
join criteria from filter criteria.
Try as you might, for inner joins with simple join criteria, I don't think
you'll ever see performance differences, because internally the engine sees
them as equivalent. Now if you have complex join and filter criteria, you
might be able to see differences if the filter takes place in the join as
opposed to after.
http://www.aspfaq.com/
(Reverse address to reply.)
"Leila" <leilas@.hotpop.com> wrote in message
news:#48398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||Leila:
The ANSI style (INNER JOIN) is better because for complex queries, the
optimizer can more easily select a better execution plan than for the old
style query; you won't find differences in simple queries though.
You might as well stop cross-posting.
Regards,
Eric Garza
AMIGE
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>
|||http://www.microsoft.com/sql/techinf...ent/july23.asp
AMB
"Leila" wrote:

> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>

Join Styles

Hi,
Are there any difference(in performance) between these two type of join:
SELECT Customers.CustomerID,orders.orderid FROM Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
&
select c.customerid,o.orderid from customers c,orders o where
c.customerid=o.customerid
I tried them many times while STATISTICS IO/TIME were set on and couldn't
find considerable difference.
Thanks,
LeilaLeila...
These are simply two different ANSI standards. One is older than the other
one. In general SQL Server will build the exact same execution plan for
either of them. The only difference between the two is when there are OUTER
JOINS. The newer method of using the JOIN keyword is preferred and is part
of the SQL 99 standard.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||I find the INNER JOIN syntax more readable, it also allows you to separate
join criteria from filter criteria.
Try as you might, for inner joins with simple join criteria, I don't think
you'll ever see performance differences, because internally the engine sees
them as equivalent. Now if you have complex join and filter criteria, you
might be able to see differences if the filter takes place in the join as
opposed to after.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Leila" <leilas@.hotpop.com> wrote in message
news:#48398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||Leila:
The ANSI style (INNER JOIN) is better because for complex queries, the
optimizer can more easily select a better execution plan than for the old
style query; you won't find differences in simple queries though.
You might as well stop cross-posting.
--
Regards,
Eric Garza
AMIGE
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||http://www.microsoft.com/sql/techinfo/tips/development/july23.asp
AMB
"Leila" wrote:
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>

Join Styles

Hi,
Are there any difference(in performance) between these two type of join:
SELECT Customers.CustomerID,orders.orderid FROM Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
&
select c.customerid,o.orderid from customers c,orders o where
c.customerid=o.customerid
I tried them many times while STATISTICS IO/TIME were set on and couldn't
find considerable difference.
Thanks,
LeilaLeila...
These are simply two different ANSI standards. One is older than the other
one. In general SQL Server will build the exact same execution plan for
either of them. The only difference between the two is when there are OUTER
JOINS. The newer method of using the JOIN keyword is preferred and is part
of the SQL 99 standard.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||I find the INNER JOIN syntax more readable, it also allows you to separate
join criteria from filter criteria.
Try as you might, for inner joins with simple join criteria, I don't think
you'll ever see performance differences, because internally the engine sees
them as equivalent. Now if you have complex join and filter criteria, you
might be able to see differences if the filter takes place in the join as
opposed to after.
http://www.aspfaq.com/
(Reverse address to reply.)
"Leila" <leilas@.hotpop.com> wrote in message
news:#48398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||Leila:
The ANSI style (INNER JOIN) is better because for complex queries, the
optimizer can more easily select a better execution plan than for the old
style query; you won't find differences in simple queries though.
You might as well stop cross-posting.
Regards,
Eric Garza
AMIGE
"Leila" <leilas@.hotpop.com> wrote in message
news:%2348398bvEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>|||http://www.microsoft.com/sql/techin...ment/july23.asp
AMB
"Leila" wrote:

> Hi,
> Are there any difference(in performance) between these two type of join:
> SELECT Customers.CustomerID,orders.orderid FROM Customers
> INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
> &
> select c.customerid,o.orderid from customers c,orders o where
> c.customerid=o.customerid
> I tried them many times while STATISTICS IO/TIME were set on and couldn't
> find considerable difference.
> Thanks,
> Leila
>
>

Friday, February 24, 2012

JOIN Question

Good Morning,

I'm attempting to pull information regarding the orders placed over the last week for all of our customers. I want my result set to show a listing for each customer and any orders they have placed, or if they have not placed any orders, just a line with a NULL value or something similar. Ultimately, I may use a COUNT on this information, but for right now, I am just trying to work around the JOIN issue I'm having. Here is an example of what I am trying to do. I rewrote the query with generic names.

Code Snippet

SELECT Customers.CustID, Orders.OrderID, Order.OrderDate
FROM Customers FULL OUTER JOIN Orders
ON Customers.CustID = Orders.CustID
WHERE Orders.OrderDate >= DATEADD(week, DATEDIFF(week, 0, GETDATE())-1, 0) AND
Orders.OrderDate < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
ORDER BY Orders.OrderDate, Customers.CustID

I've tried this query using LEFT, RIGHT and FULL OUTER JOINS, but none seem to give me the results I desire. Below is an example of what I would like to see.

CustID OrderID OrderDate

00001 02345 08/05/2007
00001 02356 08/05/2007
00002 02347 08/05/2007
00003 NULL 08/05/2007
00004 02349 08/05/2007
00001 NULL 08/06/2007
00002 02358 08/06/2007
00003 02360 08/06/2007
00004 NULL 08/06/2007
.
.
.
.
.

Many thanks in advance for any help you can provide.
--
Anthony

The following query might help you..

Code Snippet

Create Table #orderdata (

[CustID] Varchar(100) ,

[OrderID] Varchar(100) ,

[OrderDate] datetime

);

Insert Into #orderdata Values('00001','02345','08/05/2007');

Insert Into #orderdata Values('00001','02356','08/05/2007');

Insert Into #orderdata Values('00002','02347','08/05/2007');

Insert Into #orderdata Values('00004','02349','08/05/2007');

Insert Into #orderdata Values('00002','02358','08/06/2007');

Insert Into #orderdata Values('00003','02360','08/06/2007');

Create Table #customer (

[CustID] Varchar(100)

);

Insert Into #customer Values('00001');

Insert Into #customer Values('00002');

Insert Into #customer Values('00003');

Insert Into #customer Values('00004');

Create function which will list all the dates between given from & to dates.

Code Snippet

Create Function DaysBetween(@.Startdate datetime,@.Enddate datetime)

returns @.dates Table (Date datetime)

Begin

While @.Startdate <= @.Enddate

Begin

Insert Into @.dates values(@.Startdate);

Set @.Startdate = Dateadd(dd,1,@.Startdate);

End

return;

End

First Cross Join the dates & customer id, then use outer join to link with your order table

Code Snippet

Select CustAndDate.CustID, OrderID, CustAndDate.Date from

(Select date,[CustID] from DaysBetween( DATEADD(week, DATEDIFF(week, 0, GETDATE())-2, 0), DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)) days

cross join #customer) as CustAndDate

Left Outer Join #orderdata orders on orders.[CustID] = CustAndDate.[CustID] and orders.[OrderDate] = CustAndDate.date

|||

You were unable to get the results you desired simple because if a Customer did NOT place an order on a particular day, there would not be a OrderDate available.

As Mani has demonstrated very well, the solution to problems like this is to use some form of a Calendar table, and then to include the Calendar table in the JOIN to have a way to include 'missing dates'. You may find this article about the benefits of having a Calendar table to be useful. (Most databases 'should' have a permanent Calendar table. So many different operations involving dates are simplified by using a Calendar table.)

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

Mani -Excellent presentation of your suggested solution!!

|||First, thanks to both Mani and Arnie.

I'm still a bit confused on this. I'm reading about creating a calendar table now, but I guess I don't understand why if I request the names from one table, then join with orders in another table that it would not return at least one row for each name, regardless of whether it had an order associated and my thought was that in those situations, that single row would return NULL and I could work with that.

The reason I went the route of using a JOIN was that I saw if I just pulled from the orders table and a particular customer did not place an order, there would be no way of coming up with those names out of thin air.

I apologize for my continued confusion here.

Thanks Again
--
Anthony
|||

Anthony,

You can create a LEFT JOIN between Customers and Orders, and ALL Customers will be listed -BUT the OrderDate will be NULL.

From you presentation of desired results, there would be no way to indicate a OrderDate for a Customer that has not placed an Order -SINCE you don't know on what date they didn't place it.

So to have a 'placeholder' for the Customer on each date (as in your desired results), you have to JOIN with a table that has all possible dates -therefore the JOIN with a Calendar table.

|||Arnie,

After playing around with this a bit more, I understand now. It turns out, we had created a function previously that has the same effect as the Calendar table. I was able to select the customers and dates using the cross join on the function and customers table, then use the left join on that and the orders table and it worked just as Mani had stated.

I thank you both again for your assistance. I may still look into creating numbers and calendar tables as this seems like it might be a better approach than our existing function.
--
Anthony
|||Having static tables is most definitely better than having a function that is 'on demand' creating a temporary table. Every time you call the function, it creates a table -so every time you use it, you are wasting time and slowing performance.

|||Alright. I've got another question that is directly related to this. If I should post to a new thread, I apologize. What if I wanted to get the same result, but also factor in a particular product? So my example of how the output would look would be the same, but there would be an addition condition of something like ...

Code Snippet

AND ProductID = 23456

The Product ID is in the Orders table, so I tried just tacking that on to the WHERE clause in my outer select (the one with the LEFT OUTER JOIN), but it results in the ones that do not have that product not showing instead of showing zeros. In fact, if I use the query for all orders, it works, but when I add on that extra condition, with no other changes, it does not. I would venture this has something to do with the nested selects and the couple joins.

I feel like I understand what was done in your previous suggestion, but I'm not sure I am familiar enough to modify that in a way to suit this need.

Thanks again for the help.
--
Anthony

|||

Add

AND ProductID = 23456

to the JOIN condition for the #OrderData table (using Mani's sample code).

|||Arnie,

Sorry I didn't get your question until this morning. I believe I am already doing what you are suggesting, but it is not working. Currently, I have my query pulling the orders for specific date ranges dependant upon the day of the week. In my existing query, I'm using the function that we already had setup (I will likely setup the numbers and calendar tables) and so long as I am looking at all orders, it works fine. Here is my query so far. By the way, I've had to modify my query so not to give out sensitive information. I apologize if I've messed something up in the query below, but I assure you, this does give me the desired result.

Code Snippet

DECLARE @.StartTime datetime
DECLARE @.EndTime datetime

IF DATEPART(weekday, GETDATE()) IN (1, 2)
BEGIN
SET @.StartTime = DATEADD(week, DATEDIFF(week, 0, GETDATE())-1, 0)
SET @.EndTime = DATEADD(day, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0))
END
ELSE IF DATEPART(weekday, GETDATE()) = 3
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 4
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-2, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 5
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-3, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 6
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-4, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 7
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-5, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END

SELECT CustAndDate.CustName, COUNT(Orders.[Order Num]) AS Total, LEFT(DATENAME(weekday, CustAndDate.TheDate), 3) AS DayName
FROM (
SELECT TheDate, CustName, CustID
FROM fn_TimeDimension(@.StartTime,@.EndTime) CROSS JOIN Customers
WHERE CustID IN (00020, 00025, 00027, 00029, 00030, 00032, 00034)
) AS CustAndDate LEFT OUTER JOIN Orders
ON CustAndDate.CustID = Orders.CustID AND CONVERT(char(8), CustAndDate.TheDate, 112) = CONVERT(char(8), Orders.[Order Date], 112)
WHERE DATEPART(weekday, CustAndDate.TheDate) IN (2, 3, 4, 5, 6)
GROUP BY DATEPART(weekday, CustAndDate.TheDate), CustAndDate.CustName, LEFT(DATENAME(weekday, CustAndDate.TheDate), 3)
ORDER BY DATEPART(weekday, CustAndDate.TheDate), CustAndDate.CustName

Now what I'm trying to do is restrict this to only show certain products, so my thought was that after the line that reads

Code Snippet

WHERE DATEPART(weekday, CustAndDate.TheDate) IN (2, 3, 4, 5, 6)

I would add another line that reads

Code Snippet

AND ProdID = 12345

Unfortunately, this does not work. So I don't know if I am putting that additional condition in the wrong place or what. The ProdID is in the Orders table.

Thanks Again for all your help.
--
Anthony

|||

Try something more like this:

LEFT OUTER JOIN Orders
ON ( CustAndDate.CustID = Orders.CustID

AND CONVERT(char(8), CustAndDate.TheDate, 112) = CONVERT(char(8), Orders.[Order Date], 112)

AND ProdID = 12345

)
WHERE ...

AS a side comment, using CONVERT() on both sides of the equality ensures that indexes CANNOT be used and the the entire table has to be scanned, adding significantly to execution time.

|||Arnie,

This seems to work. I will have to do some more testing. The reason I was using CONVERT on both sides of the equation was to get both dates in the same format as I am under the impression that when you are performing a JOIN, your columns that you join on must be the same, so if TheDate = '08/09/2007 00:00:00' and Order Date = '08/09/2007 11:22:05', then you would have an issue. I guess I could use ISODate instead of TheDate from our calendar function or the calendar table once that has been established and just perform the convert on the date from the orders table.

What would you suggest in a situation such as mine?
|||

You could do something like this:

LEFT OUTER JOIN Orders
ON ( CustAndDate.CustID = Orders.CustID

AND ( Orders.OrderDate >= CustAndDate.TheDate

AND Orders.OrderDate < ( dateadd( day, 1, CustAndDate.TheDate ))

)

AND ProdID = 12345

)
WHERE ...

The Orders.OrderDate is not converted, and it will properly use any indexing.

Substitute your Calendar table date for the CustAndDate.TheDate value, if a better 'match'.

|||So in your example, you are joining on the condition that the Order Date be greater than or equal to 'TheDate', which would be midnight of the day in question, but not more than a day difference between the two. I will certainly take your suggestion under consideration. At present, the query is not taking more than a couple seconds to run, however I any opportunity I have to improve performance is not something to be ignored.

Thanks again to you and Mani for your assistance. I've learned quite a lot since working with SQL, but there is always something new that you've not run into before and it is great to have a resource such as these forums and individuals who take the time to assist others.

Friday, March 23, 2012

Joins

Joins

Wednesday, March 21, 2012

joining tables on aspnet_Users.UserId

Monday, March 19, 2012

Joining Problem

Wednesday, March 7, 2012

Join Styles

Join Styles

Join Styles

Friday, February 24, 2012

JOIN Question

join or subselect

Blog Archive

About Me