join or subselect: tables

Showing posts with label tables. Show all posts

Wednesday, March 28, 2012

Jump to different table within the same report

My report has 5 tables that are populated from 5 different datasets and each
table is seperated by a page break so that they get exported to different
worksheets in excel. My question is....is it possible to create a
link/bookmark that can jump from one table to another in the same report and
if it is possible will this functionality export to excel? For
example...when clicking a cell in worksheet1 it will jump to the first value
found in worksheet2. Does that make any sense?Yes, these are called bookmarks. They work in HTML and Excel. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_8j57.asp?frame=true.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
news:D7529257-E5D9-461F-BD99-2E9A2603963C@.microsoft.com...
> My report has 5 tables that are populated from 5 different datasets and
> each
> table is seperated by a page break so that they get exported to different
> worksheets in excel. My question is....is it possible to create a
> link/bookmark that can jump from one table to another in the same report
> and
> if it is possible will this functionality export to excel? For
> example...when clicking a cell in worksheet1 it will jump to the first
> value
> found in worksheet2. Does that make any sense?|||Thanks Brian,
That worked!
"Brian Welcker [MSFT]" wrote:
> Yes, these are called bookmarks. They work in HTML and Excel. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_8j57.asp?frame=true.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
> news:D7529257-E5D9-461F-BD99-2E9A2603963C@.microsoft.com...
> > My report has 5 tables that are populated from 5 different datasets and
> > each
> > table is seperated by a page break so that they get exported to different
> > worksheets in excel. My question is....is it possible to create a
> > link/bookmark that can jump from one table to another in the same report
> > and
> > if it is possible will this functionality export to excel? For
> > example...when clicking a cell in worksheet1 it will jump to the first
> > value
> > found in worksheet2. Does that make any sense?
>
>

Monday, March 26, 2012

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 to Sub-Queries -vs- JOINS to Tables

SQL Server 2000

Howdy All.

Is it going to be faster to join several tables together and then
select what I need from the set or is it more efficient to select only
those columns I need in each of the tables and then join them together
?

The joins are all Integer primary keys and the tables are all about the
same.

I need the fastest most efficient method to extract the data as this
query is one of the most used in the system.

Thanks,

CraigOn 11 Aug 2005 09:24:08 -0700, csomberg@.dwr.com wrote:

>SQL Server 2000
>Howdy All.
>Is it going to be faster to join several tables together and then
>select what I need from the set or is it more efficient to select only
>those columns I need in each of the tables and then join them together
>?
>The joins are all Integer primary keys and the tables are all about the
>same.
>I need the fastest most efficient method to extract the data as this
>query is one of the most used in the system.
>Thanks,
>Craig

Hi Craig,

I'm not sure I understand your question. Are you asking about the
performance difference between queries like these two?

SELECT A.something, B.otherthing
FROM TableA AS A
INNER JOIN TableB AS B
ON A.xxx = B.xxx
WHERE A.yyy = y
AND B.zzz = z

SELECT A.something, B.otherthing
FROM (SELECT xxx, something
FROM TableA
WHERE A.yyy = y) AS A
INNER JOIN (SELECT xxx, otherthing
FROM TableB
WHERE B.zzz = z) AS B
ON A.xxx = B.xxx

My first guess is that there will be no difference. The optimizer is
free to rearrange the query every way it wants, as long as the end
results are the same. They will probably result in the same execution
plan.

On the other hand, it is very hard to predict what the optimizer will
do. It often does a good job, but there still are situations where it
shows that it's just a program.

If you really want to be sure, then why don't you simply test both
against your system?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(csomberg@.dwr.com) writes:
> Is it going to be faster to join several tables together and then
> select what I need from the set or is it more efficient to select only
> those columns I need in each of the tables and then join them together
> ?
> The joins are all Integer primary keys and the tables are all about the
> same.
> I need the fastest most efficient method to extract the data as this
> query is one of the most used in the system.

Your query is open to several interpretations, so the answers you get
may not address your real issue.

If your idea is to first join two tables, get those columns into
a temp table, join that with the next table, then this is generally
not a good idea. Although, when it comes to performance there a few
definitive answers. For a certain query, this could actually be a
good strategy. But as a general approach, it's better to throw in
all tables into one query.

And you should not use SELECT * - only list the columns you actually
need.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I need the fastest most efficient method to extract the data as this query is one of the most used in the system. <<

Then test them. But my guess is that the optimizer will do them the
same way. Putting all the tables in the FROM clause will be much easier
to read and maintain, however.sql

Joins Question

I have 3 tables. Table1(time,readingA) Table2(time,readingB)
table3(time,readingC)
Now the time can be same and it can be different. Now i want to know
how do i join so that i get the data: time,readingA,readingB,readingC
If the time is same then it is fine, but if the time is not same in
two tables for eg : if table1 has a record for time 12:30 and Table2
and table3 does not have that time then it should show data from
table1 and the readingB and readingC will be blank.
I hope my question is clear.
Thanks for helpHi
If you are not interested in the time then you may not want to truncate
everything to midnight when they are inserted (which if you don't have a tim
e
portion on your date/time will happen anyhow). The isssue then is what will
happen if there are multiple records for each day? If the time is require fo
r
some other reason you can also use the convert function to compare the date
part of the datetime
SELECT CONVERT(char(8),T1.time,112) AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
JOIN Table2 T2 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T2.time,112
)
JOIN Table3 T3 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T3.time,112
)
John
"Pradeep" wrote:

> I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help
>|||Hi,
if I understand correctly your question, you need to use left join.
Something like
SELECT T1.time AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2
ON T2.time=T2.time
LEFT JOIN Table3 T3
ON T1.time= T3.time
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502012334.5b8f2955@.posting.google.com...
>I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help|||It looks like I may have got this mixed up! As Ana says use left JOIN
although you may not want your times to 3/100 of a second, in which
case you will still need to truncate them
SELECT T1.Time, T1.readingA, T2=AD.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.time =3D T2.time
LEFT JOIN Table3 T2 ON T1.time =3D T3.time
John

Joins on UPDATE

I need some help understanding what's happening on a join when updating
tables. Assume 2 tables with identical structures:

UPDATE
Table1
SET
Field1 = Table2.Field1,
Field2 = Table2.Field2
FROM
Table2
WHERE
Table1.Field3 = Table2.Field3
AND Table1.Field4 = Table2.Field4

Indexes exist on Field3 and Field4 on both tables. So why does SQL
Server choose a hash join?

Thanks in advance.(andrewbb@.gmail.com) writes:
> I need some help understanding what's happening on a join when updating
> tables. Assume 2 tables with identical structures:
> UPDATE
> Table1
> SET
> Field1 = Table2.Field1,
> Field2 = Table2.Field2
> FROM
> Table2
> WHERE
> Table1.Field3 = Table2.Field3
> AND Table1.Field4 = Table2.Field4
>
> Indexes exist on Field3 and Field4 on both tables. So why does SQL
> Server choose a hash join?

Are those indexes on (Field3, Field4) or indexes on the individual
fields?

In any case, one of the tables will have to be scanned. Say that would
be Table2. Now for each row, we should look for a matching row in Table1.
Now, assume that only a few rows match. In this case, using a nested loop
and look up the row in Table1 is a good idea.

But what if all rows match? In this case, the pages in Table1 would be
accessed many times, and that would be expensive. Better then to scan
Table1 once. If there is a clustered index on (Field3, Field4), SQL
Server should be able to do a merge join, and scan both tables in
parallel. But if the index is non-clustered, then it's not of much
use, so instead SQL Server builds the hash table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Fields 3 and 4 are individual indexes (not clustered) and the unique
key for the record.

There is exactly a one to one relationship between the two tables, so
how should I structure this to update quickly?|||(andrewbb@.gmail.com) writes:
> Fields 3 and 4 are individual indexes (not clustered) and the unique
> key for the record.
> There is exactly a one to one relationship between the two tables, so
> how should I structure this to update quickly?

You should have a clustered index on (field3, field4).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Joins on 3 tables

Hi All,

I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables

Main table

Label table

textbox table

The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

I did infact manage to perform a join on these these tables.

Select * From tb_Main

inner join tb_Label

on tb_Main.pk = tb_Label.main_fk

where moduleID = @.moduleID

Select * From tb_Main

inner join tb_textbox

on tb_Main.pk = tb_textbox.main_fk

where moduleID = @.moduleID

The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.

Is what im asking possible? I would appreciate if some exmaples are posted

I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!

Thanks in advance

Matt

Hai,

You can try this query, hope this will work.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

*

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.ModuleID

Regards,

Kiran.Y

|||

Thanks Y.Kiran for your suggestion.

I had already tried that out, it returns no rows.

Regards,

Matt

|||

Hai,

I didn't change any thing in the given query. Now I'm giving you the table definitions of all tables, Main, Label, TextBox. Check this one.

-- Create Main, Label, TextBox Tables.

CREATE TABLE tb_Main

( PK int,

Moduleid int,

ItemName varchar(50),

itemtype int,

CONSTRAINT PK_tb_Main_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

CREATE TABLE tb_Label

(

PK int,

Main_fk int CONSTRAINT FK_tb_Main_Main_fk REFERENCES tb_Main(PK),

labeltext varchar(50),

CONSTRAINT PK_tb_Label_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

CREATE TABLE tb_TextBox

(

PK int,

Main_fk int CONSTRAINT FK_tb_TextBox_Main_fk REFERENCES tb_Main(pk),

TextBoxText varchar(50),

CONSTRAINT PK_tb_TextBox_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

-- Insert data into Main, Label, TextBox tables.

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(36,372,'test1',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(37,372,'test2',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(38,372,'test3',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(39,372,'test4',6)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(40,372,'test5',4)

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(4,36,'labeltext1')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(5,37,'labeltext2')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(6,38,'labeltext3')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(7,40,'labeltext4')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(8,39,'labeltext5')

INSERT INTO tb_textbox(pk, Main_fk, textboxtext) VALUES(1,39,'textbox1')

-- Get the records based on the @.ModuleID variable.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.moduleID

Let me know, If I did any wrong.

Regards,

Kiran.Y

|||

You should be able to run the following statement to get what you need.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
INNER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
INNER_JOIN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

The problem that I see is in your data. If this is a true representation of you data then an INNER JOIN is not going to return anything because when you join to the third table the only match you will find is 39, which is not in the second table, hence you get no results. If you were to do a LEFT OUTER JOIN, you could get all of the results with NULL values also represented. Try the query below to attempt to get results.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
LEFT OUTER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
LEFT OUTER_JOiN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

Results:

pk ModuleID ItemName ItemType LabelText TextBox
-- -- -- -- -
36 372 test1 4 labeltext1 NULL
37 372 test2 4 labeltext2 NULL
38 372 test3 4 labeltext3 NULL
39 372 test4 6 NULL textbox1
40 372 test5 4 labeltext4 NULL

Hope this helps. Anyone feel free to correct if there are any inaccuracies. I'm relatively new to SQL Server.

|||

No Data was returned from the query that Kumar provided, BECAUSE there is NO common data between all three tables.

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

There is NO [Mail_fk] for 39 in the table [Label], therefore no matching link between [Textbox], [Label] and [Main]

You 'could' use LEFT JOIN in both of the joins to have a resultset that includes ALL rows from [MAIN] even if there is NO matching links in the other tables.

|||

DBaker,

Excellent explanation and corrected query!

|||

Thanks guys for your help it worked great !

Matt

Friday, March 23, 2012

Joins

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

Joins

joins

Hi Friends
I need ur advise on this one.
when writing sql statements and whenever there is need to join 2 or more tables am used to the following syntax (using WHERE to join tables)

select a.name,b.code from table a,table b where a.code=b.code

as you all know we can also use INNER JOINS,OUTER JOINS etc., to join tables and all BOL help example contains this type of syntax.

my question is , am having difficulty using/writing this syntax in my queries .meaning uysing left joins,inner joins in the same queries.I appreciate if u guys can direct me to some good articles or that sort of thing to improve my understanding of this type of syntax.
Thanks

Hi,

IMHO, the information you need is in BOL. As you have mentioned, it contains samples perjorming JOIN querries. All you have to do is experiment with it to get the idea behind it. Use Query Analyzer to achieve this.

cheers,

Paul June A. Domag

|||

See if these help:

--inner join
select a.name,b.code
from table a inner join table b on a.code=b.code

--left join
select a.name,b.code
from table a left join table b on a.code=b.code

--right join
select a.name,b.code
from table a right join table b on a.code=b.code

--full join
select a.name,b.code
from table a full join table b on a.code=b.code

--cross join
select a.name,b.code
from table a cross join table b

|||See if this helps:
http://www.firstsql.com/tutor.htm and especially this http://www.firstsql.com/tutor3.htm#join
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)|||Thanks Frank.That helps mate Smile

|||

Hi Paul

Seems like you are very helpful.........

Please assist.

i have several document groups in a table which data i want to export. my statement is: select * from docs_indexed where di_doc_group = 71. this is working and i get the correct results. problem is, i have 350 doc_groups. how do i create a query where i can get results for all the doc_groups in one query?

Regards

Hannes

Wednesday, March 28, 2012

Jump to different table within the same report

Monday, March 26, 2012

Joins??

JOINS to Sub-Queries -vs- JOINS to Tables

Joins Question

Joins on UPDATE

Joins on 3 tables

Friday, March 23, 2012

Joins

Joins

joins

join or subselect

Blog Archive

About Me