Joins??

Hi,
I am stuck with a problem...
I have to query dat from two tables...
PO_hdr and po_addl_cost
now some po's have additional costs and if they do have there will be an entry in po_addl_cost table. They are linked via the PO_GRP_NO.

Now I want to get an extract of data of specific fields..for all po's

I want the extract to show
po_no po_desc po_cost po_addl_costid po_addl_cost_value

The first three fields are from po_hdr and the last two from po_addl_cost

now if there are no entries for that particular po_grp_no i want the two fields blank but still want the other data.

This is my query:
select po.po_no,po.PO_PROJ_NM,po.LOGIN_ID,addl.PO_ADDL_CO ST_TYPE_ID,addl.PO_ADDL_COST_BUY_PRICE from po_hdr po,po_addl_cost_dtl addl
where
po.SITE_ID=41
And po.PO_NO in(287,58)
and po.STATUS_CD=5
and addl.SITE_ID=41
and addl.STATUS = 'A'
and addl.PO_GRP_NO=po.PO_GRP_NO

Pleaseeeeeeeeeeee help!!!select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po
LEFT OUTER JOIN
po_addl_cost_dtl addl ON
addl.PO_GRP_NO=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID=41 AND
addl.STATUS = 'A';|||Thanks , but I am getting an error when trying to execute this using toad ...it gives ORA-00933 sql comman not properly ended ...highlighting "LEFT"|||just a guess but perhaps your version of oracle does not support LEFT OUTER syntax

you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on

(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)|||All sarcasm welcome...
but I amstill having issues...
first of all from what i remember the query with (+) goes like this

select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po ,po_addl_cost_dtl addl where
addl.PO_GRP_NO=po.PO_GRP_NO (+)
AND po.SITE_ID=41
AND po.PO_NO in(287,58) AND po.STATUS_CD=5
AND addl.SITE_ID=41 AND addl.STATUS = 'A';

I have absolutely no idea of joins...but this doesnt seem to retireve two rows...which is what i want.
it gives just one row po_no of which is present in the addl_cost table.|||Originally posted by r937
you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on

(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)
It goes on the "outer" (dark) side:

select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po,
po_addl_cost_dtl addl
where
addl.PO_GRP_NO(+)=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID(+)=41 AND
addl.STATUS (+)= 'A';

But tell me: what is "LEFT" about an outer join? Especially when if written on one line the "outer" table appears on the right... ;o)|||That worked!!! thanks a lot!!!!!|||dunno which one you'd call the outer table, but it's trivial to decide which one's the left table

here, give it a try --

... FROM FOO LEFT OUTER JOIN BAR

now, you've got FOO on the left, and BAR on the right, right?

so, um, FOO is the left table and BAR is the right table

gee i hope i've got that right :cool:

i know it's probably confusing because when i write sql i never put them on the same line, i always write them on separate lines like this --

FROM FOO
LEFT OUTER
JOIN BAR

but that's because i'm an old keyboard jockey, and when i edit text, for example to replace INNER with LEFT OUTER as sometimes is necessary, then i use the arrow keys to position myself on that line, press the Home key if i'm not at the front of the line, and then while pressing the shift key, arrow down to highlight the entire line, and begin typing the replacement text

i don't use a mouse for text editing, and consequently prefer to have stuff on multiple source lines|||Hmm, maybe I've always had it wrong about what the word "outer" really means in this context. I would have called BAR the "outer" table in your example, because in my warped mind you sort of stick the matching rows from BAR on the "outside" of the FOO records...?

But if LEFT OUTER implies that the "outer" table is on the left (i.e FOO), then perhaps the analogy is more with program logic:

-- Outer query
for foo_row in (select * from foo) loop
-- Inner query
begin
select * into bar_row from bar where ...;
exception
when no_data_found then
bar_row := null;
end;
Display(foo_row, bar_row);
end loop;

Presumably there is a RIGHT OUTER that does the opposite?|||yes, RIGHT OUTER is the opposite of LEFT OUTER

did not really understand your code, there is no looping in sql ;)

i would not get into the semantic morass of which one to call the outer table, since in an outer join, one of the tables brings a few extra rows to the table (if you'll pardon the pun), i.e. extra rows which aren't there in the inner join, so these extra rows would be outside the inner rows, and since in a LEFT join they come from the left table, it might make more sense to call the left table the outer table, if you know what i mean

in any case, like i said, i don't call either of them the outer table, i just use the words left and right, because there's no ambiguity there

sample data:

Pets
1 dog
2 cat
3 bird
4 ferret

People
35 curly
38 larry
39 moe

PeoplePets
35 2
35 3
39 1

list all pets, and their people if any (RIGHT join) --

moe dog
curly cat
curly bird
NULL ferret

see this other thread (http://www.dbforums.com/showthread.php?threadid=976339&postid=3597190#post3597190) for LEFT and INNER joins|||Well, my code was supposed to represent what SQL might be doing "under the covers". Or at least, the procedural code you could write to simulate an outer join.

Yes, I agree there is nothing ambigous about LEFT and RIGHT, but then there is nothing particularly meaningful either:

Originally posted by r937
... FROM FOO LEFT OUTER JOIN BAR

now, you've got FOO on the left, and BAR on the right, right?

so, um, FOO is the left table and BAR is the right table

My response to that is:

Originally posted by me
... FROM FOO RIGHT OUTER JOIN BAR

now, you've got FOO on the left, and BAR on the right, right?

so, um, FOO is the left table and BAR is the right table

What's the difference? ;o)

I am sure that the word OUTER must be intended to convey some meaning, but I am no longer so sure what that meaning is...|||i wrote

... FROM FOO LEFT OUTER JOIN BAR

and you suggested

... FROM FOO RIGHT OUTER JOIN BAR

and then asked "What's the difference?"

well, the difference is, the first is a left outer join, and the second is a right outer join

did my people/pets example not help?

lemme know when you want to get into the FULL OUTER JOIN

:cool:|||oh, and by the way, i never write RIGHT OUTER joins anyway

i always re-write them as LEFT OUTER joins

that's because

... FROM FOO RIGHT OUTER JOIN BAR

is exactly equivalent to

... FROM BAR LEFT OUTER JOIN FOO

helps?|||I have absolutely no problem understanding what LEFT, RIGHT and FULL outer joins do, I just don't quite understand why LEFT and RIGHT are so named!

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

Join stuck...

Hi guys,

I'm stuck with this one... I have two tables....a parent and a child table...(parent*-1child).

What I am trying to do is retrieve all the parent rows where the child must contain two different values in one of its columns that is not the primary key.

Ok so Parent table structure:

ParentID

and child table structure:

ChildID (PK)

ParentID

Col3

So return all records from Parent where the Parent can have two different values for Col3. Or even three different values.

Try this query:

SELECT * FROM Parent

WHERE ParentID IN

(SELECT ParentID FROM Child

WHERE Col3 = 'something' OR Col3 = 'something else')

Or

SELECT * FROM Parent INNER JOIN Child ON Parent.ParentID = Child.ParentID

WHERE Child.Col3 = 'something' OR Child.Col3 = 'something else'

I hope this answers your question.

Best regards,

Sami Samir

|||

If I understand you correctly, you want the parent records where there are 2 or more child records with different values. If that is correct, perhaps something like this will help (for SQL 2005):

Code Snippet

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int,
ParentID int,
SomeValue int
)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )
INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 2 )
INSERT INTO @.Child Values ( 5, 2, 3 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 2 )
INSERT INTO @.Child Values ( 7, 5, 1 )
INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT ParentID
FROM @.Parent

EXCEPT

-- Remove singletons
SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) = 1

EXCEPT

-- Remove Two Records, same Parent and Value
SELECT ParentID
FROM @.Child
GROUP BY ParentID, SomeValue
HAVING ( count(1) = 2
AND ParentID NOT IN (SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) > 2
)
)

This 'feels' a bit awkward. Perhaps someone will have a better idea.

|||

Building on Arnie's Test Data. I think the query you want is:

Code Snippet

DECLARE @.Parent table

( ParentID int )

DECLARE @.Child table

( ChildID int,

ParentID int,

SomeValue int

)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )

INSERT INTO @.Parent Values ( 2 )

INSERT INTO @.Parent Values ( 3 )

INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )

INSERT INTO @.Child Values ( 1, 1, 1 )

INSERT INTO @.Child Values ( 2, 1, 2 )

INSERT INTO @.Child Values ( 3, 2, 1 )

INSERT INTO @.Child Values ( 4, 2, 2 )

INSERT INTO @.Child Values ( 5, 2, 3 )

INSERT INTO @.Child Values ( 6, 3, 1 )

INSERT INTO @.Child Values ( 7, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 2 )

INSERT INTO @.Child Values ( 7, 5, 1 )

INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT *

FROM @.PARENT

WHERE ParentID IN (

SELECT ParentID

FROM @.CHILD

GROUP BY ParentID

HAVING (COUNT(DISTINCT SomeValue) >1)

)

The marked inner query returns a list those parents IDs in the child table with more than one distinct value in SomeValue.

|||

Dhericean,

Thanks, that is much better. It was late and my thinking was not working properly. (Stuck in a bad WHILE loop, I think...)

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

--Hmmm...ok let me explain again....

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int, PrimaryKey
ParentID int,
SomeValue int
)

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 1 )
INSERT INTO @.Child Values ( 5, 2, 2 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 3, 1 )
INSERT INTO @.Child Values ( 8, 3, 1 )
INSERT INTO @.Child Values ( 9, 4, 1 )
INSERT INTO @.Child Values ( 10, 4, 1 )
INSERT INTO @.Child Values ( 11, 4, 2 )

INSERT INTO @.Child Values ( 12, 5, 2 )

INSERT INTO @.Child Values ( 13, 5, 2 )

INSERT INTO @.Child Values ( 14, 5, 2 )

--Now what I want to retrieve is all the parents that have BOTH 1 and 2 in its child values. .ie. The parents 1,2 and 4 will be returned but not 3 and 5.

|||

So, I don't see the problem.

This query returns 1,2,4

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

Thanks...but is there another way of doing this. Reason being is that sometimes you only have the value for one child. Eg. Return parents that have only 1 in its child value. That means 1, 2, 3 and 4 will be returned.

Or return parents that have 2 in its child value which will return 1,2,3,4 and 5

The combination can be anything for child and can be one or more differnt child value combinations.

The front end simply allows the user to select a parent value and then select child value/s that belongs to the selected parent. Later on the user can select a different parent value as long as it contains the existing child value/s selected.

Thanks in advanced.

|||

Basically a child can have may parents...so I need a list of all the parents that have the same children passed.

Join query not working

howz yew all
Iam in a middle of a project and iam stuck at reporting ill cut the
details it is so that i have three tables one is
"tbl_Transactions" (containing columns "Transaction_DetailID,
Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
Transaction_Type contains CR for credit and DR for debit.
and other is
"tbl_TransactionDetails" (containing columns "Transaction_ID,
Transaction_Date")
and the third table is
"tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
Account_ParentID, Account_Name, Account_Description)
Now what iam not been able to do is that
i want to select Transaction_ID, Transaction_Date, Account_Name(for
Transaction_Type debit), Account_Name(for Transaction_Type Credit),
one debit and multiple credit entries and vice versa, iam trying to
join all tables but i dont know how to work :s can sum 1 plz help me
thnx in advance
take care
bye
Hi
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
>
select * from tbl_Transactions t join tbl_TransactionDetails td on
t.Transaction_ID=td.Transaction_ID join tbl_Account ta
on ta.Account_ID=t.Account_ID
<umairsyed19@.gmail.com> wrote in message
news:c28a6557-2dfc-4efb-ad9f-c46aea287c12@.v3g2000hsc.googlegroups.com...
> howz yew all
> Iam in a middle of a project and iam stuck at reporting ill cut the
> details it is so that i have three tables one is
> "tbl_Transactions" (containing columns "Transaction_DetailID,
> Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
> Transaction_Type contains CR for credit and DR for debit.
> and other is
> "tbl_TransactionDetails" (containing columns "Transaction_ID,
> Transaction_Date")
> and the third table is
> "tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
> Account_ParentID, Account_Name, Account_Description)
> Now what iam not been able to do is that
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
> thnx in advance
> take care
> bye

Join query not working

howz yew all
Iam in a middle of a project and iam stuck at reporting ill cut the
details it is so that i have three tables one is
"tbl_Transactions" (containing columns "Transaction_DetailID,
Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
Transaction_Type contains CR for credit and DR for debit.
and other is
"tbl_TransactionDetails" (containing columns "Transaction_ID,
Transaction_Date")
and the third table is
"tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
Account_ParentID, Account_Name, Account_Description)
Now what iam not been able to do is that
i want to select Transaction_ID, Transaction_Date, Account_Name(for
Transaction_Type debit), Account_Name(for Transaction_Type Credit),
one debit and multiple credit entries and vice versa, iam trying to
join all tables but i dont know how to work :s can sum 1 plz help me
thnx in advance
take care
byeHi
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
>
select * from tbl_Transactions t join tbl_TransactionDetails td on
t.Transaction_ID=td.Transaction_ID join tbl_Account ta
on ta.Account_ID=t.Account_ID
<umairsyed19@.gmail.com> wrote in message
news:c28a6557-2dfc-4efb-ad9f-c46aea287c12@.v3g2000hsc.googlegroups.com...
> howz yew all
> Iam in a middle of a project and iam stuck at reporting ill cut the
> details it is so that i have three tables one is
> "tbl_Transactions" (containing columns "Transaction_DetailID,
> Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
> Transaction_Type contains CR for credit and DR for debit.
> and other is
> "tbl_TransactionDetails" (containing columns "Transaction_ID,
> Transaction_Date")
> and the third table is
> "tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
> Account_ParentID, Account_Name, Account_Description)
> Now what iam not been able to do is that
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
> thnx in advance
> take care
> bye