join or subselect: int

Showing posts with label int. 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.

Friday, March 23, 2012

JOINing... but not with the multiple rows thing.

My thread titles need work, I know. :o

Ok, lets say I've got:

tblDocuments
id INT PK
documentName VARCHAR

tblUsers
id INT PK
userName VARCHAR

tblDocumentApprovals
userID INT
documentID INT
approvalDate DATETIME

If I want to get a list of documents, and the users who've signed them off (if any), I'd do something like:

SELECT [tblDocuments].[documentName], [tblUsers].[userName ], [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
LEFT JOIN [tblDocumentApprovals] ON [tblDocumentApprovals].[documentID] = [tblDocuments.id]
INNER JOIN [tblUsers] ON [tblUsers].[id] = [tblDocumentApprovals].[userID]

...which is lovely. Except - I don't want a row returned for each user that's signed it off. I want one row for each document, with a field containing a list of the people who've signed it off.

I know that it's bad design. I was reading an article only yesterday on how they're putting this kind of thing into the latest version of Access, and how it's a bit of a kludge. But it'd really, really help me.

How do you do it?My thread titles need work, I know. :oDon't feel bad - I LOVVVVVED it :D

I was reading an article only yesterday on how they're putting this kind of thing into the latest version of Access, and how it's a bit of a kludge. But it'd really, really help me.Really? Currently you have to bugger about with recordsets in VBA functions.

Anyway - I mostly got involved because of your post title but I suppose I should help really. How about this:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

HTH|||Oh, God - every time I ask something on here I come away with more questions than answers :S :o

<scuttles off to find out what a FUNCTION is and how it differs from a stored proc>

I think that's what I'm after, though. Thanks.|||A (scalar) function is basically a bit of code that takes input, processes it (according to some technical\ busniess requirement) and returns a single value result. Not to be confused with In Line Table and Multi Line Table returning functions.

The idea here is you use the function in the SELECT clause of SQL, passing parameters (often other columns) and display the return.

Check out CREATE FUNCTION in BoL. If you've used other programming languages then it will seem jolly familiar

HTH|||Some principle differences to a sproc (btw) are that it ALWAYS returns a result, must be deterministic (the output can only vary if the input varies so no use of GetDate() etc) and can be used in a query.

HTH|||Ok, assuming you are using SQL Server as the database engine and MS-Access to write and execute the query, then I'd suggest:SELECT [tblDocuments].[documentName], [tblUsers].[userName ]
, [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
WHERE EXISTS (SELECT *
FROM [tblDocumentApprovals]
WHERE [tblDocumentApprovals].[documentID] = [tblDocuments.id])-PatP|||Ok, assuming you are using SQL Server as the database engine and MS-Access to write and execute the query, then I'd suggest:SELECT [tblDocuments].[documentName], [tblUsers].[userName ]
, [tblDocumentApprovals].[approvalDate ]
FROM [tblDocuments]
WHERE EXISTS (SELECT *
FROM [tblDocumentApprovals]
WHERE [tblDocumentApprovals].[documentID] = [tblDocuments.id])-PatPBlimey Pat - from the man who once produced this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63512&whichpage=3
I am frankly disappointed. Any idea how many errors? :(|||Umm.. yeah :S I can't get that one to work, either. Well, I did but it just gives me... gibberish :o

I have to admit... I've gone back to standard table joins with lots of 'duplicate' entries in rows. (Is there a technical name for this sort of thing?) I'm very grateful for your help, pootle_flump - but something about the idea of writing this little function to go and get me the data I needed... it just seemed... a bit inelegant (:D) So I'm resigned to making my application code do the hard work, and keeping SQL queries doing what they do best.|||I also have to admit that my original post wasn't entirely accurate: I wanted to concentrate on the theory of the thing, not the fact that the "documents" in question are, in fact, HTML emails, and that users are involved in both signing off emails and in being on the account management team for them. Sorry if that didn't help.

If it is any use, here is my current stored proc in all its glory. As you can see, I started with this whole headache because of the doubling-up of users as both signer-offers and as account team members: to get the detail of one email that's been signed off by two members of a four-person account team, I'm going to be generating eight rows of largely duplicate data. This seems silly.

SELECT
[tblFiles].[id] AS fileID,
[tblFiles].[client_id],
[tblFiles].[campaign_id],
[tblFiles].[filepath],
[tblFiles].[file_info],
[tblFiles].[date_added],
[tblClients].[clientname],
[tblClients].[email_root_url],
[tblCampaigns].[campaigncode],
[tblCampaigns].[description],
[tblUsers].[id] AS signoffUserID,
[tblUsers].[username] AS signoffUserName,
[tblEmailSignoffs].[signoff_date],
[ClientAccountTeam].[accountTeamMemberId],
[ClientAccountTeam].[accountTeamMemberName]
FROM [tblFiles]
LEFT JOIN [tblEmailSignoffs] ON [tblEmailSignoffs].[file_id] = [tblFiles].[id]
LEFT JOIN [tblUsers] ON [tblEmailSignoffs].[user_id] = [tblUsers].[id]
INNER JOIN [tblClients] ON [tblFiles].[client_id] = [tblClients].[id]
INNER JOIN [tblCampaigns] ON [tblFiles].[campaign_id] = [tblCampaigns].[id]
LEFT JOIN(
SELECT
[tblClients].[id] AS accountID,
[tblAccountTeams].[user_id] AS accountTeamMemberId,
[tblUsers].[username] AS accountTeamMemberName
FROM [tblClients]
INNER JOIN [tblAccountTeams] ON [tblAccountTeams].[client_id] = [tblClients].[id]
INNER JOIN [tblUsers] ON [tblUsers].[id] = [tblAccountTeams].[user_id]
) AS [ClientAccountTeam]
ON [ClientAccountTeam].[accountID] = [tblFiles].[client_id]

WHERE [tblFiles].[content_type]='text/html'
ORDER BY [tblFiles].[id]sql

Monday, March 12, 2012

joining a varchar with a int column

just noticed this, wonder if there is a performance hit
got results when executing the following
select AgendaID
from ObjectiveAgenda
Inner Join Objective On Objective.ObjectiveID =
ObjectiveAgenda.ObjectiveID
problem is that the ObjectiveID column in one table is of SQL type
INTEGER, while in the other table is of type VARCHAR.
wonder if it is doing a translation behind the scenes, with some
performance hit.Yes, SQL Server will have to convert the value and this can cause table scan
.
Thus this can cause major negative impact.
Here is a script I have used for testing. In the Query Analyser, please
'Display Execution Plan' for the 2 queries at the end of my script. One will
cause Table Scan and another will go for Index S

.
set nocount on
create table #test111
(scode int,
sdesc varchar(30))
create index idx1_test111
on #test111(sdesc)
declare @.val1 int
set @.val1 = 1
while @.val1 < 10000
begin
insert into #test111
values(@.val1, convert(varchar(30),(@.val1 * @.val1)))
set @.val1 = @.val1 + 1
end
select * from #test111 where sdesc = 25
go
select * from #test111 where sdesc = '25'
"arzewski@.hotmail.com" wrote:

> just noticed this, wonder if there is a performance hit
> got results when executing the following
> select AgendaID
> from ObjectiveAgenda
> Inner Join Objective On Objective.ObjectiveID =
> ObjectiveAgenda.ObjectiveID
> problem is that the ObjectiveID column in one table is of SQL type
> INTEGER, while in the other table is of type VARCHAR.
> wonder if it is doing a translation behind the scenes, with some
> performance hit.
>

Joining 2 tables...

Hi. I'm new to SQL, and need to join 2 tables... any hints?

table1:
id (int)
title(varchar(50))
body(text)

table2:
id (int)
title(varchar(50))
body(text)

somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information:

table1:
id title body
1 "first title" "first body"
2 "second title" "second body"
3 "third title" "third body"

table2:
id title body
1 "first title" "first body"
2 "second title" "second body"
3 "third title" "third body"

I would like to get...

id table title body
3 1 "third title" "third body"
3 2 "third title" "third body"
2 1 "second title" "second body"
2 2 "second title" "second body"
1 1 "first title" "first body"
1 2 "first title" "first body"

Does anyone know how to get this? I am fairly flexible if i need to change things...

cheers, eh!

SELECT 'A', Id, Title, Body FROM TABLE1
UNION
SELECT 'B', Id, Title, Body FROM TABLE2
ORDER BY Id DESC, 1 ASC

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)

Wednesday, March 7, 2012

join statement required

I have the following tables

tblFavourites
FavouriteSince datetime
UserCodeOwner int (the usercode of the user whose favouritelist this is)
UserCodeFavourite int (the usercode of the user who has been added to the favouritelist of usercodeowner)

EXAMPLE DATA
10/14/2006 7:32:30 PM 4 7
10/16/2006 11:24:01 PM 4 5
10/16/2006 10:55:08 PM 5 4

tblUserData
UserID uniqueidentifier
UserName nvarchar(50)
UserCode int

aspnet_Users
UserID uniqueidentifier
LastActivityDate datetime

Now I need a join statement that selects the fields
aspnet_Users.UserID,aspnet_Users.LastActivityDate,tblUserData.Username,tblUserData.UserCode,tblFavourites.FavouriteSince
for all tblFavourites.UserCodeFavourite where tblFavourites.UserCodeOwner=4

Could someone provide me with the join statement because I dont get it anymore :)

I think I'm a bit confused on your relationships... tblFavourites appears to have two columns that link to user records but its not clear where. Which table and column does UserCodeOwner and UserCodeFavourite link to? Do they link to the UserCode or UserID column of tblUserData or do they link to the aspnet_Users table instead?

Regards,

|||And I think I didnt explain that very well :)
The colums in the tblFavourites table link to the tblUserData.UserCode field.|||SELECT c.UserID,c.LastActivityDate,b.Username,b.UserCode,a.FavouriteSince
FROM tblFavourites a LEFT OUTER JOIN tblUserData b ON a.UserCodeOwner=b.UserCode
LEFT OUTER JOIN aspnet_Users c ON c.UserID=b.UserID
WHERE a.UserCodeOwner=4|||Thanks, but this statement selects the data for the UserCodeOwner (in this case 4).
I need the statement to select the data for all the UserCodeFavourite in which record the UserCodeOwner is 4.

Here's the content of tblUserData
UserID UserCode UserName

8ff3f242-2aab-499c-b0bf-f450969590ad4Jodie23e72b1a-b701-4b6d-9c39-83018dae8db15Jean71e1974f-8f1d-4e12-a0a6-7c8f3be87bc66Marie18ebec6d-adfa-4249-8c46-8daacca724e57Simon

Here's the content of tblFavourites:

FavouriteSince UserCodeOwner UserCodeFavourite

10/14/2006 7:32:30 PM4710/16/2006 10:55:08 PM5410/16/2006 11:24:01 PM4510/24/2006 10:07:46 PM79

And here's the content of aspnet_Users
UserID LastActivityDate
8ff3f242-2aab-499c-b0bf-f450969590ad 10/22/2006 10:32:44 PM
23e72b1a-b701-4b6d-9c39-83018dae8db1 10/16/2006 8:55:15 PM
230b9534-0d1e-4163-92f1-5f80927cac13 9/18/2006 7:53:49 PM
18ebec6d-adfa-4249-8c46-8daacca724e5 10/23/2006 9:55:45 PM

So in this case I want the LastActivityDate, UserName, FavouriteSince from UserCode 5 and 7
I need a select statement that for parameter usercode (value in this case 4) return:

LastActivityDate FavouriteSince UserName
10/16/2006 8:55:15 PM 10/16/2006 11:24:01 PM Jean
10/23/2006 9:55:45 PM 10/14/2006 7:32:30 PM Simon

I know this is tricky because a record in the aspnet_Users table is identified based on UserID, whereas in tblFavourites its on UserCode, so I need to get the matching UserCode and UserID from tblUserData in order to get the right lastactivitydate for a specific user.

Can you help me with the right statement?|||

SELECT u.LastActivityDate,f.FavouriteSince,ud.UserName

FROM tblFavourites f

JOIN tblUserData ud ON (ud.UserCode=f.UserCodeFavourite)

JOIN aspnet_Users u ON (u.UserID=ud.UserID)

WHERE f.UserCodeOwner=4

|||

If I understand your problem correctly, the following should work:

SELECT u.LastActivityDate
, f.FavouriteSince
, n.UserName
FROM aspnet_Users u, tblFavourites f, tblUserData n
WHERE n.UserID = u.UserID
AND n.UserCode = f.UserCodeOwner
AND n.UserCode = '4'

|||Sorry Motley - Didn't mean to step on your toes there. I didn't see your post until after I saved mine.|||Heh, no problem. As long as the question gets answered.|||

and that it is! :)

join SmallDateTime column very slow

Hello,
I posted this on programming group, but hasn't got any reply yet.
I have a table and the definition is like:
CarTable(
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
ModelID,
MakeID,
RegisterDate smallDateTime null
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ModelID and MakeID are foreign keys from a reference table (call it
refTable here).
I created an idex on MakeID, modelID and RegisterDate as:
CREATE UNIQUE CLUSTERED INDEX
[IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
(
[MakeID] ASC,
[ModelID] ASC,
[RegisterDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
= OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
[INDEX_FG]
The CarTable is quite big (50 million records)
When I do query such as
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID)
It is fairly quick - 2 seonds
However, if I put RegisterDate in the join condition, it becomes very
slow. For example:
select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
RegisterDate from CarTable inner join RefTable
on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID= RefTable.ModelID and
CarTable.RegisterDate > '01/01/1980')
This one is much slower 2 minutes.
My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
Do I need to add an non-clustered index just for RegisterDate?
Many ThanksReplied to in .programming.
Please do not multi-post. If you want to ask the same question in
several newsgroups then the prefered method is to cross-post. This
prevents double answers (and double effort).
--
Gert-Jan
DAXU@.hotmail.com wrote:
> Hello,
> I posted this on programming group, but hasn't got any reply yet.
> I have a table and the definition is like:
> CarTable(
> [RowNumber] [int] IDENTITY(0,1) NOT NULL,
> ModelID,
> MakeID,
> RegisterDate smallDateTime null
> PRIMARY KEY CLUSTERED
> (
> [RowNumber] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
> = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> ModelID and MakeID are foreign keys from a reference table (call it
> refTable here).
> I created an idex on MakeID, modelID and RegisterDate as:
> CREATE UNIQUE CLUSTERED INDEX
> [IX_vwVehicleMain_ReportingAggregate_Aggregate] ON [CarTable]
> (
> [MakeID] ASC,
> [ModelID] ASC,
> [RegisterDate] ASC
> )WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
> = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON
> [INDEX_FG]
> The CarTable is quite big (50 million records)
> When I do query such as
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID)
> It is fairly quick - 2 seonds
> However, if I put RegisterDate in the join condition, it becomes very
> slow. For example:
> select MakeID, RefTable.MakeName, ModelID, RefTable.ModelName,
> RegisterDate from CarTable inner join RefTable
> on (CarTable.MakeID= RefTable.MakeID and CarTable.ModelID=> RefTable.ModelID and
> CarTable.RegisterDate > '01/01/1980')
> This one is much slower 2 minutes.
> My index cover MakeID, ModelID and RegisterDate, so why it is so slow?
> Do I need to add an non-clustered index just for RegisterDate?
> Many Thanks

Friday, February 24, 2012

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 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 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 Issue

my scenario is given below

createtable #product (prodID int, subproductid varchar(20))

createtable #subproduct (subproductid varchar(20),description varchar(40))

Insert #product select 1,'1001/2002'

Insert #product select 1,'3003/4004'

Insert #product select 1,'5005/6006'

insert subproduct select 1001 ,'aaa'

insert subproduct select 2002 ,'bbb'

insert subproduct select 3003 ,'ccc'

insert subproduct select 4004 ,'ddd'

insert subproduct select 5005 ,'eee'

insert subproduct select 6006 ,'fff'

this is how our two tables is related. i know its a bad design . but i can't help it.

my question is how can i join these two table ?

thanks in advance

Leena S

S Leena,

IF you know that the table design is bad, why can't you fix it? Do you need help in understanding why it is so bad?

Apparently, from the way the data is put together, and looking at this query, someone made some boneheaded decisions about how to store data in a database. You can be the 'hero' and correct the 'mistake'.

And then life, with queries such as this, will be so much easier...

|||

Hi,

One of the solutions would be a UDF that splits the text and join with that.

But I too suggest the above remark because the design is against the 'rules' of normalization imho ;-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||You could join using:

SELECT * --Change this
FROM #product JOIN #subproduct
ON '/' + #product.subproductid + '/' like '%/' + #subproduct.subproductid + '/%'

But as suggested already, you should consider changing your structure.

Rob|||

This is really bad design.

Anyhow if you are not authorized to change the design, the following approach may help you..

Code Snippet

create table #product(

prodID int,

subproductid varchar (20)

);

Insert #product select 1,'1001/2002'

Insert #product select 2,'3003/4004'

Insert #product select 3,'5005/6006'

create table #subproduct(

subproductid varchar(20),

description varchar(40)

);

Insert #subproduct select 1001 ,'aaa'

Insert #subproduct select 2002 ,'bbb'

Insert #subproduct select 3003 ,'ccc'

Insert #subproduct select 4004 ,'ddd'

Insert #subproduct select 5005 ,'eee'

Insert #subproduct select 6006 ,'fff'

--Generating Number Tables;

Select Identity(int,1,1) as Number Into #Numbers From #subproduct A Cross Join #subproduct B;

--Getting the results

Select ProdId,description From

(

Select

prodId

,Case When Number <= Len(subproductid) Then Substring('/' + subproductid + '/', Number+1, CharIndex('/',subproductid + '/', Number+1)-Number) End subproductid

from

#product P

Cross Join #Numbers N

Where

Number <= Len(subproductid) AndSUBSTRING('/' + subproductid + '/', number, 1) = '/'

) as Product

Join #subproduct sub on sub.subproductid = Product.subproductid

Monday, March 26, 2012

Joins with XQuery

Joins with XQuery

Friday, March 23, 2012

JOINing... but not with the multiple rows thing.

Monday, March 12, 2012

joining a varchar with a int column

Joining 2 tables...

joining 2 tables - outer join

Wednesday, March 7, 2012

join statement required

join SmallDateTime column very slow

Friday, February 24, 2012

JOIN question and NOT JOIN

Join Query

Join Query

Monday, February 20, 2012

Join problem

Thanks everbody who help and answer.

Join Problem

Join Issue

join or subselect

Blog Archive

About Me