Nested Select, and Table Joint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested Select, and Table Joint

Author

Topic

Tsebi
Starting Member

4 Posts

Posted - 2006-09-14 : 10:34:15

Hi Guys

Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:

OPBal| Receipt| IssTrns| Transfer| ClBal

SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )

WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid

OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc

OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 11:33:40

Yikes! Where does one even begin?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-14 : 11:42:12

quote:
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,

I wonder if this query runs at all !!

Why are using both styles of writing joins (ANSI and Non-ANSI)? Stick to any one of them (better go for ANSI)?

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 11:48:29

I think it is better that you provide us with table definitions, sample data and expected output based on that sample data.

1) You want only 5 columns in the resultset, but you have 7 columns selected in the query
2) Why is ITEM Table involved? No columns are selected from that table, nor is any filter applied
3) DO you have any idea of how this CROSS JOIN does on your WHERE clauses? You have FOUR clauses of which any must be tru to get the row.
4) You are grouping by column not in the select statements.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-14 : 11:56:09

Look at the WHERE clause.

WHERE
		dbo.inventory.location = dbo.matusetrans.storeloc 
		AND dbo.inventory.itemnum = dbo.matrectrans.itemnum
		AND dbo.inventory.siteid = dbo.matrectrans.siteid 

		OR

		dbo.inventory.location = dbo.matrectrans.tostoreloc
		AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
		AND dbo.inventory.siteid = dbo.matusetrans.siteid

		OR

		dbo.inventory.location = dbo.matrectrans.fromstoreloc 

		OR

		dbo.inventory.location = dbo.ist_itemdetail.location
		AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum

Peter Larsson
Helsingborg, Sweden

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-09-14 : 12:27:53

Do people actually write SQL code like this?

Why would you preface every column name with dbo.? I could see in the FROM clause, but other than that it just makes it harder to read.

This person doesn't understand how the OR works, he needs some () in there.

I am curious to what the query plan would look like for this mess.

Live to Throw
Throw to Live

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-14 : 12:50:10

[code]
Welcome to sqlteam.com Tsebi!

As a new member we would appreciate it if you would please take a moment
to fill out the brief questionare:

1) Did you find the reponses to your post...
a) helpful, friendly, and right on the mark
b) the answers didn't really help you out
c) you feel like you've been kicked in doo-dads and
left in a crumpled heap drown in your own vomit.

2) You would tell you co-workers that sqlteam.com...
a) is great place to learn about Sql Server
b) is a good place to go if you weren't able to find the answer elsewhere
c) is a place to be avoided at all costs and that technical terrorism
is the only worthwhile reason to come here again.

3) Which statement do you feel best describes the feeling you were left with:
a) We were all new to sql server at some point, keep up the good work!
b) You've got a long way to go but stick with us
and we'll see that you get there.
c) take the drivel you call sql code, stuff it
up your @$$, and never come back here again!
[/code]

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-09-14 : 12:50:28

Why when I asked for a "Hamburger and Fries or hash-browns" did I only get the hash-browns?

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-14 : 13:02:22

I was just wondering what a Tablee of Joints would hold?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-14 : 13:03:47

quote:
Originally posted by X002548

I was just wondering what a Tablee of Joints would hold?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Did Kristen say hash-brownies?

Be One with the Optimizer
TG

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-09-14 : 15:23:06

quote:

Originally posted by TG


Welcome to sqlteam.com Tsebi!

As a new member we would appreciate it if you would please take a moment 
to fill out the brief questionare:

I agree, TG. None of the prior posts were of the quality I would have expected from the caliber of the people who made them. It's a little disappointing.

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-14 : 15:24:28

Huh?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-09-14 : 15:42:55

Brett,

Tara:

quote:

Why when I asked for a "Hamburger and Fries or hash-browns" did I only get the hash-browns?

Peter:

quote:

Yikes! Where does one even begin?

harsh:

quote:

I wonder if this query runs at all !!

You:

quote:

I was just wondering what a Tablee of Joints would hold?

Which of those answers provided any helpful info to the OP? Which were even effort to provide help? Which were even efforts to get more information in order to be able to help?

I thought they were pretty insulting to the OP (without the OP having earned the scorn by his manner of asking without providing info over and over again).

If you look at TG's post that I quoted before, I'd have wanted the answers to be:


1. a
2. a
3. a

Instead, the OP should be answering:


1. c
2. c
3. c

(Of course, TG then added the ever so helpful:

quote:

Did Kristen say hash-brownies?

which then sorta defeats the point of my agreeing in the first place.

ws5926
Yak Posting Veteran

88 Posts

Posted - 2006-09-14 : 15:54:43

I think that there are a lot of people out there who think forums are there so that they don't have to do research into their problem.

I personally try to not post until I have at least made an effort to solve the problem on my own and have hit Google to see if any web page out there has a solution.

This person is obviously trying to code outside of his abilities and hasn't looked very long at the query that he posted. The fact that he had a table in the FROM clause that wasn't even referenced in the rest of the query proves this.

No one flamed him and he shouldn't be discouraged to post on this forum, maybe he should just do a little more research before he turns for help.

Due to the fact that he hasn't responded to anyone's posts, it seems that he was just looking for someone to do the work for him and not looking into learning how to write proper SQL code.

Live to Throw
Throw to Live

Tsebi
Starting Member

4 Posts

Posted - 2006-09-14 : 15:55:47

Hi guys

like i said am new to sql, and am refering to newbie

mistake with my first posting the output colums will be something like this
1.OPBal| Receipt| IssTrns| Transfer| ClBal|itemnum|location

where OPbal & opbal will be a values from the ist_itemdetail table,
Reciept and Trnsfer values from matrectranstable table when, isstrans a value from matusetrans,
itemnum & location strings from the inventory table.

2.item table is included as it contains all the items numbers
i tried using OUTER JOIN, however i receive the following error :
"Outer join operators cannot be specified in a query containing joined tables".

3. if i use AND instaed of the OR, the output is nothing

4. all the 4 clauses have to be true given that one of the computed columns,share same table name, and colum name, however with different condition

Great Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-14 : 16:27:29

>>KenW: It's a little disappointing.
I noticed you haven't provided anything constructive yet either

Tsebi,

>>I wold appreciate help with optimising the folloing example
Is your question purely about optimization?

Here are two tips right off the bat:
1) column values that are subqueries kill optimization because the subquery must exectue for each row returned. It is much better to join the tables into the main query and perform the SUMs as a set.

2) ORs in the where clause force a tablescan. In other words any indexes that could normally be used won't be used. So if you can avoid them that would be good.
One way that may be possible is to UNION two seperate statments. That way you get the "effect" of the OR but the optimizer can still utilize any existing indexes.

If your question also was because the results you are getting are incorrect then the "hash-brown" point Kristen alluded to is right on the mark. You need to enclose ORed criteria in parenthises to isolate them from the AND criteria.

Be One with the Optimizer
TG

Tsebi
Starting Member

4 Posts

Posted - 2006-09-14 : 16:33:00

Thanks TG

this gives me the idea, will try the various options and will shout out

Tsebi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-14 : 16:44:46

quote:
Originally posted by Tsebi

Thanks TG

this gives me the idea, will try the various options and will shout out

Tsebi

No problem :)

when you do shout out, check out the forum faq for the use of "code" tags. That way you won't loose the formatting.

Also check out the link in Brett's (X002548) posts for the ideal way to get a quick response that will help you.

Good luck!

Be One with the Optimizer
TG

Tsebi
Starting Member

4 Posts

Posted - 2006-09-14 : 16:48:35

sure will do!

Tsebi
Se Nkganago Se Nthola Morwalo

Tsebi
[/quote]
No problem :)

when you do shout out, check out the forum faq for the use of "code" tags. That way you won't loose the formatting.

Also check out the link in Brett's (X002548) posts for the ideal way to get a quick response that will help you.

Good luck!

Be One with the Optimizer
TG
[/quote]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-15 : 01:11:18

KenW,

I think you were too fast with critisism of us trying to help. You didn't mention the four points (posted 09/14/2006 11:48:29) I made to TRY to structure and understand the problem. Also you didn't mention the attempt to visualize the WHERE clause problem with the cartesian product (posted 09/14/2006 11:56:09 ).

I had to start somewhere, right?

Peter Larsson
Helsingborg, Sweden

LazyDragon
Starting Member

30 Posts

Posted - 2006-09-15 : 02:43:16

Too many guys trying to sound like Celko.

LazyDragon
T-SQL Programmer

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources