join or subselect: null

Showing posts with label null. Show all posts

Monday, March 26, 2012

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

with the following content:

ID = 1
OutputPayload:

<ReportDocument>
<ALMSimulationResult>
<selectedModelAssets>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01" description="Global Bond" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0" initialCostUpFront="0.02" regularCostUpFront="0.015" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF00FFD4" stochasticDuration="5">
<models>
<model ID="0e70216f-48ce-4f6c-b2d6-519a5cdfd246" type="corporate grade bond" description="Eurozone Corporate Bond Intermediate (D=5Years)" weight="1" />
</models>
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec" description="America Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0.005" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF3B00FF" stochasticDuration="13.55">
<models>
<model ID="b0817f64-5090-48a3-b58c-aa8f6e5bbdc1" type="equity" description="US Value Style (Eur)" weight="0.9" />
<model ID="677e8aae-7b32-4dc3-88c5-e9302dddad8f" type="conventional bond" description="Euro Cash (TBill)" weight="0.1" />
</models>
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" description="Europa Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0.001" annualPerformanceFee="0.001" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF9D00FF" stochasticDuration="17.5">
<models>
<model ID="86fedd24-2a92-422c-b733-17c60105ff81" type="equity" description="Asia Value Style (Eur)" weight="0.1" />
<model ID="55425529-8adc-47d8-a36d-8cfd9da34880" type="conventional bond" description="Italian Long Term Gov Bond" weight="0.1" />
<model ID="fea29db9-cf0e-4802-bcbe-e2b8d367f0ca" type="cash" description="Euro Cash (Euribor 1m)" weight="0.1" />
<model ID="8e58d785-5fc5-4ede-8ec8-eb1af8e62541" type="equity" description="Eurozone Value Style" weight="0.7" />
</models>
</modelAsset>
</selectedModelAssets>
<savingModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<surplusModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<modelAssetTimeSeries>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.027353" />
<tValues t="2" value="0" annualYield="0.027288" />
<tValues t="3" value="0" annualYield="0.027237" />
<tValues t="4" value="0" annualYield="0.027274" />
<tValues t="5" value="0" annualYield="0.027262" />
<tValues t="6" value="0" annualYield="0.02722" />
<tValues t="7" value="1453" annualYield="0.027258" />
<tValues t="8" value="1457" annualYield="0.027258" />
<tValues t="9" value="1460" annualYield="0.027219" />
<tValues t="10" value="1463" annualYield="0.027259" />
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.065466" />
<tValues t="2" value="0" annualYield="0.063841" />
<tValues t="3" value="0" annualYield="0.063707" />
<tValues t="4" value="0" annualYield="0.063692" />
<tValues t="5" value="0" annualYield="0.062438" />
<tValues t="6" value="0" annualYield="0.064081" />
<tValues t="7" value="0" annualYield="0.063476" />
<tValues t="8" value="0" annualYield="0.064294" />
<tValues t="9" value="0" annualYield="0.062034" />
<tValues t="10" value="0" annualYield="0.065144" />
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978">
<tValues t="0" value="830" annualYield="0" />
<tValues t="1" value="1641" annualYield="0.06504" />
<tValues t="2" value="2456" annualYield="0.063229" />
<tValues t="3" value="3278" annualYield="0.062939" />
<tValues t="4" value="4104" annualYield="0.062825" />
<tValues t="5" value="4935" annualYield="0.061233" />
<tValues t="6" value="5772" annualYield="0.063522" />
<tValues t="7" value="5155" annualYield="0.062448" />
<tValues t="8" value="5994" annualYield="0.063548" />
<tValues t="9" value="6837" annualYield="0.061053" />
<tValues t="10" value="7688" annualYield="0.06525" />
</modelAsset>
</modelAssetTimeSeries>
</ALMSimulationResult>
</ReportDocument>

When I run the following command from SQL server:

SELECT
N.ma.value('@.ID', 'uniqueidentifier') as ModelAssetID,
N.ma.value('@.description', 'nvarchar(255)') as ModelAssetDescription,
N.ma.value('@.minimumLimit', 'float') as ModelAssetMinLimit,
N.ma.value('@.maximumLimit', 'float') as ModelAssetMaxLimit,
N.ma.value('@.stochasticDuration', 'float') as ModelAssetDuration,
N.ma.value('@.color', 'char(9)') as Color,
N1.ma1.value('tValues[1]/@.value', 'float') as ActualAssetMix
FROM ALMPayloads A1 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)
LEFT JOIN ALMPayloads A2 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)
ON N.ma.value('@.ID', 'uniqueidentifier') = N1.ma1.value('@.ID', 'uniqueidentifier')
WHERE A1.ID = 1000
ORDER BY ModelAssetDuration

I get the following result:

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 96803
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Instead of

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

Are you sure there is not other data in the table? I notice that you are doing a self join on the the table [ALMPayloads] but I don't see any predicate on the A2 table alias. I suspect that this could be the reason why you are seeing the additional rows in your results if in fact there are other rows in that table.|||Hi John,
try to add another record (the same xml content with 2 different IDs) and the result will be duplicated.

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

ALMPayloads A1
CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)

sql

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

Monday, March 19, 2012

Joining on NULLS

I have a stored proc that matches 2 tables on a datetime column. I am
worried that matching on a NULL column is not a good idea. Should I consider
matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a better idea?
Thanks.
DavidI believe outer joins should take care of this.
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23GBhm7xCGHA.3812@.TK2MSFTNGP15.phx.gbl...
>I have a stored proc that matches 2 tables on a datetime column. I am
>worried that matching on a NULL column is not a good idea. Should I
>consider matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a
>better idea? Thanks.
> David
>|||> worried that matching on a NULL column is not a good idea. Should I
> consider matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a
> better idea?
Does this mean that you intended to match NULLs (i.e., you consider NULL =
NULL to be true)? If so, then your logic is dependent on the current
ANSI_NULLS setting. There is a section in BOL (sigh - as there usually is)
that discusses this particular issue - Accessing and Changing Relational
Data / Query Fundamentals / Filtering Rows with WHERE and HAVING / NULL
Comparison Search Conditions.
As for whether this (or any other approach) is "better" depends on many
factors. A connection setting dependency is generally not recommended.
Your "better" approach is dependent on knowledge of the domain of the
columns - is this dependency any "better" than the connection setting? I'll
ignore the use of the implicit conversion, something that can easily trip an
unsuspecting reader.
The short answer is that it IS better to use a more defensive approach to
coding. However, this particular case will also involve the identification
of a technique that yields the best performance for your given situation.
Performance is often driven as much by the batch characteristics (e.g., use
of parameters, plan usage) as it is by the query and schema. Logically, you
should use something like:
where (col1 = col2) or (col1 is null and col2 is null)
Isnull and coalesce can be used - as you indicated. There might also be
other ways of looking at the data that would lead you to a different
approach. If you are attempting to equate NULLs, perhaps this is an
indication of a flaw in the data model. If so, the "better" approach is to
find and fix this model flaw. A better model generally improves the system
as a whole, often by orders of magnitude.|||not sure what you mean:
"matching on a null column" to me means "null = null" - if that's your
meaning, then isnull(datecolumn1,0)=isnull(datecolumn2
,0) is the same thing.
yes - joining on nulls is a bad idea -- if the other join criteria (if
any) isn't selective enough, then you'll get a cartesian product for
these (x nulls in table1 * x nulls in table2).
why would you want to match them?
David Chase wrote:
> I have a stored proc that matches 2 tables on a datetime column. I am
> worried that matching on a NULL column is not a good idea. Should I consid
er
> matching on a ISNULL(datecolumn1,0) = ISNULL(datecolumn2,0) a better idea?
> Thanks.
> David
>|||clarification: by "null = null" i mean that these would "match", not
that you would use "where null = null".
"null = null" evaluates to null unless SET ANSI_NULLS is OFF.
therefore, these would not be included in the results, whereas
isnull(datecolumn1,0)=isnull(datecolumn2
,0) would be included in the
results regardless of ANSI_NULLS setting.
but the end result in the narrative is the same - consider nulls a match
and return them in the result set.
Trey Walpole wrote:
> not sure what you mean:
> "matching on a null column" to me means "null = null" - if that's your
> meaning, then isnull(datecolumn1,0)=isnull(datecolumn2
,0) is the same
> thing.
> yes - joining on nulls is a bad idea -- if the other join criteria (if
> any) isn't selective enough, then you'll get a cartesian product for
> these (x nulls in table1 * x nulls in table2).
> why would you want to match them?
> David Chase wrote:
>|||> "null = null" evaluates to null unless SET ANSI_NULLS is OFF.
Not for a JOIN operation, though. ANSI_NULLS does not change the meaning of
NULL = NULL for a join,
the unknown will still be false in the end:
USE tempdb
CREATE TABLE t1(c1 datetime, c2 int)
CREATE TABLE t2(c1 datetime, c2 int)
INSERT INTO t1 VALUES(NULL, 1)
INSERT INTO t2 VALUES(NULL, 3)
INSERT INTO t1 VALUES('20050101', 2)
INSERT INTO t2 VALUES('20050101', 4)
INSERT INTO t1 VALUES('20050102', 5)
INSERT INTO t2 VALUES('20050103', 6)
SELECT * FROM t1
SELECT * FROM t2
SET ANSI_NULLS OFF
SELECT *
FROM t1
INNER JOIN t2 ON t1.c1 = t2.c1
SELECT *
FROM t1 ,t2
WHERE t1.c1 = t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:ejF54IzCGHA.2040@.TK2MSFTNGP14.phx.gbl...
> clarification: by "null = null" i mean that these would "match", not that
you would use "where
> null = null".
> "null = null" evaluates to null unless SET ANSI_NULLS is OFF.
> therefore, these would not be included in the results, whereas
> isnull(datecolumn1,0)=isnull(datecolumn2
,0) would be included in the resu
lts regardless of
> ANSI_NULLS setting.
> but the end result in the narrative is the same - consider nulls a match a
nd return them in the
> result set.
>
> Trey Walpole wrote:|||ah yes - thanks for the clarification
Tibor Karaszi wrote:
>
> Not for a JOIN operation, though. ANSI_NULLS does not change the meaning
> of NULL = NULL for a join, the unknown will still be false in the end:
> USE tempdb
> CREATE TABLE t1(c1 datetime, c2 int)
> CREATE TABLE t2(c1 datetime, c2 int)
> INSERT INTO t1 VALUES(NULL, 1)
> INSERT INTO t2 VALUES(NULL, 3)
> INSERT INTO t1 VALUES('20050101', 2)
> INSERT INTO t2 VALUES('20050101', 4)
> INSERT INTO t1 VALUES('20050102', 5)
> INSERT INTO t2 VALUES('20050103', 6)
> SELECT * FROM t1
> SELECT * FROM t2
> SET ANSI_NULLS OFF
> SELECT *
> FROM t1
> INNER JOIN t2 ON t1.c1 = t2.c1
> SELECT *
> FROM t1 ,t2
> WHERE t1.c1 = t2.c1
>

Monday, March 12, 2012

Joining and Conditional Column Data Help

I have 2 table that I want to join and output a row on a condition that one of the records have a null in the field. Heres what I have.

employee table (empid, name)
tasks table (taskid, empid, taskname, resolution)

If the resolution is null than I want it to be accounted for in each employee record. Heres my query so far that joins the 2 tables and accounts for each employee and counts each task they have. I need another column that counts the tasks.resolution's null values for each employee but cant figure it out. Thanks for any help!

SELECT e.empid,
e.name,
COUNT(t.ID) as 'tcount'
FROM tasks t
RIGHT JOIN employee e ON c.empid = t.empid
GROUP BY e.empid, e.name
order by 'tcount' desc

SELECT

e.empid,

.empname,

COUNT

(t.taskid)as'tcount'

FROM

tasks t

LEFT

JOIN employee eON e.empid= t.empid

WHERE

t.resolutionISNULL

GROUP

BY e.empid, e.empname

Order

by tcountDesc|||

limno's query doesn't quite work the way you'd expect. Because it is being filtered by where the resolution is null from within the WHERE clause, it will eliminate employees that have no records where resolution is null from the output. If you want the employees listed even if they have no tasks, then use this instead:

SELECT empid, empname, (SELECTCOUNT(*)FROM tasksWHERE tasks.empid=employee.empidAND resolutionISNULL)as'tcount'FROM employeeOrder by tcountDesc

|||

Thanks. That helped me put together someting else

selecte.empid, e.ename, count(*) as 'tcount', sum(case when t.resolution isnull and t.empid is not null then 1 else 0 end) as 'NULL resolution'
from employee as e
left join tasks as t on t.empid = e.empid
group by e.empid, e.ename
order by 3 desc

joining 2 tables - outer join

I have the following 2 tables and data:
CREATE TABLE [Applications] (
[Applicant] [char] (20) NULL ,
[PositionID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [positions] (
[PositionID] [int] NULL ,
[JobID] [int] NULL
) ON [PRIMARY]
go
insert positions values(1,25)
insert positions values (2,37)
insert positions values (3,15)
insert positions values (12,15)
insert positions values (18,12)
insert applications values('tom',2)
insert applications values('frank',1)
insert applications values('tom',12)
insert applications values('larry',2)
insert applications values('mary',15)
I want to join the tables to show all the positions and put a star next to
the ones that 'tom' is in. I tried using an outer join just to get the
applicant names to show and expected Nulls in the applicants, something
like:
select Applicant,a.positionID,JobID from positions p left outer join
applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
and wanted:
null 1 25
tom 2 37
null 3 15
tom 12 15
null 18 12
What I got was:
tom 2 37
tom 12 15
I then want to change the name to just show a "*" for name field and get rid
of the null (blank).
1 25
* 2 37
3 15
* 12 15
18 12
Can I do this in one select?
Thanks,
tomTry something like.
select Case Applicant When 'Tom' then '*' else '' end,a.positionID,JobID
from positions p left outer join
applications a on (p.PositionID = a.PositionID)
Ryan
"tshad" wrote:

> I have the following 2 tables and data:
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> I want to join the tables to show all the positions and put a star next to
> the ones that 'tom' is in. I tried using an outer join just to get the
> applicant names to show and expected Nulls in the applicants, something
> like:
> select Applicant,a.positionID,JobID from positions p left outer join
> applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
> and wanted:
> null 1 25
> tom 2 37
> null 3 15
> tom 12 15
> null 18 12
> What I got was:
> tom 2 37
> tom 12 15
> I then want to change the name to just show a "*" for name field and get r
id
> of the null (blank).
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> Can I do this in one select?
> Thanks,
> tom
>
>|||Try,
select
a.applicant,
b.PositionID,
b.JobID
from
Applications as a
inner join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
union all
select
' ',
b.PositionID,
b.JobID
from
Applications as a
right join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
where
a.applicant is null
order by
b.PositionID
go
AMB
"tshad" wrote:

> I have the following 2 tables and data:
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> I want to join the tables to show all the positions and put a star next to
> the ones that 'tom' is in. I tried using an outer join just to get the
> applicant names to show and expected Nulls in the applicants, something
> like:
> select Applicant,a.positionID,JobID from positions p left outer join
> applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
> and wanted:
> null 1 25
> tom 2 37
> null 3 15
> tom 12 15
> null 18 12
> What I got was:
> tom 2 37
> tom 12 15
> I then want to change the name to just show a "*" for name field and get r
id
> of the null (blank).
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> Can I do this in one select?
> Thanks,
> tom
>
>|||"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:14B02A9A-01ED-4025-902D-37DF7F8C980D@.microsoft.com...
> Try something like.
> select Case Applicant When 'Tom' then '*' else '' end,a.positionID,JobID
> from positions p left outer join
> applications a on (p.PositionID = a.PositionID)
Does the job.
Thanks,
Tom
> Ryan
> "tshad" wrote:
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:FAF40495-536F-425B-B4C3-17C68E32BFFB@.microsoft.com...
> Try,
> select
> a.applicant,
> b.PositionID,
> b.JobID
> from
> Applications as a
> inner join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> union all
> select
> ' ',
> b.PositionID,
> b.JobID
> from
> Applications as a
> right join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> where
> a.applicant is null
> order by
> b.PositionID
> go
>
This one does what I was looking for.
Is there performance hit doing these 2 selects versus Ryans which does the
outer join?
Thanks,
Tom
> AMB
>
> "tshad" wrote:
>|||I do not get expected result using Ryan's suggestion.
use northwind
go
CREATE TABLE [Applications] (
[Applicant] [char] (20) NULL ,
[PositionID] [int] NULL
) ON [PRIMARY]
go
CREATE TABLE [positions] (
[PositionID] [int] NULL ,
[JobID] [int] NULL
) ON [PRIMARY]
go
insert positions values(1,25)
insert positions values (2,37)
insert positions values (3,15)
insert positions values (12,15)
insert positions values (18,12)
insert applications values('tom',2)
insert applications values('frank',1)
insert applications values('tom',12)
insert applications values('larry',2)
insert applications values('mary',15)
go
select
'*',
b.PositionID,
b.JobID
from
Applications as a
inner join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
union all
select
'',
b.PositionID,
b.JobID
from
Applications as a
right join
positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
where
a.applicant is null
order by
b.PositionID
-- Ryan's idea
select
case Applicant When 'Tom' then '*' else '' end,
a.positionID,
JobID
from
positions p
left outer join
applications a
on (p.PositionID = a.PositionID)
go
drop table Applications, positions
go
Result:
PositionID JobID
-- -- --
1 25
* 2 37
3 15
* 12 15
18 12
(5 row(s) affected)
positionID JobID
-- -- --
1 25
* 2 37
2 37
NULL 15
* 12 15
NULL 12
(6 row(s) affected)
AMB
"tshad" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:FAF40495-536F-425B-B4C3-17C68E32BFFB@.microsoft.com...
> This one does what I was looking for.
> Is there performance hit doing these 2 selects versus Ryans which does the
> outer join?
> Thanks,
> Tom
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:09F515E4-D541-4215-89A8-BB229205F8C1@.microsoft.com...
>I do not get expected result using Ryan's suggestion.
You're right.
I had to look at it awhile to figure it out. I tried both the right and
left join and they all gave me extra rows, which they should have - and was
not what I was looking for. It was more apparent when I took out the case
statement and saw the Nulls.
The union gave me what I was looking for, which was to give me all the
applications and if 'tom' was there, indicate it.
Thanks,
Tom
> use northwind
> go
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> go
> select
> '*',
> b.PositionID,
> b.JobID
> from
> Applications as a
> inner join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> union all
> select
> '',
> b.PositionID,
> b.JobID
> from
> Applications as a
> right join
> positions as b
> on a.PositionID = b.PositionID and a.applicant = 'tom'
> where
> a.applicant is null
> order by
> b.PositionID
> -- Ryan's idea
> select
> case Applicant When 'Tom' then '*' else '' end,
> a.positionID,
> JobID
> from
> positions p
> left outer join
> applications a
> on (p.PositionID = a.PositionID)
> go
> drop table Applications, positions
> go
>
> Result:
> PositionID JobID
> -- -- --
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> (5 row(s) affected)
> positionID JobID
> -- -- --
> 1 25
> * 2 37
> 2 37
> NULL 15
> * 12 15
> NULL 12
> (6 row(s) affected)
>
> AMB
>
> "tshad" wrote:
>|||I'd seriously hesitate to use joins on these tables since Primary keys are
not defined, and no uniqueness is guaranteed.
--Untested
SELECT
CASE
WHEN
(
SELECT MAX(a.Applicant) --MAX guarantees 1 return, null if no match
FROM Applications a
WHERE a.PositionID = p.PositionID and a.Applicant = 'tom'
) is not null
THEN '*'
ELSE ''
END --Case
p.PositionID,
p.JobID
FROM
Positions p
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eEVI10WBFHA.1388@.TK2MSFTNGP09.phx.gbl...
> I have the following 2 tables and data:
> CREATE TABLE [Applications] (
> [Applicant] [char] (20) NULL ,
> [PositionID] [int] NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [positions] (
> [PositionID] [int] NULL ,
> [JobID] [int] NULL
> ) ON [PRIMARY]
> go
> insert positions values(1,25)
> insert positions values (2,37)
> insert positions values (3,15)
> insert positions values (12,15)
> insert positions values (18,12)
> insert applications values('tom',2)
> insert applications values('frank',1)
> insert applications values('tom',12)
> insert applications values('larry',2)
> insert applications values('mary',15)
> I want to join the tables to show all the positions and put a star next to
> the ones that 'tom' is in. I tried using an outer join just to get the
> applicant names to show and expected Nulls in the applicants, something
> like:
> select Applicant,a.positionID,JobID from positions p left outer join
> applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
> and wanted:
> null 1 25
> tom 2 37
> null 3 15
> tom 12 15
> null 18 12
> What I got was:
> tom 2 37
> tom 12 15
> I then want to change the name to just show a "*" for name field and get
rid
> of the null (blank).
> 1 25
> * 2 37
> 3 15
> * 12 15
> 18 12
> Can I do this in one select?
> Thanks,
> tom
>|||On Fri, 28 Jan 2005 11:10:41 -0800, tshad wrote:
(snip)
>I want to join the tables to show all the positions and put a star next to
>the ones that 'tom' is in. I tried using an outer join just to get the
>applicant names to show and expected Nulls in the applicants, something
>like:
>select Applicant,a.positionID,JobID from positions p left outer join
>applications a on (p.PositionID = a.PositionID) where applicant = 'tom'
(snip)
Hi Tom,
You were nearly there - just move the test on applicant from the WHERE to
the JOIN clause and select PositionID from the positions table instead of
the applications table and you're set.
(snip)
>I then want to change the name to just show a "*" for name field and get ri
d
>of the null (blank).
> 1 25
>* 2 37
> 3 15
>* 12 15
> 18 12
>Can I do this in one select?
Yes. After making the changes indicated above, use a CASE to change 'tom'
to '*' and NULL to ' '. The end result will look like this:
SELECT CASE WHEN a.Applicant IS NULL THEN ' ' ELSE '*' END,
p.PositionID, p.JobID
FROM positions AS p
LEFT OUTER JOIN applications AS a
ON a.PositionID = p.PositionID
AND a.Applicant = 'tom'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q33ov0dnuntdva9u6ujdrv09gee5oub30c@.
4ax.com...
> On Fri, 28 Jan 2005 11:10:41 -0800, tshad wrote:
> (snip)
> (snip)
> Hi Tom,
> You were nearly there - just move the test on applicant from the WHERE to
> the JOIN clause and select PositionID from the positions table instead of
> the applications table and you're set.
> (snip)
> Yes. After making the changes indicated above, use a CASE to change 'tom'
> to '*' and NULL to ' '. The end result will look like this:
> SELECT CASE WHEN a.Applicant IS NULL THEN ' ' ELSE '*' END,
> p.PositionID, p.JobID
> FROM positions AS p
> LEFT OUTER JOIN applications AS a
> ON a.PositionID = p.PositionID
> AND a.Applicant = 'tom'
That does do it also, without the Join. I just want to make sure here. The
above will do the same as:
select '*', b.PositionID,b.JobID
from Applications as a inner join positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
union all
select '',b.PositionID,b.JobID
from Applications as a right join positions as b
on a.PositionID = b.PositionID and a.applicant = 'tom'
where a.applicant is null
order by
b.PositionID
Also, why does it matter whether it "applicant='tom'" is in the Join or the
Where clause?
Is it because the all the records are selected first and then everything
that is not ='tom' gets thrown out (including the outer join rows)?
Thanks,
Tom
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, February 24, 2012

Join Query

Hi,
I have 3 tables as follows (simplified for posting here):
Table1
--
CREATE TABLE [dbo].[Customer] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table2
--
CREATE TABLE [dbo].[Supplier] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[SupplierName] [varchar] (50) NOT NULL
) ON [PRIMARY]
Table3
--
CREATE TABLE [dbo].[PhoneBook] (
[PBID] [int] IDENTITY (1, 1) NOT NULL ,
[PhoneNumber] [varchar] (50) NOT NULL ,
[ContactType] [varchar] (10) NOT NULL ,
[ContactID] [int] NOT NULL
) ON [PRIMARY]
The ContactType column in Table3 (PhoneBook) has 2 possible values (Customer
and Supplier) and the ContactID column will either have CustomerID or
SupplierID stored in it based on the ContactType.
Now I am trying to write a query that will return me all records from the
3rd table (PhoneBook) along with the original names. I have come up with
this:
Query:
--
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
Obviously I'm new to writing queries and I know that there is probably a
much better way to write this query. This query seems inelegant and
probably very poorly written. I am hoping someone can help me come up with
a better queries.
Thanks in advance!
AbhinavHow are enforcing data integrity between the Customer and Supplier
tables and the PhoneBook table? Since you can't use a foreign key, [as
it references 2 separate tables], I assume you have a trigger? The FK is
more solid, btw. [if you don't have anything in the database to do this,
consider the fact that anyone that can add data to the database can add
data to this table that does not have valid referenced values.]
You really should have 2 separate phonebook tables, since you have 2
referenced tables (and therefore are representing 2 distinct phonebook
entities), with FKs.
That said, if you can't/won't change it, you can use a UNION ALL.
Select
P.PBID,
P.PhoneNumber ,
C.CustomerName As ContactName,
P.Contacttype
From
PhoneBook P
Join Customer C On P.ContactID = C.CustomerID
where
P.Contacttype = 'Customer'
UNION ALL
select
p.pbid,
p.phonenumber,
s.suppliername as ContactName,
P.contacttype
from
phonebook p
join Supplier S On P.ContactID = S.SupplierID
where
P.Contacttype = 'Supplier'
[to get one phonebook from the 2 i recommend, you could do the same thing]
A J wrote:
> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (PhoneBook) has 2 possible values (Custom
er
> and Supplier) and the ContactID column will either have CustomerID or
> SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from the
> 3rd table (PhoneBook) along with the original names. I have come up with
> this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably a
> much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up wit
h
> a better queries.
> Thanks in advance!
> Abhinav
>|||Assuming you reject Trey's idea to have a schema that properly enforces
referential integrity. I don't think your sql is too bad. The only thing
that is "inelegant" is the ContactName portion. I think it should just be
something like this:
Select
P.PBID,
P.PhoneNumber ,
IsNull(C.CustomerName, S.SupplierName) As
ContactName,
P.Contacttype
From
PhoneBook P
Left Outer Join Customer C On P.ContactID = C.CustomerID and
P.Contacttype = 'Customer'
Left Outer Join Supplier S On P.ContactID = S.SupplierID and
P.Contacttype = 'Supplier'
In my opinion, this is better than the union all approach.
"A J" wrote:

> Hi,
> I have 3 tables as follows (simplified for posting here):
> Table1
> --
> CREATE TABLE [dbo].[Customer] (
> [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table2
> --
> CREATE TABLE [dbo].[Supplier] (
> [SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
> [SupplierName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
>
> Table3
> --
> CREATE TABLE [dbo].[PhoneBook] (
> [PBID] [int] IDENTITY (1, 1) NOT NULL ,
> [PhoneNumber] [varchar] (50) NOT NULL ,
> [ContactType] [varchar] (10) NOT NULL ,
> [ContactID] [int] NOT NULL
> ) ON [PRIMARY]
>
> The ContactType column in Table3 (PhoneBook) has 2 possible values (Custom
er
> and Supplier) and the ContactID column will either have CustomerID or
> SupplierID stored in it based on the ContactType.
> Now I am trying to write a query that will return me all records from the
> 3rd table (PhoneBook) along with the original names. I have come up with
> this:
> Query:
> --
> Select
> P.PBID,
> P.PhoneNumber ,
> IsNull(C.CustomerName, '') + IsNull(S.SupplierName, '') As
> ContactName,
> P.Contacttype
> From
> PhoneBook P
> Left Outer Join Customer C On P.ContactID = C.CustomerID and
> P.Contacttype = 'Customer'
> Left Outer Join Supplier S On P.ContactID = S.SupplierID and
> P.Contacttype = 'Supplier'
> Obviously I'm new to writing queries and I know that there is probably a
> much better way to write this query. This query seems inelegant and
> probably very poorly written. I am hoping someone can help me come up wit
h
> a better queries.
> Thanks in advance!
> Abhinav
>
>

Monday, February 20, 2012

Join problem

CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Users] VALUES('Unal')
INSERT INTO [dbo].[Users] VALUES('Volkan')
INSERT INTO [dbo].[Users] VALUES('Duygu')
INSERT INTO [dbo].[Users] VALUES('Elif')
INSERT INTO [dbo].[Users] VALUES('Mehmet')
INSERT INTO [dbo].[Users] VALUES('Demir')

CREATE TABLE [dbo].[Agenda](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityName] [nvarchar](50) NULL,
[UserId] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Agenda] VALUES('school excursion','1,3,4')
INSERT INTO [dbo].[Agenda] VALUES('party','6,2,3,1')

Table Name : Users

Id UserName

1 Unal

2 Volkan

3 Duygu

4 Elif

5 Mehmet

6 Demir

Table Name : Agenda

Id ActivityName UserId

1 school excursion 1,3,4

2 party 6,2,3,1

I want to join Users table with Agenda table.

The Result has to be like below:

Id ActivityName UserId UserNames

1 school excursion 1,3,4 Unal, Duygu, Elif

2 party 6,2,3,1 Demir, Volkan, Duygu, Unal

How can I do it in SQL 2005. I don't want use cursor.

if I can't do that join, I have to do other join that it's below.

Id ActivityName UserId UserNames

1 school excursion 1 Unal

1 school excursion 3 Duygu

1 school excursion 4 Elif

2 party 6 Demir

2 party 2 Volkan

2 party 3 Duygu

2 party 1 Unal

thanks so much for help

What you are attempting to accomplish is a form of denormalization. While it is not a 'straightforward' task in T-SQL, here are links to a couple of approaches that may work for you.

Lists -Field Concatenation, One Field to Itself for string
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||

Thanks everbody who help and answer.

join problem

Hi,
I have problem with joining.
DDL:
CREATE TABLE Items (
item_code INTEGER NOT NULL,
item_description VARCHAR(50) NOT NULL,
PRIMARY KEY (item_code)
);
CREATE TABLE VAT_Groups (
vat_group CHAR(1) NOT NULL,
vat_description CHAR(20) NOT NULL,
PRIMARY KEY (vat_group)
);
CREATE TABLE VAT_Percents (
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
vat_percent NUMERIC(5,2) NOT NULL,
PRIMARY KEY (vat_group, start_date),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
CREATE TABLE Items_VAT_History (
item_code INTEGER NOT NULL,
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
PRIMARY KEY (item_code, vat_group, start_date),
FOREIGN KEY (item_code) REFERENCES Items (item_code),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
Sample data:
INSERT INTO Items VALUES (1, 'Vegetables');
INSERT INTO Items VALUES (2, 'Vine');
INSERT INTO Items VALUES (3, 'Milk');
INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
Desired Result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 NULL 8.00
or equally good result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 2004-12-31 8.00
3 2005-01-01 NULL 8.00
Thanks
Srdjan MijatovHi
Try:
SELECT i.item_code,
CONVERT(CHAR(10),CASE WHEN h.start_date >= p.start_date THEN h.start_date
ELSE P.start_date END, 121) AS start_date,
CONVERT(CHAR(10),CASE WHEN ISNULL(h.end_date,'29991231') <=
ISNULL(p.end_date,'29991231') THEN h.end_date ELSE p.end_date END,121) AS
end_date,
p.vat_percent
FROM Items i
JOIN Items_VAT_History h on i.item_code = h.item_code
JOIN VAT_Percents P on h.vat_group = p.vat_group
AND ( ( h.start_date >= p.start_date AND h.start_date <=
ISNULL(p.end_date,'29991231') )
OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
ISNULL(h.end_date,'29991231') >= p.start_date )
OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
ISNULL(p.end_date,'29991231') )
)
ORDER BY i.item_code, start_date
John
"Srdjan Mijatov" wrote:

> Hi,
> I have problem with joining.
>
> DDL:
> CREATE TABLE Items (
> item_code INTEGER NOT NULL,
> item_description VARCHAR(50) NOT NULL,
> PRIMARY KEY (item_code)
> );
> CREATE TABLE VAT_Groups (
> vat_group CHAR(1) NOT NULL,
> vat_description CHAR(20) NOT NULL,
> PRIMARY KEY (vat_group)
> );
> CREATE TABLE VAT_Percents (
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> vat_percent NUMERIC(5,2) NOT NULL,
> PRIMARY KEY (vat_group, start_date),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
> CREATE TABLE Items_VAT_History (
> item_code INTEGER NOT NULL,
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> PRIMARY KEY (item_code, vat_group, start_date),
> FOREIGN KEY (item_code) REFERENCES Items (item_code),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
>
> Sample data:
> INSERT INTO Items VALUES (1, 'Vegetables');
> INSERT INTO Items VALUES (2, 'Vine');
> INSERT INTO Items VALUES (3, 'Milk');
> INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
> INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
> INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
> INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
> INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
> INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
> INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
> INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
> INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
>
> Desired Result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 NULL 8.00
> or equally good result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 2004-12-31 8.00
> 3 2005-01-01 NULL 8.00
>
>
> Thanks
> Srdjan Mijatov
>|||Thank you, its working.
I tried to figure out that complex join condition

> AND ( ( h.start_date >= p.start_date AND h.start_date <=
> ISNULL(p.end_date,'29991231') )
> OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
> ISNULL(h.end_date,'29991231') >= p.start_date )
> OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
> ISNULL(p.end_date,'29991231') )
> )
Then I run query without it and it is working agian.
Srdjan

Join Problem

Hi,

I have 2 tables:

CREATE TABLE [dbo].[TBL_CONDITION](
[CONDITIONID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](500) NULL)

CREATE TABLE [dbo].[TBL_CONDITION_CUSTOMER](
[CCAN] [varchar](10) NOT NULL,
[CONDITIONID] [int] NOT NULL,
[FOLLOW-UP_DATE] [nchar](10) NOT NULL)

Sample data is as follows:

INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of statements')
INSERT INTO [tbl_condition] ([NAME])VALUES('Satisfactory review')
INSERT INTO [tbl_condition] ([NAME])VALUES('Receipt of latest interim')

INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',1,'03/09/2007')
INSERT INTO [tbl_condition_customer] ([CCAN],[CONDITIONID],[FOLLOW-UP_DATE])VALUES('52410',2,'04/09/2007')

Inrespective of data in child table ([tbl_condition_customer] ) I want all the rows from tbl_condition - I am using outer join for that but am not getting the required output:

SELECT dbo.TBL_CONDITION.CONDITIONID, dbo.TBL_CONDITION.NAME, dbo.TBL_CONDITION_CUSTOMER.CCAN
FROM dbo.TBL_CONDITION
LEFT OUTER JOIN dbo.TBL_CONDITION_CUSTOMER ON dbo.TBL_CONDITION.CONDITIONID = dbo.TBL_CONDITION_CUSTOMER.CONDITIONID
WHERE (dbo.TBL_CONDITION_CUSTOMER.CCAN = '52410')

But I am getting output as that of Inner Join?

What am I missing. I want output as follows:

ConditionId Name CCAN
--
1 Receipt of statements 52410
2 Satisfactory review 52410
3 Receipt of latest interim NULL

Hi JayaC

You could change your select statement to the statement shown below.

Chris

|||Thanks Chris. That worked :)

JOIN on fields where ther may be NULL values

I need to extract data from 4 tables. Each of these
tables contains a field called 'page'. In some instances
this field is null (otherwise an integer).
Here's my problem: I need to join these tables on
the 'id' field and 'page', e.g.
SELECT t1.id, t1.name, t1.date, t2.action, t3.country,
t4.region
FROM table1 t1
LEFT OUTER JOIN table2 t2 on t1.id=t2.id AND
t1.page=t2.page
LEFT OUTER JOIN table3 t3 on t1.id=t3.id AND
t1.page=t3.page
LEFT OUTER JOIN table4 t4 on t1.id=t4.id AND
t1.page=t4.page
WHERE t1.category='questions' AND t1.flag='t'
The query runs and returns data; however, for those rows
where 'page' is null, I do not get any data from tables
t2, t3, t4.
What am I doing wrong?
Thanks,
Rafal
Hi Rafal,
Left Outer Join returns all the records from the table on the Left (in your
case t1) and data that is matching the criteria from the table on the right
(in you case T2 or T3 or T4).
If you want records from both the table, irrespective of whether the
condition matches, you need to use Full Outer Join.
When you use Full Outer Join, data from both the tables is returned, if a
match for the criteria is found then the repective coulmn gets the value
else it will return null.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

JOIN on fields where ther may be NULL values

I need to extract data from 4 tables. Each of these
tables contains a field called 'page'. In some instances
this field is null (otherwise an integer).
Here's my problem: I need to join these tables on
the 'id' field and 'page', e.g.
SELECT t1.id, t1.name, t1.date, t2.action, t3.country,
t4.region
FROM table1 t1
LEFT OUTER JOIN table2 t2 on t1.id=t2.id AND
t1.page=t2.page
LEFT OUTER JOIN table3 t3 on t1.id=t3.id AND
t1.page=t3.page
LEFT OUTER JOIN table4 t4 on t1.id=t4.id AND
t1.page=t4.page
WHERE t1.category='questions' AND t1.flag='t'
The query runs and returns data; however, for those rows
where 'page' is null, I do not get any data from tables
t2, t3, t4.
What am I doing wrong?
Thanks,
RafalHi Rafal,
Left Outer Join returns all the records from the table on the Left (in your
case t1) and data that is matching the criteria from the table on the right
(in you case T2 or T3 or T4).
If you want records from both the table, irrespective of whether the
condition matches, you need to use Full Outer Join.
When you use Full Outer Join, data from both the tables is returned, if a
match for the criteria is found then the repective coulmn gets the value
else it will return null.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 26, 2012

Joins with XQuery

Joins with XQuery

Monday, March 19, 2012

Joining on NULLS

Monday, March 12, 2012

Joining and Conditional Column Data Help

joining 2 tables - outer join

Friday, February 24, 2012

Join Query

Join Query

Monday, February 20, 2012

Join problem

Thanks everbody who help and answer.

join problem

Join Problem

JOIN on fields where ther may be NULL values

JOIN on fields where ther may be NULL values

join or subselect

Blog Archive

About Me