JOINs problem

I work for a hospital network (three different hospitals involved), and
I need to generate a result set containing ever combination of
specialty and department applicable for each doctor. The base tables
are as follows...
CREATE TABLE Hospitals
(
HospitalID VARCHAR(10)
)
CREATE TABLE Specialties
(
DoctorID VARCHAR(10),
SpecialtyID VARCHAR(10),
)
CREATE TABLE Departments
(
DoctorID VARCHAR(10),
HospitalID, VARCHAR(10),
DepartmentID VARCHAR(10)
)
INSERT Hospitals VALUES ('HOSP1')
INSERT Hospitals VALUES ('HOSP2')
INSERT Hospitals VALUES ('HOSP3')
INSERT Specialties VALUES ('JONES1', 'CARDIO1')
INSERT Departments VALUES ('JONES1', 'HOSP1', 'CARVASSUR1')
INSERT Departments VALUES ('JONES1', 'HOSP3', 'VASSUR1')
Here's the kicker: specialties apply to all hospitals, while
departments are hospital-specific. Therefore, for Dr. Jones the
following should be produced...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 CARDIO1 CARVASSUR1
JONES1 HOSP2 CARDIO1 null
JONES1 HOSP3 CARDIO1 VASSURG1
...but if no specialty record existed for Dr. Jones, only the two
relevant hospitals would be represented...
DoctorID HospitalID Specialty DepartmentID
JONES1 HOSP1 null CARVASSUR1
JONES1 HOSP3 null VASSURG1
What would be the simplest way to accomplish this result?how about:
SELECT *
FROM Hospitals as h
LEFT JOIN Departments as d on d.HospitalID = h.HospitalID
LEFT JOIN Specialties as s on s.DoctorID = d.DoctorID
WHERE NOT (s.DoctorID is null AND d.DoctorID is null)|||That would work *except* that where there is no specialty, I only want
a record for each hospital that has a related department record.|||so when you run my example, what output is it that's wrong, could you
give an example input, output, and desired output.|||Well, after discussing it with the client further, it appears that they
*would* like to see a record for each hospital, so your suggestion will
work for me after all. Thank you very much for your time.|||Glad it's all sorted, but I'm still pretty sure that the result will
actually show as you originally specified. You see if there's no
speciality, and no dept in the hospital, both these clauses will be
null, so it will be filtered by that where clause.
Anyway, as long as it's working, it's - just check that example
before you use it.
Cheers
Will

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

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'IsUse
rTable')
= 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_In
voiced)
VALUES ('00000008', 'ABC', 'ITEM123', '40')
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_In
voiced)
VALUES ('00000008', 'ABC', 'ITEM123', '50')
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_In
voiced)
VALUES ('00000008', 'ABC', 'ITEM123', '60')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
1;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_n
o,
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 Orde
rs]
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_n
o)
> VALUES ('0009', 'ABC', '00000008')
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_n
o)
> VALUES ('0010', 'ABC', '00000008')
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_n
o)
> 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.[Pur
chase
> 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...
is
> N'IsUserTable')
> Qty_Invoiced)
> Qty_Invoiced)
> Qty_Invoiced)
> object_id(N'[dbo].[Vendor
> dbo.[Vendor
>|||"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...
> 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

> Orders]
work,
> is
another
dbo.[Purchase
>|||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...
> Table.
> this
>
>
> (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
>
> work,
it
> another
Orders].vend_no,
> dbo.[Purchase
>|||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...
ETC
to
sure
> it
po_no)
po_no)
po_no)
> Orders].vend_no,
>sql

Joining tables in several ways withing the same query

We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:

> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated

Joining tables in several ways withing the same query

We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated :)
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)

Joining tables in several ways withing the same query

We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"hsifelbmur" wrote:

> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>
|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegr oups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
sql

joining one table with itself

Hello all

Let's say I have 1 table "contract" containing the following data:

id year sales
45 2005 100
45 2004 95
89 2005 250
89 2004 275
12 2005 42

I want to make a table with one unique row for each id and then a column for
2004 sales and 2005 sales, like this:

select a.id, a.sales, b.sales
from contract a, contract b
where a.contract=b.contract(+)
and a.year=2005
and b.year=2004

The rows for id 45 and 89 are shown perfectly. But id 12 is not shown at all
because it doesn't have a record for 2004!! I don't know why 'cause I
outerjoined the tables.

It works perfectly when I have two distinct tables for each year (for
instance contract_2005 and contract_2004). So the problem seems to be in the
fact I like to join one table with itself.

Someone has a solution for this?

thanks!

MaartenThe problem is a logical one; by specifing that you want rows returned
from your result set where a.year =2005 and b.year=2004, you've limited
your return to rows that match BOTH criteria. Essentially, you've
eliminated the NULLS from your outer join.

To get around this, you need to use subqueries; I would also move to a
newer JOIN syntax (it's easier to read):

SELECT a.id, a.sales, b.sales
FROM (SELECT id, sales
FROM contract
WHERE year = 2005) a
LEFT JOIN (SELECT id, sales
FROM contract
WHERE year = 2004) b
ON a.id=b.id

Untested.

HTH,
Stu|||Hi Stu

It works, thanks a lot!

regards,
Maarten

Joining on partial matches

Hi all,
I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) I want to select distinct() and
join on id number to return a recordset containing every individual
listed in both the files HOWEVER, in some cases an incomplete ID
number has been collected into one of the 2 files -is there a way to
join on partial matches not just identical records in the same way as
you can select where LIKE '%blah, blah%'??
Is hash joining an option i should investigate?

TIA
MarkA join expression can include any predicates, including LIKE:

...
ON A.colx LIKE B.colx+'%'

You may also find the functions CHARINDEX and PATINDEX useful (see BOL).

--
David Portas
----
Please reply only to the newsgroup
--

"Mark" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.posting.google.c om...
> Hi all,
> I have 2 files containing Id numbers and surnames (these files
> essentially contain the same data) I want to select distinct() and
> join on id number to return a recordset containing every individual
> listed in both the files HOWEVER, in some cases an incomplete ID
> number has been collected into one of the 2 files -is there a way to
> join on partial matches not just identical records in the same way as
> you can select where LIKE '%blah, blah%'??
> Is hash joining an option i should investigate?
> TIA
> Mark|||A join expression can include any predicates, including LIKE:

...
ON A.colx LIKE B.colx+'%'

You may also find the functions CHARINDEX and PATINDEX useful (see BOL).

--
David Portas
----
Please reply only to the newsgroup
--

"Mark" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.posting.google.c om...
> Hi all,
> I have 2 files containing Id numbers and surnames (these files
> essentially contain the same data) I want to select distinct() and
> join on id number to return a recordset containing every individual
> listed in both the files HOWEVER, in some cases an incomplete ID
> number has been collected into one of the 2 files -is there a way to
> join on partial matches not just identical records in the same way as
> you can select where LIKE '%blah, blah%'??
> Is hash joining an option i should investigate?
> TIA
> Mark|||>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<

Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning.|||>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<

Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning.

Join, Count and Group By

Hello,

I have two tables containing the following info I need to use in a query...

Table 1
---

repair_no
contactor_code

Table 2
---

repair_no
area_code
log_date

I have a query to list all contractors starting with code 'SC', for a given area and between the dates shown.

SELECT
a.repair_no,
a.contractor_code,
b.area_code,
b.log_date
FROM
contractors a,
repairs b
where
a.repair_no = b.repair_no and
a.contractor_code like 'SC%' and
b.area_code like 'CH%' and
b.log_date >= date('01.02.2003') and b.log_date <= date('01.02.2004')
order by
a.contractor_code

How can I use this to obtain and list the same fields as above, but provide a count of (and possibly group by) all similar contractors?

I can get the count I need with the following. Do I need to run the two queries separately or in some way combine the two?

select
a.contractor_code,
count(a.contractor_code) as contr_count
from
contractors a,
repairs b
where
a.repair_no = b.repair_no and
a.contractor_code like 'SC%' and
b.area_code like 'CH%' and
b.log_date >= date('01.02.2003') and b.log_date <= date('01.02.2004')
group by
a.contractor_code

Thanksyou could combine them with UNION ALL, but it would be kluge (http://www.clueless.com/jargon3.0.0/kluge.html)y

i can whip up an example for you if you really need it

if you are returning the detail rows to an application program, you can simply calculate the counts while printing them, and you wouldn't need the second query at all

however, if you want the totals to precede the details in your listing, as in this example:

contractor SC001 has the following 3 repairs:
b0032 416 2004-02-01
b0077 905 2004-02-03
b0032 416 2004-02-05

contractor SC002 has the following 2 repairs:
b0050 905 2004-02-02
b0066 905 2004-02-04

then you might want to give the union a try, otherwise you will have to do two sets of loops in your code, one to count the rows per contractor, and the second to print them|||Thanks.

I've got something working using the two separate queries, which is acceptable.

However, if you do have an example of a union, and you don't mind, I would be grateful to see it. It will at least give me something to play around with.

Many thanks.|||Group totals and details in one database query (http://r937.com/grouptotals.htm)

that page is an unfinished article (i.e. you cannot find it in the archives, it was never published on my site)

the content (i.e. the sql and coldfusion logic) is fine, i just never figured out how to mark it up with colours that i'm happy with|||A query can obtain its output either from table(s) or from other queries: something that is called a view.

The exact means of doing this depend upon what tool you are using, but you can expect to find it with any fully SQL-compliant tool. (Microsoft Access, for example, doesn't support the concept of "views" but does allow you to include a query as well as a table in a query-designw window; thus, the same result, at least for our purposes here.)

In your example, I'd suggest that you use this approach simply because it's easy to visualize. You see, you've already got a query that does the first part: selecting the base records you want. It's a fairly complicated query and it might be a pervasive one: that is, "something you might wish to use in the same way in lots of different places." If you base subsequent inputs directly upon this query, you'll only have to change this query; not a whole slew of 'em.

Final note: when you combine queries in this way, the query optimizer will consider all of them at once to determine their combined effect, building the execution plan accordingly. It doesn't actually "run them one-at-a-time." So you don't [necessarily] pay a performance penalty in your quest for clarity.

And I prize clarity just about most-of-all.

Join table with a UDF?

Hello all,
I have a UDF that returns a few fields.
The UDF accepts a 'ConnoteId' and returns a table containing 4 fields
including the same 'connoteId'. I want to be able to join this table to the
connote table and be able to pass a connoteId to the UDF.
Essentially something like this:
Select *
From Connote C
Inner Join udf_GetTimeliness(C.kConnoteId) As CT On C.kConnoteId =
CT.kConnoteId
I cant put it into a view as it invloves quite a bit of processing.
Thanks in advance
Regards
IshanIt's possible in SQL Server 2005 , using CROSS APPLY.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
news:5EEB2357-4C6F-4BFB-8565-2095077BE027@.microsoft.com...
Hello all,
I have a UDF that returns a few fields.
The UDF accepts a 'ConnoteId' and returns a table containing 4 fields
including the same 'connoteId'. I want to be able to join this table to the
connote table and be able to pass a connoteId to the UDF.
Essentially something like this:
Select *
From Connote C
Inner Join udf_GetTimeliness(C.kConnoteId) As CT On C.kConnoteId =
CT.kConnoteId
I cant put it into a view as it invloves quite a bit of processing.
Thanks in advance
Regards
Ishan|||Thanks - u made my day!!
"Tom Moreau" wrote:

> It's possible in SQL Server 2005 , using CROSS APPLY.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ishan Bhalla" <IshanBhalla@.discussions.microsoft.com> wrote in message
> news:5EEB2357-4C6F-4BFB-8565-2095077BE027@.microsoft.com...
> Hello all,
> I have a UDF that returns a few fields.
> The UDF accepts a 'ConnoteId' and returns a table containing 4 fields
> including the same 'connoteId'. I want to be able to join this table to th
e
> connote table and be able to pass a connoteId to the UDF.
> Essentially something like this:
> Select *
> From Connote C
> Inner Join udf_GetTimeliness(C.kConnoteId) As CT On C.kConnoteId =
> CT.kConnoteId
> I cant put it into a view as it invloves quite a bit of processing.
> Thanks in advance
> Regards
> Ishan
>