JOINS / Exclude rows

Help:
How do I constuct a queery returning all the rows from table A that do
NOT have a match in table B for a given column?
To be more specific, I am pulling a copy of the sysprcesses table. I
then want to report out the rows that ARE NOT in my permitted logins
(i.e., potiential problems)
PermittedUsers
==============
ID LoginName
-- --
01 johnsmith
02 davebarry
-- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
TABLE #processlist
Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
#processlist From master..sysprocesses (nolock)
-- This will give me a list of all the processes running by permitted
users:
select * INTO #goodprocesses from #processlist PL INNER JOIN
PermittedUsers PU ON (PL.loginame =PU.LoginName collate
Latin1_General_CI_AS)
-- I need a query giving me the "opposite"
select * INTO #suspectprocesses from #processlist PL INNER JOIN
PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
Latin1_General_CI_AS)
-- but the above is not it.
Can anyone help?
THANKS!
d.Try:
SELECT COLUMNLIST
FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE1.COLID = TABLE2.COLID)
HTH
Jerry
<google@.dcbarry.com> wrote in message
news:1128642174.792462.153940@.g49g2000cwa.googlegroups.com...
> Help:
> How do I constuct a queery returning all the rows from table A that do
> NOT have a match in table B for a given column?
>
> To be more specific, I am pulling a copy of the sysprcesses table. I
> then want to report out the rows that ARE NOT in my permitted logins
> (i.e., potiential problems)
>
> PermittedUsers
> ==============
> ID LoginName
> -- --
> 01 johnsmith
> 02 davebarry
>
>
> -- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
> TABLE #processlist
> Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
> #processlist From master..sysprocesses (nolock)
>
> -- This will give me a list of all the processes running by permitted
> users:
> select * INTO #goodprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame =PU.LoginName collate
> Latin1_General_CI_AS)
> -- I need a query giving me the "opposite"
> select * INTO #suspectprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
> Latin1_General_CI_AS)
> -- but the above is not it.
>
> Can anyone help?
>
> THANKS!
> d.
>|||Hi
SELECT <column list> FROM TableA LEFT JOIN TableB ON TableA.pk=TableB.pk
WHERE TableB.pk IS NULL
<google@.dcbarry.com> wrote in message
news:1128642174.792462.153940@.g49g2000cwa.googlegroups.com...
> Help:
> How do I constuct a queery returning all the rows from table A that do
> NOT have a match in table B for a given column?
>
> To be more specific, I am pulling a copy of the sysprcesses table. I
> then want to report out the rows that ARE NOT in my permitted logins
> (i.e., potiential problems)
>
> PermittedUsers
> ==============
> ID LoginName
> -- --
> 01 johnsmith
> 02 davebarry
>
>
> -- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
> TABLE #processlist
> Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
> #processlist From master..sysprocesses (nolock)
>
> -- This will give me a list of all the processes running by permitted
> users:
> select * INTO #goodprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame =PU.LoginName collate
> Latin1_General_CI_AS)
> -- I need a query giving me the "opposite"
> select * INTO #suspectprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
> Latin1_General_CI_AS)
> -- but the above is not it.
>
> Can anyone help?
>
> THANKS!
> d.
>

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

Joining when rows don't exist.

Afternoon all..
I'm trying to join two tables together. The tables have information
like:
Table_Data
Name - Calls - Sales
Bob -- 17 -- 10
John -- 23 -- 5
Dave -- 25 -- 7
Carol -- 16 -- 13
Table_Target_Data
Name - Calls - Sales
Bob -- 30 -- 20
Carol -- 40 -- 30
What I need to do is to join them together, but also include those
people that aren't mentioned in the target table.
So far I have :
SELECT
T1.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1,Table_Data T2
where T1.Name = T2.Name
Group by T1.Name
This will only bring back those names that are in both tables. How can
I bring back 'John' and 'Dave', filling in relevant results with <null>
or '0'?Hi John,
SELECT
T1.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1
LEFT JOIN Table_Data T2
ON T1.Name = T2.Name
HTH, Jens Suessmeyer.|||You need to use left join and you're using inner join.
MC
<vladikavkaz@.XXXXyou.co.uk> wrote in message
news:1139227585.567641.110490@.g44g2000cwa.googlegroups.com...
> Afternoon all..
> I'm trying to join two tables together. The tables have information
> like:
> Table_Data
> Name - Calls - Sales
> Bob -- 17 -- 10
> John -- 23 -- 5
> Dave -- 25 -- 7
> Carol -- 16 -- 13
> Table_Target_Data
> Name - Calls - Sales
> Bob -- 30 -- 20
> Carol -- 40 -- 30
> What I need to do is to join them together, but also include those
> people that aren't mentioned in the target table.
> So far I have :
> SELECT
> T1.Name AS [Agent Name],
> T1.Calls AS [Target Client Calls],
> T2.Calls AS [Client Calls],
> T1.Sales AS [Target Sales],
> T2.Sales AS [Sales]
> FROM
> Table_Target_Data T1,Table_Data T2
> where T1.Name = T2.Name
> Group by T1.Name
> This will only bring back those names that are in both tables. How can
> I bring back 'John' and 'Dave', filling in relevant results with <null>
> or '0'?
>|||vladikavkaz@.XXXXyou.co.uk wrote on 6 Feb 2006 04:06:25 -0800:

> Afternoon all..
> I'm trying to join two tables together. The tables have information
> like:
> Table_Data
> Name - Calls - Sales
> Bob -- 17 -- 10
> John -- 23 -- 5
> Dave -- 25 -- 7
> Carol -- 16 -- 13
> Table_Target_Data
> Name - Calls - Sales
> Bob -- 30 -- 20
> Carol -- 40 -- 30
> What I need to do is to join them together, but also include those
> people that aren't mentioned in the target table.
> So far I have :
> SELECT
> T1.Name AS [Agent Name],
> T1.Calls AS [Target Client Calls],
> T2.Calls AS [Client Calls],
> T1.Sales AS [Target Sales],
> T2.Sales AS [Sales]
> FROM
> Table_Target_Data T1,Table_Data T2
> where T1.Name = T2.Name
> Group by T1.Name
> This will only bring back those names that are in both tables. How can
> I bring back 'John' and 'Dave', filling in relevant results with <null>
> or '0'?
Use an outer join.
SELECT
T2.Name AS [Agent Name],
T1.Calls AS [Target Client Calls],
T2.Calls AS [Client Calls],
T1.Sales AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1 RIGHT OUTER JOIN Table_Data T2
ON T1.Name = T2.Name
Group by T2.Name
If you want 0 in the target data rather than nulll, use
SELECT
T2.Name AS [Agent Name],
COALESCE(T1.Calls,0) AS [Target Client Calls],
T2.Calls AS [Client Calls],
COALESCE(T1.Sales,0) AS [Target Sales],
T2.Sales AS [Sales]
FROM
Table_Target_Data T1 RIGHT OUTER JOIN Table_Data T2
ON T1.Name = T2.Name
Group by T2.Name
Dan|||Thanks all for the help.
Got the results I need now.

Joining two tables with repeating rows

Hi. I am trying to get data from two different tables. The first table contains user access data for access to different modules of our application. There are only records for the modules that the user has access to. So if User1 can only access 2 of the 5 modules, there will be 2 User1 records. The second table lists the modules.

UserAccess table:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User2 1 1

Modules table:

ModuleID Description

1 Mod1

2 Mod2

3 Mod3

4 Mod4

5 Mod5

What I am trying to select is a list of all modules for each user, whether they have access or not. So basically I would like the data from the Modules table to repeat 5 rows for each user. This is a sample of the output I am trying to get:

UserID ModuleID AccessLevel

User1 1 1

User1 2 1

User1 3 Null

User1 4 Null

User1 5 Null

User2 1 1

User2 2 Null

User2 3 Null

User2 4 Null

User2 5 Null

I've tried all sorts of joins but haven't been able to get this to happen. Not sure if this is possible? Thanks!!!

The magic words you want here are "CROSS JOIN". Previously known as 'comma'.

First make yourself a table of users. If you have this in a separate table already, then great. If you don't, slap yourself, write a post-it note to do it later, and make do with:

select *
from
(select distinct UserID from UserAccess) u

Now do your cross join to the modules table.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m

Note - there's no ON clause here... you want every possible combination.

Now join this to your UserAccess table to see if they have access or not. You'll want to use a LEFT JOIN to make sure you don't eliminate the rows you already have.

select *
from
(select distinct UserID from UserAccess) u
cross join
Modules m
left join
UserAccess ua
on ua.moduleid = m.moduleid
and ua.userid = u.userid

Now, ua will have null records for the times when there is no record to match (it's the way LEFT JOIN works). So you can just have a look to see if one of the records which can't be null (like userid) is null or not...

select u.UserID, m.ModuleID, case when ua.UserID is null then 1 else 0 end as AccessLevel

from

(select distinct UserID from UserAccess) u

cross join

Modules m

left join

UserAccess ua

on ua.moduleid = m.moduleid

and ua.userid = u.userid

Hope this works for you!

Rob|||

Part of the problem is that you need to have a users table. Then this becomes a bit easier of a task. So I built one in the query:

--sample tables (please include in future if you can...)
create table userAccess
(
userId varchar(8)
,moduleId int
,accessLevel int
,primary key (userId, moduleId)
)
insert into userAccess
select 'User1', 1, 1
union all
select 'User1', 2, 1
union all
select 'User2', 1, 1

create table modules
(
moduleId int
,description varchar(10)
,primary key (moduleId)
)
insert into modules
select 1, 'Mod1'
union all
select 2, 'Mod2'
union all
select 3, 'Mod3'
union all
select 4, 'Mod4'
union all
select 5, 'Mod5'

This query will get it for you:

select users.userId, modules.moduleId,userAccess.accessLevel
from (select distinct userId
from userAccess) as users --creates the users table with all users that have some access
cross join modules --cross join it with modules to get the all modules for all users set.

left outer join userAccess --then left join it to the access table to get the accessLevel column...
on userAccess.userId = users.userId
and modules.moduleId = userAccess.moduleId

|||Apparently I worked on my solution for at least 15 minutes :)|||15 minutes? Really?|||

Thank you both so much!!! I KNEW there was a way to do this but I couldn't quite get there. I do have a users table so luckily I do not have to slap myself.

Thanks again!!!

|||

You try to find out in

www.sqlzoo.com

Joining two datasets?

Hi All,

I have got two datasets. Some 'normal' rows with state values and an addtional dataset with the translation from state value to plain text (value / text pair).

A table object is attached to the first dataset, but I would like to show the plain text from the second dataset in a group header. So I'd like to do some kind of 'look up in another dataset'.

I wasn't able to find a function that could help me solve this problem. Does anyone has any ideas on this?

Kind regards.

Create 2 new parameters: Parameter1 will have all the values from DataSet2 (dataset with text/value paur) and Parameter2 will have all the text from DataSet2.

Write a function (VB.Net ot C#) in the report code (Report -> Report Properties -> Code tab) which will accept these parameters as object arrays and then store them in a shared variable.

Create a textbox in the beginning of the report which will call the above function by passing both the parameters. If you just specify the parameter (say Parameter!Parameter1.Value), it is considered as an array as it have more than one value and it will be received in the function as an object array.

Write another function (function2) in the code which will get the state id (or value) and then loop through the shared arrays (already stored) to find the corresponding text.

Call function2 from your table header to get the text for a given value.

Hope this helps. If more examples and explanation is needed, let me know.

Shyam

|||

Hello Shyam,

thanks a lot for your post. I think this a way to solve my problem.

But also think MS should think about this for future versions. I think it is very complicated (Crystal Reports makes it much easier for developers). It would also be very helpful if datasets could be accessed directly within custom funcation.

Kind regards, CLive76

|||

An alternative approach is to combine the two datasets already in the dataset query. Some options to consider:
? Linked Server functionality (see http://msdn2.microsoft.com/en-us/library/aa213283(SQL.80).aspx)
? OpenRowSet functionality to join data from another database server into the current query (http://msdn2.microsoft.com/en-us/library/ms190312.aspx, http://msdn2.microsoft.com/en-us/library/aa276850(SQL.80).aspx)

-- Robert

Joining Rows in a SubQuery

I have the following code in a stored procedure
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
order by CategoryName
it's output is similar to this:
CatID | ParentId | CategoryName | pagecount
6 1 Cat1 0
4 Null Cat2 3
I would like to make a new column (say Newtext) adn Return something like th
is
CatID | ParentId | CategoryName | pagecount | NewText
6 1 Cat1 0 Cat1 (0)
4 Null Cat2 3 Cat2 (3)
How can i join the data to have this result?
I would hope that i could do something like CategoryName + "(" + pagecount +
")" in some sort of sql statement.
Thanks for any input
Lots of ways, here's one
SELECT CatID, ParentId, CategoryName,pagecount ,
CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
NewText
FROM
(
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
) X
order by CategoryName|||One approach is to use a derived table, embedding the existing query
in the FROM clause of an outer query:
SELECT *,
NewText =
CategoryName + '(' + convert(varchar(8),pagecount) + ')'
FROM (<query as you stated it> ) as X
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
<Fabuloussites@.discussions.microsoft.com> wrote:

>I have the following code in a stored procedure
>SELECT CatID, ParentId, CategoryName, (select count(*)
>from members WHERE DirectoryCat = DirectoryCats.CatID and
>InFreeDirectory=1 and ApproveDirectory=1) pagecount
>FROM DirectoryCats
>where active=1
>order by CategoryName
>
>it's output is similar to this:
>CatID | ParentId | CategoryName | pagecount
>6 1 Cat1 0
>4 Null Cat2 3
>I would like to make a new column (say Newtext) adn Return something like t
his
>CatID | ParentId | CategoryName | pagecount | NewText
>6 1 Cat1 0 Cat1 (0)
>4 Null Cat2 3 Cat2 (3)
>How can i join the data to have this result?
>I would hope that i could do something like CategoryName + "(" + pagecount
+
>")" in some sort of sql statement.
>Thanks for any input|||thanks for the fast and helpful response.
"markc600@.hotmail.com" wrote:

>
> Lots of ways, here's one
>
> SELECT CatID, ParentId, CategoryName,pagecount ,
> CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
> NewText
> FROM
> (
> SELECT CatID, ParentId, CategoryName, (select count(*)
> from members WHERE DirectoryCat = DirectoryCats.CatID and
> InFreeDirectory=1 and ApproveDirectory=1) pagecount
> FROM DirectoryCats
> where active=1
> ) X
> order by CategoryName
>|||thanks for the fast and helpful response.
"Roy Harvey" wrote:

> One approach is to use a derived table, embedding the existing query
> in the FROM clause of an outer query:
> SELECT *,
> NewText =
> CategoryName + '(' + convert(varchar(8),pagecount) + ')'
> FROM (<query as you stated it> ) as X
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
> <Fabuloussites@.discussions.microsoft.com> wrote:
>
>

joining rows

HI,
I've got a little problem. I have 2 rows in different tables ... is there
any way how to simply get these two 2 rows in table?
The rows are similar, only datas are different.
Thanks a lot.
S.You can join the 2 tables, filter on the specific record(s) you need, and
insert into another table using a single line. For example:
insert into MyTable
select
A.*,
B.*
from
A
left join
B on B.Date = A.Date
where
A.Date = xxx
"schnackie@.post.cz" <schnackiepostcz@.discussions.microsoft.com> wrote in
message news:A2DF5422-2B5D-44C3-B878-28656B30D988@.microsoft.com...
> HI,
> I've got a little problem. I have 2 rows in different tables ... is there
> any way how to simply get these two 2 rows in table?
> The rows are similar, only datas are different.
> Thanks a lot.
> S.|||schnackie@.post.cz wrote:
> HI,
> I've got a little problem. I have 2 rows in different tables ... is
> there any way how to simply get these two 2 rows in table?
> The rows are similar, only datas are different.
> Thanks a lot.
> S.
Please read:
www.aspfaq.com/5006
My guess is that you need a union query:
select <column list> from table1 where <criteria>
union all
select <column list> from table2 where <criteria>
You can use this in an insert statement:
Insert Into table3 (<column list> )
select <column list> from table1 where <criteria>
union all
select <column list> from table2 where <criteria>
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Valid values of database compatibility level are 60, 65, or 70.
This means , I'm screwed :P
I'll have a look at this in BOL or the web thanks for the pointer into right
direction.
"schnackie@.post.cz" wrote:

> HI,
> I've got a little problem. I have 2 rows in different tables ... is there
> any way how to simply get these two 2 rows in table?
> The rows are similar, only datas are different.
> Thanks a lot.
> S.

Joining on a pivot table is sluggish

I am building a table-value UDF that joins a significant-sized pivot table (ends up with 10000 rows) to another significant-size table. The query takes about 3 minutes, and I noticed that the memory allocation for the sqlserver process goes through the roof.

I tried selecting the pivot table into a temp table, and joining on that, and it is real fast - about 2 seconds. However, since you can't use a temp table in a UDF, I needed to use a table variable instead. Changing the temp table to a table variable gave me the same slow results - about 3 minutes. Does anyone have a clue why this would make any difference? My SQL looks like the following:

Code Snippet

declare @.temp table

(

[PrimaryKey] bigint,

[CategoryName] varchar(15),

[Description] varchar(255),

[LongDescription] varchar(1000)

)

insert into

@.temp

select

PrimaryKey,

convert(varchar(15),[CategoryName]),

convert(varchar(255),[Description]),

convert(varchar(1000),[LongDescription])

from

history pivot

(

max(oldvalue)

for colname

in ([CategoryName],[Description],[LongDescription])

) as tn

select

*

from

Categories

left outer join @.temp on Categories.CategoryID=@.temp.PrimaryKey

I am running SQL Server 2005 SP2.

Try putting a primary key constraint on your Primary Key Column. No guarantees, but it might help.

Code Snippet

declare @.temp table

(

[PrimaryKey] bigint PRIMARY KEY,

[CategoryName] varchar(15),

[Description] varchar(255),

[LongDescription] varchar(1000)

)

If that doesn't help, check out the plan of the two queries (and post them), it might show something. Is categoryId the primary key for Categories?

|||

Adding the primary key definition didn't help anything. The real problem is when inserting into the table variable, not performing the join. Once the data is in the table variable, it will work fine. The join only time the join works slowly is if I either use a CTE or a derived table to do a brute join. It seems like this in-memory operation involving a pivot table is somehow causing it to perform slowly. It's slow inserting into a table variable, or simply doing the join. The only solution I've found so far is to use a temp table, which I can't use in a UDF.

I looked at the execution plans using a table variable vs a temp table, and they are essentially the same. I'm not sure how to upload a picture onto the forum, but here are the major tasks for each:

% for temp table / % for table variable

Clustered index scan: 48% / 46 %

Hash match: 10% / 17%

Clustered index seek 41% / 35%

Another odd thing I noticed about the pivot table performance is that it works much faster if I add an extra column to the table. I.e, if I add a blank bit column to the source table, and don't define it as one of the pivoted fields, it's a lot faster. Or rather, I should say it is really slow if I don't do this (even using a temp table).

=== Edited by jehhynes @. 05 May 2007 8:08 PM UTC===
Following is the table definition for my source table that I am pivoting:

CREATE TABLE [dbo].[history](

[colname] [varchar](50) NOT NULL,

[primarykey] [bigint] NOT NULL,

[oldvalue] [sql_variant] NULL,

[perfcol] [bit] NULL

) ON [PRIMARY]

perfcol is the empty bit column i added which increased the performance

Joining a query (inner/outer)

I have this current sql query which works great, but it only returns
the rows that exist in all tables.
I need to return all rows from po_cfg_Budget B and simply fill in 0 if
there are no totals.
Any help would be greatly appreciated.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S,
(select * from po_cfg_budget) B,
(Select * from po_cfg_dept) D
where S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
and S.DeptID = D.BudgetDeptID
order by B.DeptIDThis might work for you (passes syntax check but not actually
tested)...
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S
left outer join po_cfg_budget B on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D on S.DeptID = D.BudgetDeptID
order by B.DeptID
If you have null dollar amounts you can wrap those in the ISNULL or
COALESCE function.
Bryce|||Not entirely sure what you are looking for but try this.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
coalesce((B.Budget - NotApproved - Approved), 0) as AmountRemaining,
coalesce((B.Budget - Approved),0) as AmountApprovedRemaining
from po_cfg_budget B
left outer join BudgetStatus S
on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D
on D.BudgetDeptID = S.DeptID
order by B.DeptID
The inline views that you were using were redundant.
"Dave" wrote:

> I have this current sql query which works great, but it only returns
> the rows that exist in all tables.
> I need to return all rows from po_cfg_Budget B and simply fill in 0 if
> there are no totals.
> Any help would be greatly appreciated.
> select S.*,
> b.Budget,
> B.Description,
> D.DeptID as DeptDescription,
> (B.Budget - NotApproved - Approved) as AmountRemaining,
> (B.Budget - Approved) as AmountApprovedRemaining
> from BudgetStatus S,
> (select * from po_cfg_budget) B,
> (Select * from po_cfg_dept) D
> where S.Years = B.BudgetYear
> and S.Months = B.BudgetMonth
> and S.DeptID = B.DeptID
> and S.AccountID = B.AccountID
> and S.DeptID = D.BudgetDeptID
> order by B.DeptID
>

Joining 5 tables

Hi,

I have to join five tables in sql server using primary and foreign keys .I have joine all the tables

but problem was duplicate rows were repeating.Could any body help me to resolve this problem.

Thanks in advance.

Regards,

Raja.

Could you post the query and the table relationships? No one will be able to help you without that.

|||

1.Feature Column NameDatatypeSizeConstraints1FeatureIdUniqueIdentifiernot null, primary key2FeatureNamevarchar255not null 2.Products 1ProductIdUniqueIdentifiernot null,primary key2ProductNamevarchar255not null 3.Feature_Product 1FeaturIdUniqueIdentifiernot null,foreign key Feature2ProductIdUniqueIdentifiernot null,foreign key Products 4.Customer 1CustomerIdUniqueIdentifiernot null,primary key2CustomerNamevarchar255not null3Emailidvarchar255not null4Mobilevarchar2555Faxvarchar2556Telephonevarchar2557Addressvarchar5008TypeIdintidentityforeign key customertype 5.Customer_Product 1CustomerIdUniqueIdentifiernot null,foreign key Customer2ProductIdUniqueIdentifiernot null,foreign key Products 6.License 1LicenseIdUniqueIdentifiernot null,primary key2Typevarchar255not null3Release_datedatetimenot null4License_Versionvarchar255not null5StartDatedatetime6EndDatedatetime7HostId1varchar2558HostId2varchar2559HostId3varchar25510HostName1varchar25511HostName2varchar25512HostName3varchar25513IPAddress1varchar25514IPAddress2varchar25515IPAddress3varchar25516No_Licensesint17CustomerIdUniqueIdentifiernot null,foreign key Customer18MailTovarchar255 7.License_Product 1LicenseIdUniqueIdentifiernot null,foreign key License2ProductIdUniqueIdentifiernot null,foreign key Products

select ProductName,FeatureName,LicenseType, REPLACE(CONVERT(VARCHAR(11), ReleaseDate, 106), ' ', '-') AS [ReleaseDate],LicenseVersion,REPLACE(CONVERT(VARCHAR(11), StartDate, 106), ' ', '-') AS [StartDate],REPLACE(CONVERT(VARCHAR(11), EndDate, 106), ' ', '-') AS [EndDate],HostID1,HostID2,HostID3,HostName1,HostName2,HostName3,ipaddress1,IPAddress2,IPAddress3,No_Licenses,MailTo,CustomerName,EmailID,Mobile,Fax,Telephone,Address from products p,feature f,feature_product fp,license_product lp, Customer_product cp, license l,customers c where p.productid = fp.productid
and f.featureid = fp.featureid
and l.licenseid = lp.licenseid
and c.customerid = l.customerid
and p.productid = cp.productid
and p.productid = lp.productid
and c.customerid = cp.customerid

Hi Prasanth,

These are the tables and relationship for my task and I worked with that code but duplicate rows are generated

could you help me from this iisue.

ThankYou,

Rajasekhar.

|||

1.Feature Column NameDatatypeSizeConstraints1FeatureIdUniqueIdentifiernot null, primary key2FeatureNamevarchar255not null 2.Products 1ProductIdUniqueIdentifiernot null,primary key2ProductNamevarchar255not null 3.Feature_Product 1FeaturIdUniqueIdentifiernot null,foreign key Feature2ProductIdUniqueIdentifiernot null,foreign key Products 4.Customer 1CustomerIdUniqueIdentifiernot null,primary key2CustomerNamevarchar255not null3Emailidvarchar255not null4Mobilevarchar2555Faxvarchar2556Telephonevarchar2557Addressvarchar5008TypeIdintidentityforeign key customertype 5.Customer_Product 1CustomerIdUniqueIdentifiernot null,foreign key Customer2ProductIdUniqueIdentifiernot null,foreign key Products 6.License 1LicenseIdUniqueIdentifiernot null,primary key2Typevarchar255not null3Release_datedatetimenot null4License_Versionvarchar255not null5StartDatedatetime6EndDatedatetime7HostId1varchar2558HostId2varchar2559HostId3varchar25510HostName1varchar25511HostName2varchar25512HostName3varchar25513IPAddress1varchar25514IPAddress2varchar25515IPAddress3varchar25516No_Licensesint17CustomerIdUniqueIdentifiernot null,foreign key Customer18MailTovarchar255 7.License_Product 1LicenseIdUniqueIdentifiernot null,foreign key License2ProductIdUniqueIdentifiernot null,foreign key Products

select ProductName,FeatureName,LicenseType, REPLACE(CONVERT(VARCHAR(11), ReleaseDate, 106), ' ', '-') AS [ReleaseDate],LicenseVersion,REPLACE(CONVERT(VARCHAR(11), StartDate, 106), ' ', '-') AS [StartDate],REPLACE(CONVERT(VARCHAR(11), EndDate, 106), ' ', '-') AS [EndDate],HostID1,HostID2,HostID3,HostName1,HostName2,HostName3,ipaddress1,IPAddress2,IPAddress3,No_Licenses,MailTo,CustomerName,EmailID,Mobile,Fax,Telephone,Address from products p,feature f,feature_product fp,license_product lp, Customer_product cp, license l,customers c where p.productid = fp.productid
and f.featureid = fp.featureid
and l.licenseid = lp.licenseid
and c.customerid = l.customerid
and p.productid = cp.productid
and p.productid = lp.productid
and c.customerid = cp.customerid

Hi Prasanth,

These are the tables and relationship for my task and I worked with that code but duplicate rows are generated

could you help me from this iisue.

ThankYou,

Rajasekhar.