join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).

join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).

Join Sequence Priority

Hi,
I have a select statement like this:
SELECT *
FROM
T1
INNER JOIN T2 ON T1.ID=T2.ID
LEFT OUTER JOIN T3 ON T2.ID=T3.ID
The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
JOIN the result with T1.
I tried to use bracket like this:
SELECT *
FROM
T1
(
INNER JOIN T2 ON T1.ID=T2.ID
LEFT OUTER JOIN T3 ON T1.ID=T2.ID
)
But it gives me error. Is there any way that I enforce the sequence of the
join priority?
Any help would be appreciated,
AlanMove the ON belonging to the INNER JOIN to after the complete LEFT OUTER
JOIN:
SELECT *
FROM
T1
INNER JOIN T2
LEFT OUTER JOIN T3 ON T2.ID=T3.ID
ON T1.ID=T2.ID
Jacco Schalkwijk
SQL Server MVP
"A.M-SG" <alanalan@.newsgroup.nospam> wrote in message
news:unMnD0M0FHA.736@.tk2msftngp13.phx.gbl...
> Hi,
>
> I have a select statement like this:
>
> SELECT *
> FROM
> T1
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T2.ID=T3.ID
>
> The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
> JOIN the result with T1.
>
> I tried to use bracket like this:
>
> SELECT *
> FROM
> T1
> (
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T1.ID=T2.ID
> )
> But it gives me error. Is there any way that I enforce the sequence of the
> join priority?
>
> Any help would be appreciated,
> Alan
>|||You'll need to use a subquery to achieve the results you are looking
for. The statement below should get you on your way:
SELECT * FROM T1 INNER JOIN (
SELECT * FROM T2 LEFT OUTER JOIN T3 ON T1.ID=T2.ID) X
ON T1.ID = X.ID
HTH
Jason Strate|||This wont work if T2 and T3 have the same coloum names.
Use:-
SELECT *
FROM
T1
INNER JOIN T2
LEFT OUTER JOIN T3 ON T2.ID = T3.ID
ON T1.ID = T2.ID
The t1->t2 ON clause is after the t2->t3 ON clause and so it is processed
after.
You dont need parenthesis, just move the ON clause.
"j strate" <jason.strate@.digineer.com> wrote in message
news:1129301301.659027.3430@.g43g2000cwa.googlegroups.com...
> You'll need to use a subquery to achieve the results you are looking
> for. The statement below should get you on your way:
> SELECT * FROM T1 INNER JOIN (
> SELECT * FROM T2 LEFT OUTER JOIN T3 ON T1.ID=T2.ID) X
> ON T1.ID = X.ID
> HTH
> Jason Strate
>|||Why?
Either order will give you same result, according to the query posted.
A.M-SG wrote:

>Hi,
>
>I have a select statement like this:
>
>SELECT *
>FROM
>T1
>INNER JOIN T2 ON T1.ID=T2.ID
>LEFT OUTER JOIN T3 ON T2.ID=T3.ID
>
>The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
>JOIN the result with T1.
>
>I tried to use bracket like this:
>
>SELECT *
>FROM
>T1
>(
>INNER JOIN T2 ON T1.ID=T2.ID
>LEFT OUTER JOIN T3 ON T1.ID=T2.ID
> )
>But it gives me error. Is there any way that I enforce the sequence of the
>join priority?
>
>Any help would be appreciated,
>Alan
>
>|||I don't know what the other posters are thinking, because unless you force
the order using hints or FORCEPLAN, the query optimizer ignores the order of
the joins and chooses the best plan based on available indexes, statistics,
etc. Look up SET FORCEPLAN, and OPTION(FORCE ORDER) in Books Online if you
want to coerce the optimizer to use a specific order. In that case, the
suggestions by the other posters may have merit.
"A.M-SG" <alanalan@.newsgroup.nospam> wrote in message
news:unMnD0M0FHA.736@.tk2msftngp13.phx.gbl...
> Hi,
>
> I have a select statement like this:
>
> SELECT *
> FROM
> T1
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T2.ID=T3.ID
>
> The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
> JOIN the result with T1.
>
> I tried to use bracket like this:
>
> SELECT *
> FROM
> T1
> (
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T1.ID=T2.ID
> )
> But it gives me error. Is there any way that I enforce the sequence of the
> join priority?
>
> Any help would be appreciated,
> Alan
>|||On Fri, 14 Oct 2005 13:03:42 -0400, Brian Selzer wrote:

>I don't know what the other posters are thinking, because unless you force
>the order using hints or FORCEPLAN, the query optimizer ignores the order o
f
>the joins and chooses the best plan based on available indexes, statistics,
>etc.
Brian, Trey,
What the other posters were thinking, is that though the evaluation
order of INNER JOINs does not matter, this can change when OUTER JOINs
are involved.
In the give example, both orders of evaluation will produce the same
results. But the OP said "I have a select statement *like* this"
(emphasis is mine). His real statement is probably more complex, and
*will* probably return wrong results if the joins are performed in the
wrong order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 14 Oct 2005 10:38:57 -0400, A.M-SG wrote:

>I tried to use bracket like this:
(snip)
>But it gives me error. Is there any way that I enforce the sequence of the
>join priority?
Hi Alan,
Jacco's answer is correct. But it might be easier to understand if you
return to this query in a year or so if you include some parentheses to
clarify (they are optional in this case):
SELECT *
FROM T1
INNER JOIN (T2 LEFT OUTER JOIN T3
ON T2.ID=T3.ID)
ON T1.ID=T2.ID
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm not sure that's right. The evaluation order is immaterial. Although
I'm not privy to the internals of the relational engine within SQL Server,
in a typical evaluator, an expression is compiled into an execution tree.
For any given node in the tree, the order in which each immediately
subordinate node is executed is immaterial, so long as all are executed
prior to the execution of the given node. The join expression can be
written in several different ways to produce an equivalent execution tree.
While it is true that changing the order of tables in the FROM clause may
require a left join to change to a right join, the expressions are
equivalent with respect to the number and type (inner or outer) of joins,
produce equivalent execution trees, and most important, produce the same
results.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vac0l19i21rv81n5itu6fk0b0brvuu4e9a@.
4ax.com...
> On Fri, 14 Oct 2005 13:03:42 -0400, Brian Selzer wrote:
>
> Brian, Trey,
> What the other posters were thinking, is that though the evaluation
> order of INNER JOINs does not matter, this can change when OUTER JOINs
> are involved.
> In the give example, both orders of evaluation will produce the same
> results. But the OP said "I have a select statement *like* this"
> (emphasis is mine). His real statement is probably more complex, and
> *will* probably return wrong results if the joins are performed in the
> wrong order.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The rules in SQL-92 are that the JOINs are done in left-to-right order,
with the usual exceptions for parens. Your parens are wrong if you
wrote this:
Your parens are wrong if you wrote this:
SELECT *
FROM T1
INNER JOIN
T2
ON T1.id = T2.id
LEFT OUTER JOIN
T3
ON T1.id = T2.id;
It would done as:
SELECT *
FROM (T1
INNER JOIN
T2
ON T1.id = T2.id) -- makes sense
LEFT OUTER JOIN
T3
ON T1.id = T2.id; -- where is T3?
Hey! that is a serious CROSS JOIN problems
Try this:
SELECT *
FROM T1
INNER JOIN
(T2
LEFT OUTER JOIN
T3
ON T2.id = T3.id)
ON T1.id = T2.id);
You also need to research the scoping rules when you use a derived
table name. It is logical and follows the pattern you would expect
from block-structures languages. But you need to think about it.

Join Returns too many rows

Hi

I'm sure this is a real noob question and it may be something I have know the answer to in the past but I can't remember and its been driving me mad for hours. If anyone can tell me how to do this it would make my day!

I have simplified the problem for the purpose of clarity and have attached a sript to create a simple example table.

the table looks like this:

id cMatch cData
1 A A1
2 B B1
3 C C1
4 B B2
5 A A2
6 B B3

I want to be able to do a join on the two table that only returns the following:

t1.cData t2.cData
A1 A2
B1 B2
B1 B3

The Closest I can get is with the following qry:

SELECT t1.cdata, t2.cdata from tmp_Table1 t1
JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch AND t1.cdata<>t2.cdata
WHERE t1.cdata<t2.cdata
ORDER BY t1.cdata, t2.cdata

Which returns:

t1.cData t2.cData
A1 A2
B1 B2
B1 B3
B2 B3Not sure if I attached the script last time so I thought I'd make sure.

thanks in advance for all your help!

Andy|||Hi

while not knowing your specific database, this will work with the example you provided:

SELECT MIN(cdata1), cdata2 FROM
(
SELECT t1.cdata AS cdata1, t2.cdata AS cdata2 FROM tmp_Table1 t1
INNER JOIN tmp_Table1 t2 ON t1.cMatch=t2.cMatch
AND t1.cData <> t2.cData
AND t1.id < t2.id)
AS subtable
GROUP BY cdata2

you may have to change the aggragation function that evaluates the correct value to choose.

join results from multiple EXEC calls

I would like to make inner join with results from different exec %procedureName% calls.

thanks.

insert into #temp
exec Proc1

insert into #temp2
exec Proc2

insert into #temp3
exec Proc3

Then join or union the tables (depending on the data)
If the reultsets are the same you can insert into 1 table instead of multiple tables

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You might also consider rewriting the procedures as functions if it is possible, then the join of the sets would be very natural.

select *
from function (parm) as set1
join function2(parm) as set2
on set1.value = set2.value

join reducing selected data in undesired way

Platform: SQL Server 2000 (8.00.2040, SP4, Enterprise edition)

I've got a complex query I'm trying to build, which will select all requests that have a status_code of 1, and who's related incident has a manager_id of the specified value.

SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
WHERE i.manager_id='value' AND r.status_code = 1

manager_id is a clumn in incident, and status_code is a column in requests

Run this way, the query selects 6 records, which I would expect.

Complication:

I need additional data from 2 more tables. This is intended give me the user who performed the insert action on the incident, and their first and last name.

table actions (a) has:user_id which is FK to users tablerequest_id which is FK to requests table already in queryaction_type which will need to be constrained to a value of 1 when a.request_id=r.request_id
table users (u) has columns with user name (which will replace user_id in display)

When I add these tables to the join in the following manner, my result set goes down to 1 record.

SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
JOIN actions a ON a.request_id=i.request_id
JOIN users u ON a.user_id=u.user_id
WHERE manager_id='value' AND status_code = 1

While I believe I need to be specific that I want the user_id from actions that performed the action_type=1, I don't believe that's what's hindering the operation (I'd have expected to get some duplicate results).

Any thoughts?The join on the actions table or the users table is causing the result set to shrink. Try doing a join on actions and request to see if you get the right number of results.|||try this

SELECT (columns desired)
FROM recipient p
JOIN requests r ON p.recipient_id=r.recipient_id
JOIN incident i ON r.request_id=i.request_id
left outer JOIN actions a ON a.request_id=i.request_id
JOIN users u ON a.user_id=u.user_id
WHERE manager_id='value' AND status_code = 1|||Left join worked, thanks.

Join records of each group

Hello, thanks in advance for your help / comments

I have 2 tables:

SampleInfo contains 2 columns: SampleID & SampleName
Analysis contains 2 columns: SampleID & Elements

I link these 2 tables, get the SampleName & Elements out by the code:

SELECT SampleInfo.SampleName, Analysis.Elements

FROM SampleInfo INNER JOIN Analysis ON (SampleInfo.SampleID = Analysis.SampleID)

It would display

SampleName | Elements
A | a
A | b
A | c
A | f
B | a
B | g
B | l
C | c
C | s
C | o
C | m
C | n

I need to display the report as following:

SampleName | Elements
A | a, b, c, f
B | a, g, l
C | c, s, o, m, n

QUESTION: is it possible? If it is, how should I do this?

FYI, I use CR10 & SQLServer 2000 database

Regards,
tHi,

I found some solution to yor post.

I created a report with excel as datasource.

Grouped the report on Sample Name field.
Created two formula fields to get the results.
1. Elements -- Formula Field
Code for formula filed as follows :
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
2.ResetElementValue -- Formula Field
whileprintingrecords;
shared stringvar Elements;
Elements:="";
-- Place the @.Elements fromula filed in detail section and suppress the section.
-- Place the Group Name in Group Footer section
-- Place @.Elements in the same group Footer section
-- Place the ResetElementValue formula filed in the Group Header section and suppres

Try with the following format and let me know will it fulfills your requirement.

Thanks,
Vidu.
-- Group|||Vidu,
Thanks for your help. It was a great start. I only had to change the Elements (formula field) a little as below. If I don't have the ELSE IF, it will double the last element in each group.

whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else IF Elements <> Right(Elements, length({Sheet1_.Elements}) then
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;

Again, your help is truly appreciated.|||or place the formula in group footer and suppress the details and group header

join question.

Hello, folks
Desc. two tables table A
CriteriaID
1
2
3
Table B
UID ResultID CriteriaID
1 1 1
2 1 2
3 1 3
4 2 1
Want to build a query which gonna show me group by ResultID only records
from table B where B.CriteriaID = {1,2,3} from table APlease don't multi-post.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jonh Smith" <support@.rtsplus.com> wrote in message
news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>|||What do mean by "group By ResultID" If you group by resultID, then you will
get only one record in the ouput for each distinct value of ResultID {1,2}
which means that in the outout SQL needs t obe told what yo put in the other
columns.
We can easily just output the ResultID...
Select ResultID From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a count of original records...
Select ResultID, COunt(*)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
or we can Add a Sum, or Avg of original UID Values...
Select ResultID, Sum(UID), Avg(UID)
From TableB
Where CriteriaID In (Select CriteriaID From TableA)
Group By ResultID
-- --
... But if you want to Groyp BY, you have to tell the query processor what
else you want besides the Group By COlumn...
"Jonh Smith" wrote:

> Hello, folks
> Desc. two tables table A
> CriteriaID
> 1
> 2
> 3
> Table B
> UID ResultID CriteriaID
> 1 1 1
> 2 1 2
> 3 1 3
> 4 2 1
> Want to build a query which gonna show me group by ResultID only records
> from table B where B.CriteriaID = {1,2,3} from table A
>
>
>|||Thank you for your sugestion ! next time i will ..
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%234gars0RFHA.164@.TK2MSFTNGP12.phx.gbl...
> Please don't multi-post.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Jonh Smith" <support@.rtsplus.com> wrote in message
> news:OIz61p0RFHA.3076@.tk2msftngp13.phx.gbl...
>|||Thank you for your response
It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID =
3 where {1,2,3} from table A
It is now make sense ?
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
> What do mean by "group By ResultID" If you group by resultID, then you
> will
> get only one record in the ouput for each distinct value of ResultID {1,2}
> which means that in the outout SQL needs t obe told what yo put in the
> other
> columns.
> We can easily just output the ResultID...
> Select ResultID From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a count of original records...
> Select ResultID, COunt(*)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> or we can Add a Sum, or Avg of original UID Values...
> Select ResultID, Sum(UID), Avg(UID)
> From TableB
> Where CriteriaID In (Select CriteriaID From TableA)
> Group By ResultID
> -- --
> ... But if you want to Groyp BY, you have to tell the query processor what
> else you want besides the Group By COlumn...
> "Jonh Smith" wrote:
>|||Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, all
at the same time...
Do you mean OR instead of of AND ?
But even then, that doesn't answer my question...
Sorry If I'm off-track, but it seems you might have some difficulties with
English.. Do you understand my question about "Group By" ? If Not, get
someone at your site who is a bit more fluent in English to read it and
explain it to you...
"Jonh Smith" wrote:

> Thank you for your response
> It is has to be B.CriteriaID = 1 and B.CriteriaID = 2 and B.CriteriaID
=
> 3 where {1,2,3} from table A
> It is now make sense ?
>
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:DE24A35A-9344-4F69-8E23-F3F838B8C0B6@.microsoft.com...
>
>|||I mean 'AND', .. so this is the point of my problem.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:9182725D-9E48-48D0-9430-F2F0C3D66491@.microsoft.com...
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3,
> all
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||I guess he is talking about relational division.
Relational Division
http://www.dbazine.com/ofinterest/o...br />
division
Example:
select
b.ResultID
from
tableB as b
inner join
tableA as a
on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
group by
b.ResultID
having
count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
and count(distinct a.CriteriaID) = 3;
AMB
"CBretana" wrote:
> Sorry, No, it doesn;t... B.CriteriaID cannot be = 1, AND = 2, AND = 3, al
l
> at the same time...
> Do you mean OR instead of of AND ?
> But even then, that doesn't answer my question...
> Sorry If I'm off-track, but it seems you might have some difficulties with
> English.. Do you understand my question about "Group By" ? If Not, get
> someone at your site who is a bit more fluent in English to read it and
> explain it to you...
> "Jonh Smith" wrote:
>|||Case closed, thank you Alejandro Mesa.
This is solution
SELECT B.ResultID FROM A
JOIN B ON B.CriteriaID = A.CriteriaID
GROUP BY B.ResultID
HAVING (COUNT(B.ResultID) = (SELECT COUNT(CriteriaID ) FROM
A))
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6D45BAB4-B39E-41DF-836E-8E2F50AA596D@.microsoft.com...
>I guess he is talking about relational division.
> Relational Division
> http://www.dbazine.com/ofinterest/o... />
20division
> Example:
> select
> b.ResultID
> from
> tableB as b
> inner join
> tableA as a
> on b.CriteriaID = a.CriteriaID and (a.CriteriaID in (1, 2, 3))
> group by
> b.ResultID
> having
> count(distinct b.CriteriaID) = count(distinct a.CriteriaID)
> and count(distinct a.CriteriaID) = 3;
>
> AMB
>
> "CBretana" wrote:
>|||Alej,
Got it now... Didn't understand...
On Celko's article (yr Link), I was familiar with the problem, but did
not know it was called "Relational Division".
I have in past used the "Not Exists... Where Not Exists..." syntax for
this issue, but I was unfamiliar with the approach you used. Thanks...
In his article, Celko makes the point that the two approaches return
different results when the divisor is empty, The nested Not Exists return
ALL records, and the Having Count(*)... approach returns an empty set...
On a purely academic note, I kinda think the former, nested approach,
which returns all records, is more mathematically "accurate"... Using Joes
example, All the pilots have the skill to fly "every" plane in an empty
hanger ...

JOIN question and NOT JOIN

Hi,

This is a sample database table

TableA

============================

aID int identity(1,1) primary key

aName varchar(30)

TableB

===========================

bID int identity(1,1) primary key

bTitle varchar(30)

aID int references TableA(aID)

TableC

===========================

cID int indentity(1,1) primary key

cCategory varchar(30)

bID int references TableB(bID)

Here I got two query, are them the same?

Select A.aName, B.bTitle, C.cCategory

From TableA A, TableB B, TableC C

Where A.aID = B.aID And B.bID = C.cID

and

Select A.aName, B.bTitle, C.cCategory

From TableA A Join TableB B On A.aID=B.aID

Join TableC On B.bID=C.cID

Are those two the same?

And what is the different of JOIN and LEFT OUTER JOIN? Any other JOIN?

Millions Thanks!

Usually they are the same.

However, the first form is 'old' and will be soon deprecated.

Use the second form.

For more details about JOIN, refer to Books Online, Topics: Using Joins, JOIN

|||

hi ,

If you measure this, you will most likely discover that the two versions
use the exact same access plan. SQL Server tries very hard to optimize a
query, and in that process, a where clause which equates columns from two
tables will be converted to an inner join.

please check this link for the second question.

http://en.wikipedia.org/wiki/Join_(SQL)

hope, it clear

|||Thanks for leading me to the source.|||

The definitive 'source' is Books Online.

Wikipedia is often a good source of information also, but I would trust Books Online more than a wiki -especially if a job or exam was dependent upon the 'answer'.

|||

Books online can sometimes be complicated, but I agree with your answer.

Join question

I was curious as to which format everyone is using for sql joins between tables. At my last couple jobs we used "*=", but wondering how many people are using the "inner join" format. Is one more accepted that the other or is it more personal preference? Personally i find that the *= way is easier to understand but that is most likely due to my past exposure to it.

I also find the =* format much easier to use. Microsoft are trying to force a change to the ANSI format but making the =* format to be specifically enabled with SQL2005.

Join Question

Is it possible to do something like the following:

Select t1.Branch, t2.SCNamefrom WWFE07BoothLeadsAs t1JOIN New_Products.dbo.SupportCentersAs t2ON t1.BranchLike't2.SCName%'

We cannot join the tables like that. If you provide your requirement we can provide the query for that.

|||

Not a clean solution at all, but at least works, if you need it urgently:

Select t1.Branch, t2.SCName
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

If somebody knows a nicer way of doing it, please share :)

|||

Select t1.Branch, t2.SCName
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

Needs to be:
SELECT

t1.Branch,

t2.SCName

FROM WWFE07BoothLeads t1

INNER JOIN New_Products t2 ON t2.t1ID = t1.ID

WHERE t1.ID = @.Id //Or whatever condition you want

Make sense?

|||

WoW!! That works great!! There are only 605 original records but the query produces 608. Not sure why that is.

What I am trying to accomplish is to update the original table (WWFE07BoothLeads ) with a branchId

Select t1.Branch, t2.SCName, t2.SCID
from WWFE07BoothLeads As t1,
New_Products.dbo.SupportCenters As t2
where SUBSTRING(t2.SCName, 1, len(t1.Branch))=t1.Branch

Update WWFE07BoothLeads Set BoothId = ?(1) Where Branch = ?(Atlanta)

|||

Prob a data/query issue. Post your query.

|||

Select t1.Branch, t2.SCNamefrom WWFE07BoothLeadsAs t1
JOIN New_Products.dbo.SupportCentersAs t2
ON t1.BranchLiket2.SCName+'%'

|||

Ok... so my original idea was correct.

Select t1.Branch, t2.SCName, SCID from WWFE07BoothLeads As t1
JOIN New_Products.dbo.SupportCenters As t2
ON t2.SCName Like t1.Branch+'%'

I had to modify it a little bit..

|||

Yes your original query is correct except 't1.Branch%' except specify the value in the completely single quote which is corrected now.

There is scope of getting more records than the actual join as you already mentioned in your message.

JOIN Question

Good Morning,

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

Code Snippet

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

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

CustID OrderID OrderDate

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

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

The following query might help you..

Code Snippet

Create Table #orderdata (

[CustID] Varchar(100) ,

[OrderID] Varchar(100) ,

[OrderDate] datetime

);

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

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

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

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

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

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

Create Table #customer (

[CustID] Varchar(100)

);

Insert Into #customer Values('00001');

Insert Into #customer Values('00002');

Insert Into #customer Values('00003');

Insert Into #customer Values('00004');

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

Code Snippet

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

returns @.dates Table (Date datetime)

as

Begin

While @.Startdate <= @.Enddate

Begin

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

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

End

return;

End

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

Code Snippet

Select CustAndDate.CustID, OrderID, CustAndDate.Date from

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

cross join #customer) as CustAndDate

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

|||

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

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

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

Mani -Excellent presentation of your suggested solution!!

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

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

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

I apologize for my continued confusion here.

Thanks Again
--
Anthony
|||

Anthony,

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

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

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

|||Arnie,

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

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

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

Code Snippet

AND ProductID = 23456

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

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

Thanks again for the help.
--
Anthony

|||

Add

AND ProductID = 23456

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

|||Arnie,

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

Code Snippet

DECLARE @.StartTime datetime
DECLARE @.EndTime datetime

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

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

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

Code Snippet

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

I would add another line that reads

Code Snippet

AND ProdID = 12345

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

Thanks Again for all your help.
--
Anthony

|||

Try something more like this:

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

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

AND ProdID = 12345

)
WHERE ...

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

|||Arnie,

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

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

You could do something like this:

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

AND ( Orders.OrderDate >= CustAndDate.TheDate

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

)

AND ProdID = 12345

)
WHERE ...

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

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

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

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

Join Question

Sort of an esoteric question:

In a transaction with several joins that depend on the prior join statement, does the subsequence join attach to the previous table in its entirety or just the result of it's join?

Example:

INNER JOIN FS_COHeader HDR
ON DTL.COHeaderKey = HDR.COHeaderKey

INNER JOIN Mfg_SHIPDTL SHPDT
ON SHPDT.OMON = HDR.CONUMBER
AND SHPDT._DATESHIP_OwnRec = 11

LEFT OUTER JOIN Mfg_DFSHIP DFSHP
ON DFSHP._SHPLINE_OwnRow = SHPDT._Row

Now will this statement:

LEFT OUTER JOIN Mfg_DFSHIP DFSHP
ON DFSHP._SHPLINE_OwnRow = SHPDT._Row

JOIN with the results of this one:

INNER JOIN Mfg_SHIPDTL SHPDT
ON SHPDT.OMON = HDR.CONUMBER
AND SHPDT._DATESHIP_OwnRec = 11

Or will it JOIN to the entire (previous) table?Use Query Analyser's 'Display Estemated Execution Plan' (ctrl-l) and look for the joins doing table scans for your exact problem. I'd say it should use the subset from the previous join.

Join Question

Hi,
I am trying to do a JOIN on two tables where one table could have more than
one record however I only want the top one. Is there a way to do this in
the JOIN statement or do I have to seperate the JOIN into a sub select
statement?
ThanksWhat do you mean by "top one"? Tables have no inherent order so that phrase
doesn't mean anything on its own. You'll most likely need a self-join or a
subquery to achieve this but it does depend on requirements.
David Portas
SQL Server MVP
--
"Tim" <timmy@.timemail.com> wrote in message
news:OQBAvt6wFHA.2652@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am trying to do a JOIN on two tables where one table could have more
> than one record however I only want the top one. Is there a way to do
> this in the JOIN statement or do I have to seperate the JOIN into a sub
> select statement?
> Thanks
>

JOIN Question

I'm not a real wizard with SQL but am working with it more lately. I've only
recently experimented with JOINS, and am having a problem with the following
.
It says there's an issue "near the JOIN".
Select HoldXref.HoldXrefId, HoldXref.FolderName,
HoldXref.ImageRecId,HoldXref.HoldListId, HoldList.HoldId,
HoldList.HoldType,HoldList.Comment, HoldList.UserName,
HoldTypes.HoldTypeId,HoldTypes.HoldName, HoldTypes.HoldDescription,
HoldTypes.DateBased From HoldXref Where HoldXref.FolderName='Dept_06' And
ImageRecId = 11 INNER JOIN HoldList On HoldXref.HoldListId = HoldList.HoldId
INNER JOIN HoldTypes On HoldList.HoldType = HoldTypes.HoldTypeId
This seems really complicated. When I experimented with it originally, I
didn't have the "where HoldXref.FolderName='Dept_06' and ImageRecId = 11"
It was just a prototype and it worked. However, I want to limit the output
to this criteria and am not sure how to do that. Maybe it's as simple as
putting parenthesis around them, but I've tried several things and it's not
working. And for one reason or another, the stuff I find in the book(s) don'
t
happen to have an example similar to what I'm trying to do.You have your WHERE clause embedded before your JOINs... Please work on your
formatting a bit. It will help you resolve problems like these much more
easily.
SELECT
HoldXref.HoldXrefId,
HoldXref.FolderName,
HoldXref.ImageRecId,
HoldXref.HoldListId,
HoldList.HoldId,
HoldList.HoldType,
HoldList.Comment,
HoldList.UserName,
HoldTypes.HoldTypeId,
HoldTypes.HoldName,
HoldTypes.HoldDescription,
HoldTypes.DateBased
FROM HoldXref
INNER JOIN HoldList On HoldXref.HoldListId = HoldList.HoldId
INNER JOIN HoldTypes On HoldList.HoldType = HoldTypes.HoldTypeId
WHERE
HoldXref.FolderName='Dept_06'
And ImageRecId = 11
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:96A94732-929E-4756-BB3E-E60ABC720D56@.microsoft.com...
> I'm not a real wizard with SQL but am working with it more lately. I've
only
> recently experimented with JOINS, and am having a problem with the
following.
> It says there's an issue "near the JOIN".
> Select HoldXref.HoldXrefId, HoldXref.FolderName,
> HoldXref.ImageRecId,HoldXref.HoldListId, HoldList.HoldId,
> HoldList.HoldType,HoldList.Comment, HoldList.UserName,
> HoldTypes.HoldTypeId,HoldTypes.HoldName, HoldTypes.HoldDescription,
> HoldTypes.DateBased From HoldXref Where HoldXref.FolderName='Dept_06' And
> ImageRecId = 11 INNER JOIN HoldList On HoldXref.HoldListId =
HoldList.HoldId
> INNER JOIN HoldTypes On HoldList.HoldType = HoldTypes.HoldTypeId
>
> This seems really complicated. When I experimented with it originally, I
> didn't have the "where HoldXref.FolderName='Dept_06' and ImageRecId = 11"
> It was just a prototype and it worked. However, I want to limit the output
> to this criteria and am not sure how to do that. Maybe it's as simple as
> putting parenthesis around them, but I've tried several things and it's
not
> working. And for one reason or another, the stuff I find in the book(s)
don't
> happen to have an example similar to what I'm trying to do.|||>> It says there's an issue "near the JOIN".
Why do you have the INNER JOIN clause after the WHERE clause? Did you check
the exact syntax for a SELECT statement in SQL Server Books Online and
looked at the examples given there?
Anith

JOIN question

Hi, I have a question concerning joining three tables together, and
getting the information to spit out correctly. Say I have three
tables:
products
ID Code Name
1 9000 Test Product
categories
ID Name
1 Test Category
2 Another Test
3 Yet another test
productcategories
ID Category Product
1 1 1
2 2 1
3 3 1
Now I want SQL to send back product ID, Code, Product Name, and all
categories it is with. So for example, one row would be:
ID Code Name Categories
1 9000 Test Product Test Category,Another Test,Yet
another test
Here is the SQL I am using. All it is doing is pulling one category
for now. I don't know how to group by and do a concatenation of all
the categories.
SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
(C.ID=PC.Name) GROUP BY P.ID
Any help in this would be appreciated. I am still relatively new to
the complexity of GROUP BYs in SQL.
Thanks for all the help!
JJHi,
I think pure SQL will not be sufficient in this case as far as text
(categories.names) shall be aggregated (concatenated). 'Group by'
returns just one member of the (each) group. If the select list contains
an aggregate function, 'group by' will aggregate over all group
records . So, if a aggregate function e.g. 'concatenateText' would
exist, it could be used here. As far as I know it does not exist.
Therefore a function could be used doing the concatenation.
Then the query would be like:
SELECT P.ID,Code,P.Name, getAllCategories(P.ID) AS AllCategories FROM
products p
The function 'getAllCategories(@.Pid integer)' has to get all Categories
with your query , concatenates the names and returns the concatenated
string.
Hope this helps
Olli
Jimmy Jim schrieb:

> Hi, I have a question concerning joining three tables together, and
> getting the information to spit out correctly. Say I have three
> tables:
> products
> ID Code Name
> 1 9000 Test Product
>
> categories
> ID Name
> 1 Test Category
> 2 Another Test
> 3 Yet another test
> productcategories
> ID Category Product
> 1 1 1
> 2 2 1
> 3 3 1
> Now I want SQL to send back product ID, Code, Product Name, and all
> categories it is with. So for example, one row would be:
> ID Code Name Categories
> 1 9000 Test Product Test Category,Another Test,Yet
> another test
> Here is the SQL I am using. All it is doing is pulling one category
> for now. I don't know how to group by and do a concatenation of all
> the categories.
> SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
> productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
> (C.ID=PC.Name) GROUP BY P.ID
> Any help in this would be appreciated. I am still relatively new to
> the complexity of GROUP BYs in SQL.
> Thanks for all the help!
> JJ|||Thanks for teh reply. Yeah, I tought it was going to be tough with SQL,
and I am using MySQL, so I can't use subqueries like I watned to. I
ended up creating something like a view and using that in PHP/MySQL. Not
as efficient, but all I could do.
Thanks again for the response!
JJ
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Join Question

I am trying to join two tables and one table could have multiple records.
Is there a way to limit to one record on the JOIN statement?
ThanksSELECT TOP 1 t1.col1, t1.col2, t1.colN
FROM
table1 AS t1 JOIN table2 AS t2 ON t1.col1 = t2.col1
"jack" wrote:

> I am trying to join two tables and one table could have multiple records.
> Is there a way to limit to one record on the JOIN statement?
> Thanks
>
>|||On Fri, 14 Oct 2005 13:11:47 -0600, jack wrote:

>I am trying to join two tables and one table could have multiple records.
>Is there a way to limit to one record on the JOIN statement?
>Thanks
>
Hi Jack,
Unfortunately, SQL Server has no "GimmeOneDontCareWhich" function. If
you want one from the group, you'll have to specify which one.
Here's a possible way to do what you want. I'll assume that you join on
col1 and want to join on only the row with the lowest col2.
SELECT ....
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t1.col1 = t2.col1
WHERE t2.col2 = (SELECT MIN(col2)
FROM table2 AS t2b
WHERE t2b.col1 = t2.col1)
BTW, it's easier to reply if you provide some tables and sample data to
work with. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Join Question

Hi
I drop table employee
go
create table employee
( fname char(20),
lname char(36),
dept char(6),
in_dt char(6)
)
insert into employee values ('Joe','Doe','legal','980622')
insert into employee values ('Joe','Doe','legal','990313')
insert into employee values ('Joe','Doe','legal','990704')
insert into employee values ('Joe','Doe','legal','991015')
insert into employee values ('Joe','Doe','legal','000329')
insert into employee values ('Joe','Doe','legal','010503')
drop table work_day
go
create table work_day
(fname char(20),
lname char(36),
dept char(6),
out_dt char(6)
)
insert into work_day values ('Joe','Doe','legal','990228')
insert into work_day values ('Joe','Doe','legal','000617')
insert into work_day values ('Joe','Doe','legal','010407')
select c.fname,
c.lname,
c.dept,
c.in_dt,
e.out_dt
from employee c
left join work_day e
on c.fname = e.fname
and c.lname=e.lname
and c.dept = e.dept
and convert(datetime,c.in_dt) < convert(datetime,e.out_dt)
go
I am getting the multiple records
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 980622
000617
Joe Doe legal 980622
010407
Joe Doe legal 990313
000617
Joe Doe legal 990313
010407
Joe Doe legal 990704
000617
Joe Doe legal 990704
010407
Joe Doe legal 991015
000617
Joe Doe legal 991015
010407
Joe Doe legal 000329
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
and I need the following output
fname lname dept in_dt
out_dt
-- -- -- -- --
--
Joe Doe legal 980622
990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015
000617
Joe Doe legal 000329
010407
Joe Doe legal 010503 NULL
Any Suggestions
AjHello,
Thank you for including DDL, sample data and expected result.
However, there are a few problems:
1. Your DDL does not include primary keys (and other constraints)
2. You use char(6) instead of datetime. That's really bad, for (at
least) two reasons:
- performance: converting the values to datetime prevents SQL Server
from using indexes
- data integrity: in a char(6) you can store a value that is not a
valid date and you won't notice until it's too late
3. The expected result... is not quite what I expected. Either the
provided expected result is be wrong or I am unable to understand what
it should contain. If the expected result would have been this:
fname lname dept in_dt out_dt
-- -- -- -- --
Joe Doe legal 980622 990228
Joe Doe legal 990313 NULL
Joe Doe legal 990704 NULL
Joe Doe legal 991015 NULL
Joe Doe legal 000329 000617
Joe Doe legal 010503 NULL
Then a possible solution is this:
SELECT fname, lname, dept, in_dt, (
SELECT MIN(out_dt)
FROM work_day e
WHERE e.fname=c.fname and e.lname=c.lname
AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
AND NOT EXISTS (
SELECT *
FROM employee d
WHERE d.fname=c.fname and d.lname=c.lname
AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
)
) AS out_dt
FROM employee c
Razvan|||Thank you, I agree with the char date field but that is what the table was
initially created with and I am extracting data from it. Your script gave
me the output I will looking for.
Aj
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116436704.952637.73930@.g49g2000cwa.googlegroups.com...
> Hello,
> Thank you for including DDL, sample data and expected result.
> However, there are a few problems:
> 1. Your DDL does not include primary keys (and other constraints)
> 2. You use char(6) instead of datetime. That's really bad, for (at
> least) two reasons:
> - performance: converting the values to datetime prevents SQL Server
> from using indexes
> - data integrity: in a char(6) you can store a value that is not a
> valid date and you won't notice until it's too late
> 3. The expected result... is not quite what I expected. Either the
> provided expected result is be wrong or I am unable to understand what
> it should contain. If the expected result would have been this:
> fname lname dept in_dt out_dt
> -- -- -- -- --
> Joe Doe legal 980622 990228
> Joe Doe legal 990313 NULL
> Joe Doe legal 990704 NULL
> Joe Doe legal 991015 NULL
> Joe Doe legal 000329 000617
> Joe Doe legal 010503 NULL
> Then a possible solution is this:
> SELECT fname, lname, dept, in_dt, (
> SELECT MIN(out_dt)
> FROM work_day e
> WHERE e.fname=c.fname and e.lname=c.lname
> AND CONVERT(datetime,e.out_dt)>CONVERT(datetime,c.in_dt)
> AND NOT EXISTS (
> SELECT *
> FROM employee d
> WHERE d.fname=c.fname and d.lname=c.lname
> AND CONVERT(datetime,d.in_dt)>CONVERT(datetime,c.in_dt)
> AND CONVERT(datetime,d.in_dt)<CONVERT(datetime,e.out_dt)
> )
> ) AS out_dt
> FROM employee c
> Razvan
>

JOIN question

Hi, I have a question concerning joining three tables together, and
getting the information to spit out correctly. Say I have three
tables:
products
ID Code Name
1 9000 Test Product
categories
ID Name
1 Test Category
2 Another Test
3 Yet another test
productcategories
ID Category Product
1 1 1
2 2 1
3 3 1
Now I want SQL to send back product ID, Code, Product Name, and all
categories it is with. So for example, one row would be:
ID Code Name Categories
1 9000 Test Product Test Category,Another Test,Yet
another test
Here is the SQL I am using. All it is doing is pulling one category
for now. I don't know how to group by and do a concatenation of all
the categories.
SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
(C.ID=PC.Name) GROUP BY P.ID
Any help in this would be appreciated. I am still relatively new to
the complexity of GROUP BYs in SQL.
Thanks for all the help!
JJ
Hi,
I think pure SQL will not be sufficient in this case as far as text
(categories.names) shall be aggregated (concatenated). 'Group by'
returns just one member of the (each) group. If the select list contains
an aggregate function, 'group by' will aggregate over all group
records . So, if a aggregate function e.g. 'concatenateText' would
exist, it could be used here. As far as I know it does not exist.
Therefore a function could be used doing the concatenation.
Then the query would be like:
SELECT P.ID,Code,P.Name, getAllCategories(P.ID) AS AllCategories FROM
products p
The function 'getAllCategories(@.Pid integer)' has to get all Categories
with your query , concatenates the names and returns the concatenated
string.
Hope this helps
Olli
Jimmy Jim schrieb:

> Hi, I have a question concerning joining three tables together, and
> getting the information to spit out correctly. Say I have three
> tables:
> products
> ID Code Name
> 1 9000 Test Product
>
> categories
> ID Name
> 1 Test Category
> 2 Another Test
> 3 Yet another test
> productcategories
> ID Category Product
> 1 1 1
> 2 2 1
> 3 3 1
> Now I want SQL to send back product ID, Code, Product Name, and all
> categories it is with. So for example, one row would be:
> ID Code Name Categories
> 1 9000 Test Product Test Category,Another Test,Yet
> another test
> Here is the SQL I am using. All it is doing is pulling one category
> for now. I don't know how to group by and do a concatenation of all
> the categories.
> SELECT P.ID,Code,P.Name,C.Category FROM products P LEFT JOIN
> productcategories PC ON (P.ID=PC.Product) LEFT JOIN categories C ON
> (C.ID=PC.Name) GROUP BY P.ID
> Any help in this would be appreciated. I am still relatively new to
> the complexity of GROUP BYs in SQL.
> Thanks for all the help!
> JJ
|||Thanks for teh reply. Yeah, I tought it was going to be tough with SQL,
and I am using MySQL, so I can't use subqueries like I watned to. I
ended up creating something like a view and using that in PHP/MySQL. Not
as efficient, but all I could do.
Thanks again for the response!
JJ
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Join question

SQL 7.0
How do I do this in Transact SQL? Is this a join issue?
If the record in B has a flag = 0, then that record
should be deleted from A?
Thx,
Don
Something like this perhaps:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE flag = 0
AND B.key_col = A.key_col)
David Portas
SQL Server MVP
|||Perfect!
Thank you very much,
Don

>--Original Message--
>Something like this perhaps:
>DELETE FROM A
> WHERE EXISTS
> (SELECT *
> FROM B
> WHERE flag = 0
> AND B.key_col = A.key_col)
>--
>David Portas
>SQL Server MVP
>--
>
>.
>

Join question

SQL 7.0
How do I do this in Transact SQL? Is this a join issue?
If the record in B has a flag = 0, then that record
should be deleted from A?
Thx,
DonSomething like this perhaps:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE flag = 0
AND B.key_col = A.key_col)
David Portas
SQL Server MVP
--|||Perfect!
Thank you very much,
Don

>--Original Message--
>Something like this perhaps:
>DELETE FROM A
> WHERE EXISTS
> (SELECT *
> FROM B
> WHERE flag = 0
> AND B.key_col = A.key_col)
>--
>David Portas
>SQL Server MVP
>--
>
>.
>

Join question

SQL 7.0
How do I do this in Transact SQL? Is this a join issue?
If the record in B has a flag = 0, then that record
should be deleted from A?
Thx,
DonSomething like this perhaps:
DELETE FROM A
WHERE EXISTS
(SELECT *
FROM B
WHERE flag = 0
AND B.key_col = A.key_col)
--
David Portas
SQL Server MVP
--|||Perfect!
Thank you very much,
Don
>--Original Message--
>Something like this perhaps:
>DELETE FROM A
> WHERE EXISTS
> (SELECT *
> FROM B
> WHERE flag = 0
> AND B.key_col = A.key_col)
>--
>David Portas
>SQL Server MVP
>--
>
>.
>

Join query with view and inline view produced a different result

Hi:
I've a problem with the following querys, These two query is suppose to
produce a same result
but it is not, i don't know why.
The first query is using view, it procuce a correct result (2 rows),
the second query is using inline view (the inline view defination is
exactly the same as the view) but the result is wrong (4 rows).

>From the execution plan, the second query perform the join with the
inline view twist which is not correct.
Please help.
JCVoon
-- Join with view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
SELECT * From view_PutHold
WHERE CompanyCode='HQ' And BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
-- Join with inline view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
--Here is the DDL
CREATE TABLE [dbo].[WmsPutawayDet] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[Qty] [numeric](18, 0) NOT NULL ,
[Completed] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsPutawayHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsTallyInHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[wmsStockLedger] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL ,
[TxnNo] [varchar] (10) NOT NULL ,
[BaseQuantity] [numeric](18, 0) NOT NULL ,
[PhysicalFlag] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayDet] ADD
CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo],
[ProductCode],
[LocationCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayHed] ADD
CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsTallyInHed] ADD
CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[TallyInNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[wmsStockLedger] ADD
CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[ProductCode],
[LocationCode],
[TallyInNo],
[PrincipalCode],
[TxnNo]
) ON [PRIMARY]
GO
create view view_PutHold as
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
GO
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
I am looking into this issue.
Looks like a problem with SQL server itself.
The query works as expected in SQL 2005 (returns only 2 rows in both cases)
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegro ups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>
|||The problem appears to be in the section
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
If you just comment that and run the query, the result is correct.
Also if you comment the
SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
line, the query gives the correct result.
I am still not sure whether its a known bug. I will update you once I have
more info.
BTW thankls for posting the DDL.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag)
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
--And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
--IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
Roji. P. Thomas
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I am looking into this issue.
> Looks like a problem with SQL server itself.
> The query works as expected in SQL 2005 (returns only 2 rows in both
> cases)
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegro ups.com...
>
|||I observed that commenting the line
will solve the problem.
So here is a workaround, other than using the view.
SELECT PrincipalCode, ProductCode, BaseQty
FROM
(
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
AND (LEFT(Trx.TXNNo,3) = 'OP-'
OR PutHold.Completed = 1)
GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
WHERE BaseQty > 0
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> The problem appears to be in the section
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> If you just comment that and run the query, the result is correct.
> Also if you comment the
> SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
> line, the query gives the correct result.
> I am still not sure whether its a known bug. I will update you once I have
> more info.
> BTW thankls for posting the DDL.
>
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag)
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> --And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> --IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>
|||Roji. P. Thomas:
Thanks for your help.
Comment the HAVING clause will also return 2 rows.
Please update me if u found any thing.
Regards
JCVoon
|||>I observed that commenting the line

>will solve the problem
Read
I observed that commenting the line
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
will solve the problem
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uqT$jHqFGHA.516@.TK2MSFTNGP15.phx.gbl...
>I observed that commenting the line
> will solve the problem.
> So here is a workaround, other than using the view.
> SELECT PrincipalCode, ProductCode, BaseQty
> FROM
> (
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> AND (LEFT(Trx.TXNNo,3) = 'OP-'
> OR PutHold.Completed = 1)
> GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
> WHERE BaseQty > 0
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
>
|||Here is a repro for others looking into the problem.
(SQL Server 2000 SP4)
The query without the last line (HAVING ) returns 3 rows, which is correct.
With HAVING it returns 6 rows and the result is incorrect
Use Pubs
GO
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price * 1) > 0
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegro ups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>
|||Yes. this is a known bug.
http://support.microsoft.com/kb/308458/en-us
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ezkvteqFGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Here is a repro for others looking into the problem.
> (SQL Server 2000 SP4)
> The query without the last line (HAVING ) returns 3 rows, which is
> correct.
> With HAVING it returns 6 rows and the result is incorrect
>
> Use Pubs
> GO
> SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
> FROM Titles T
> LEFT JOIN
> (Select NULL) X (pub_id)
> ON X.pub_id = T.pub_id
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.pub_id, T.type
> HAVING SUM(T.price * 1) > 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegro ups.com...
>
|||Here is the best fix so far.
Just change LEFT(Trx.TXNNo,3) with SUBSTRING(Trx.TXNNo,1,3)
That seems to prevent the otimizer from doing the incorrect cross join.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity*
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (SUBSTRING(Trx.TXNNo,1,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegro ups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>
|||Roji. P. Thomas:
Thank you so much.
I've tested my original query on SQL2000PE with SP4, the problem still
exist.
With your work around my query working fine now.
Just wonder how do u know change LEFT(Trx.TXNNo,3) with
SUBSTRING(Trx.TXNNo,1,3) will solved the problem ?
Thanks
JCVoon