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 Views & Query Performance

Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.

A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.

Thanks, Davequerying a query that queries a query that queries yet another query. yeah that might make query processor a little pissy. Views use indexes and statistics. Have you looked at the query execution plan? Could just be a couple of bad scans.

I discourage nested views among my developers. They say "code reuse". I call them lazy.|||The execution plan looks like a map of Illinois listing the location of every McDonald's. In other words it's full of icons. As for the use of indexes, I don't believe these views meet the criteria, especially the one indicating "The view must not reference any other views, only base tables."

I suggested they rewrite the code to not use so many views.|||yeah the views may be doing a lot of extra junk you just do not need.|||...As for the use of indexes, I don't believe these views meet the criteria,...I don't think Thrasy was referring to "indexed views", but to the fact the views can make use of indexes on their source tables.|||Ok. Either way the execution plan was so large it wasn't worth the effort of debugging. Do you know of any documentation indicating to avoid using nested views? I would like to pass it along to our developers, assuming I can find anything.

Dave|||Hmmm...pity there isn't some sort of public bulletin board, or forum, monitored by experienced and knowledgeable experts to which you could direct them. :(

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 tables with many to many relationship

How can I join two tables with many to many relationship? Any idea?
On Mon, 27 Sep 2004 12:27:47 +0300, Comfort wrote:

>How can I join two tables with many to many relationship? Any idea?
>
Hi Comfort,
A many-to-many relationship is usually implemented through a linking
table. For example, let's say each employee can work on multiple proects
and each project can be worked on by multiple employees. Your tables would
look somewhat like this:
CREATE TABLE Personnel (EmpID char(10) NOT NULL,
other columns,
PRIMARY KEY (EmpID)
)
CREATE TABLE Projects (ProjID int NOT NULL,
other columns,
PRIMARY KEY (ProjID)
)
CREATE TABLE Assignments (EmpID char(10) NOT NULL,
ProjID int NOT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Personnel,
FOREIGN KEY (ProjID) REFERENCES Projects
)
This structure also allows you to store extra information about the
relations (e.g. when was the employee assigned to a project, wat role will
(s)he play in the project, etc)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Can u please tell the SQL Select syntax for joining two tables with many to many relationships. The two tables don't hold the foreign keys, but the third table as u said, holds them. Then how to perform join?
Thanx
A.P

Quote:

Originally posted by Hugo Kornelis
On Mon, 27 Sep 2004 12:27:47 +0300, Comfort wrote:

>How can I join two tables with many to many relationship? Any idea?
>
Hi Comfort,
A many-to-many relationship is usually implemented through a linking
table. For example, let's say each employee can work on multiple proects
and each project can be worked on by multiple employees. Your tables would
look somewhat like this:
CREATE TABLE Personnel (EmpID char(10) NOT NULL,
other columns,
PRIMARY KEY (EmpID)
)
CREATE TABLE Projects (ProjID int NOT NULL,
other columns,
PRIMARY KEY (ProjID)
)
CREATE TABLE Assignments (EmpID char(10) NOT NULL,
ProjID int NOT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Personnel,
FOREIGN KEY (ProjID) REFERENCES Projects
)
This structure also allows you to store extra information about the
relations (e.g. when was the employee assigned to a project, wat role will
(s)he play in the project, etc)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Joining two tables to modify a tree structure with more information

I'm trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry for the tabs that won't work)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667
I have tried many ways, but I'm not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable

Thanks :)Worked it out.Used inner join to get the first data, and run an update afterwards to update level 3 and 4sql

Joining two tables to modify a tree structure

I’m trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but I’m not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

I think you have to Innerjoin the tables

Just go through this|||Thanks for answer, I had made it a bit to complex.

Worked it out.

Used inner join to get the first data, and run an update afterwards to update level 3 and 4|||

Quote:

Originally Posted by KingKong07

Im trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry about the tabs, can't get them right here)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different

ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
15063823136510667
15073824136510667
15093825136410667
15093826136410667

I have tried many ways, but Im not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable...

Thanks :)

1- i think you should have a MainCatalogId in your second table.because if you have many Maincatalog you can not save relation for other Maincatalog if you have repeated code of items.
2-you must create a dataset returning storedprocedure with the columns for your output.
3-in your storedprocedure you must have three cursor with three loop for three level(suppose you have three level),each loop contains parent loop.then fetch what you want to output for your output dataset.
4-use your storedprocedure As a table in your application .

Joining two tables multiple times

Hi
I have two tables (in an third party application, I cannot change the
data structure) as follows:-
T1 - Main data
Amount Ref1 Ref2 Ref3
==============================
100 A A A
150 A B A
200 A B B
T2 - Reference data
Type Value Name
=========================
Ref1 A Area 1
Ref1 B Area 2
...
Ref2 A Dept 1
Ref2 B Dept 2
...
Ref3 A Type 1
Ref3 B Type 3
At a simple level I want to be able to return (though obviously there
are many more complex applications of this data that I want to do):-
Amount Name1 Name2 Name3
==================================
100 Area 1 Dept 1 Type 1
150 Area 1 Dept 2 Type 1
200 Area 1 Dept 2 Type 2
At the moment I achive this by creating a query for each Refn type
Select * FROM T2
WHERE Type = 'Refn'
and then joining T1 several types to each of these queries. Is there a
way of creating this without creating the queries first?
App is SQL server, I'm using Access 2000 to query, but quite happy (and
permitted) to use passthrough queries instead.
Any help gratefully received!
MattTry this
create table #T1(Amount int, Ref1 char(1),Ref2 char(1),Ref3 char(1))
insert into #T1(Amount,Ref1,Ref2,Ref3) values(100,'A','A','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(150,'A','B','A')
insert into #T1(Amount,Ref1,Ref2,Ref3) values(200,'A','B','B')
create table #T2(Type char(4),Value char(1), Name varchar(10))
insert into #T2(Type,Value,Name) values ('Ref1','A','Area 1')
insert into #T2(Type,Value,Name) values ('Ref1','B','Area 2')
insert into #T2(Type,Value,Name) values ('Ref2','A','Dept 1')
insert into #T2(Type,Value,Name) values ('Ref2','B','Dept 2')
insert into #T2(Type,Value,Name) values ('Ref3','A','Type 1')
insert into #T2(Type,Value,Name) values ('Ref3','B','Type 2')
select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
Name3
from #T1 as T1
inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
drop table #T1
drop table #T2|||markc600@.hotmail.com wrote:
> Try this
> [snipped]
> select T1.Amount, t2a.Name as Name1, t2b.Name as Name2, t2c.Name as
> Name3
> from #T1 as T1
> inner join #T2 as t2a on t2a.Type='Ref1' and T1.Ref1=t2a.Value
> inner join #T2 as t2b on t2b.Type='Ref2' and T1.Ref2=t2b.Value
> inner join #T2 as t2c on t2c.Type='Ref3' and T1.Ref3=t2c.Value
> drop table #T1
> drop table #T2
Mark
Many thanks for such a swift reply - just what I needed. Turned the
real world SQL into this (which worked a treat).
Matt
SELECT T1.CODE, T1.NAME AS COSTCENTRE, T2.NAME AS FUNCTION, T3.NAME AS
REGION, T4.NAME AS LNHREGION, T8.NAME AS BRANCH
FROM SADFLDGRIP AS DATA
INNER JOIN
SSRFACC AS CA ON
CA.SUN_DB = 'RIP'
AND
DATA.ACCNT_CODE = CA.ACCNT_CODE
INNER JOIN
SSRFANV AS T1 ON T1.CATEGORY = 'T1'
AND
T1.SUN_DB = 'RIP'
AND
DATA.ANAL_T1 = T1.CODE
INNER JOIN
SSRFANV AS T2 ON T2.CATEGORY = 'T2'
AND
T2.SUN_DB = 'RIP'
AND
DATA.ANAL_T2 = T2.CODE
INNER JOIN
SSRFANV AS T3 ON T3.CATEGORY = 'T3'
AND
T3.SUN_DB = 'RIP'
AND
DATA.ANAL_T3 = T3.CODE
INNER JOIN
SSRFANV AS T4 ON T4.CATEGORY = 'T4'
AND
T4.SUN_DB = 'RIP'
AND
DATA.ANAL_T4 = T4.CODE
INNER JOIN
SSRFANV AS T8 ON T8.CATEGORY = 'T8'
AND
T8.SUN_DB = 'RIP'
AND
DATA.ANAL_T8 = T8.CODE
WHERE DATA.PERIOD >= 2004001 AND DATA.PERIOD <=2005001 AND
CA.ACCNT_TYPE = 'P'
GROUP BY T1.CODE, T1.NAME, T2.NAME, T3.NAME, T4.NAME, T8.NAME
;|||you can try this 3 solutions
(i let myself redefine and rename some of your tables and columns):
SET NOCOUNT ON;
SET ANSI_NULLS ON;
USE YOUR_DB;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='MainData') DROP TABLE MainData;
IF EXISTS(SELECT * FROM YOUR_DB.INFORMATION_SCHEMA.TABLES
WHERE table_name='RefData') DROP TABLE RefData;
CREATE TABLE MainData(
amnt INTEGER NOT NULL,
ref1 CHAR(1) NOT NULL CHECK(ref1 IN('A', 'B')),
ref2 CHAR(1) NOT NULL CHECK(ref2 IN('A', 'B')),
ref3 CHAR(1) NOT NULL CHECK(ref3 IN('A', 'B')));
CREATE INDEX MainData_ref1_Idx ON MainData(ref1);
CREATE INDEX MainData_ref2_Idx ON MainData(ref2);
CREATE INDEX MainData_ref3_Idx ON MainData(ref3);
INSERT INTO MainData
SELECT 100, 'A', 'A', 'A' UNION ALL
SELECT 150, 'A', 'B', 'A' UNION ALL
SELECT 200, 'A', 'B', 'B';
CREATE TABLE RefData(
ref_tp CHAR(4) NOT NULL,
ref_vl CHAR(1) NOT NULL CHECK(ref_vl IN('A', 'B')),
ref_nm CHAR(6) NOT NULL);
CREATE INDEX RefData_ref_tp_Idx ON RefData(ref_tp);
INSERT INTO RefData
SELECT 'Ref1','A','Area 1' UNION ALL
SELECT 'Ref1','B','Area 2' UNION ALL
SELECT 'Ref2','A','Dept 1' UNION ALL
SELECT 'Ref2','B','Dept 2' UNION ALL
SELECT 'Ref3','A','Type 1' UNION ALL
SELECT 'Ref3','B','Type 2';
-- amnt ref_nm1 ref_nm2 ref_nm3
-- 100 Area 1 Dept 1 Type 1
-- 150 Area 1 Dept 2 Type 1
-- 200 Area 1 Dept 2 Type 2
SELECT amnt,
(SELECT R1.ref_nm FROM RefData AS R1
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1) AS ref_nm1,
(SELECT R2.ref_nm FROM RefData AS R2
WHERE R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2) AS ref_nm2,
(SELECT R3.ref_nm FROM RefData AS R3
WHERE R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3) AS ref_nm3
FROM MainData AS M
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM MainData AS M, RefData AS R1, RefData AS R2, RefData AS R3
WHERE R1.ref_tp = 'Ref1' AND R1.ref_vl = M.ref1
AND R2.ref_tp = 'Ref2' AND R2.ref_vl = M.ref2
AND R3.ref_tp = 'Ref3' AND R3.ref_vl = M.ref3
SELECT M.amnt,
R1.ref_nm AS ref_nm1, R2.ref_nm AS ref_nm2, R3.ref_nm AS ref_nm3
FROM RefData AS R3
RIGHT OUTER JOIN RefData AS R2
RIGHT OUTER JOIN RefData AS R1
RIGHT OUTER JOIN MainData AS M
ON R1.ref_vl = M.ref1 AND R1.ref_tp = 'Ref1'
ON R2.ref_vl = M.ref2 AND R2.ref_tp = 'Ref2'
ON R3.ref_vl = M.ref3 AND R3.ref_tp = 'Ref3'
-- WHERE R1.ref_tp = 'Ref1'
-- AND R2.ref_tp = 'Ref2'
-- AND R3.ref_tp = 'Ref3'

JOINING TWO TABLES ERROR

How can i Join these 2 table ? I am getting an error my LEFT OUTER JOIN
DOESNT WORK thanks
SELECT EmployeeNo, Datehired, SectionCode,
Department,Lastname,Firstname
FROM (dbo.Employees H1
GROUP By EmployeeNo, Datehired, SectionCode,
Department,Lastname,Firstname)
dbo.Employees LEFT OUTER JOIN ON dbo.empBenefits.EmployeeNo =
dbo.Employees.EmployeeNo
SELECT EmployeeNo, StartDate,Amount AS OLDBENEFITS,
EndDate,BenefitCode,
(SELECT MAX([amount])
FROM EMPBENEFITS T2
WHERE T2.employeeno = T.employeeno AND
startdate >= '2000-01-01' AND startdate <= '2007-03-30')
AS NEWBENEFITS
FROM dbo.empBenefits T
WHERE(StartDate =(SELECT TOP 1 ([startdate])
FROM EMPBENEFITS T1
WHERE T1.employeeno = T.employeeno AND
Benefitcode = 'HON' AND startdate >= '2000-01-01' AND startdate <=
'2007-03-30'))
GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amountheri wrote:
> How can i Join these 2 table ? I am getting an error my LEFT OUTER JOIN
> DOESNT WORK thanks
> SELECT EmployeeNo, Datehired, SectionCode,
> Department,Lastname,Firstname
> FROM (dbo.Employees H1
> GROUP By EmployeeNo, Datehired, SectionCode,
> Department,Lastname,Firstname)
> dbo.Employees LEFT OUTER JOIN ON dbo.empBenefits.EmployeeNo =
> dbo.Employees.EmployeeNo
> SELECT EmployeeNo, StartDate,Amount AS OLDBENEFITS,
> EndDate,BenefitCode,
> (SELECT MAX([amount])
> FROM EMPBENEFITS T2
> WHERE T2.employeeno = T.employeeno AND
> startdate >= '2000-01-01' AND startdate <= '2007-03-30')
> AS NEWBENEFITS
> FROM dbo.empBenefits T
> WHERE(StartDate =(SELECT TOP 1 ([startdate])
> FROM EMPBENEFITS T1
> WHERE T1.employeeno = T.employeeno AND
> Benefitcode = 'HON' AND startdate >= '2000-01-01' AND startdate <=
> '2007-03-30'))
> GROUP BY EmployeeNo, Amount, BenefitCode, StartDate, EndDate, Amount
It seems like you have two queries here. I can't quess how you intended
to combine them. It would help if you post CREATE TABLE statements for
the tables, some sample data and explain what result you want. Always
tell us the content of any error messages and always say what version
of SQL Server you are using. "Getting an error" is not a helpful
description of your problem!
SELECT E.employeeno, datehired, sectioncode, department, lastname,
firstname
FROM dbo.Employees AS E
LEFT JOIN dbo.empBenefits AS B
ON B.employeeno = E.employeeno ;
SELECT employeeno, startdate, amount AS oldbenefits, enddate,
benefitcode,
(SELECT MAX([amount])
FROM empbenefits AS T2
WHERE T2.employeeno = T.employeeno
AND startdate >= '2000-01-01'
AND startdate <= '2007-03-30') AS newbenefits
FROM dbo.empBenefits T
WHERE startdate =
(SELECT TOP 1 ([startdate])
FROM empbenefits AS T1
WHERE T1.employeeno = T.employeeno
AND benefitcode = 'HON'
AND startdate >= '2000-01-01'
AND startdate <= '2007-03-30')
GROUP BY employeeno, amount, benefitcode, startdate, enddate, amount ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thank you so much for the reply. Im sorry for that I did not mention
the Version .. I am using SQL2005.
The output I need to see is all employees In one row of data
Ex.
EmployeeNo, Lastname, Firstname, SectionCode, DepartmentCode,
Oldbenefits, NewBenefits, StartDate (Columns are displayed in a Single
Row as shown)
(EmployeeNo Lastname, Firstname, SectionCode, DepartmentCode) comes
from Employees Table
(Oldbenefits, NewBenefits,Startdate ) Comes from empBenefits Table
The second query gets the MIN and MAX Benefits from a row of data with
a specified data range.
I tried this query but it doesnt JOIN in my second Select Statement
seems like LEFT OUTER JOIN DOES NOT WORK
SELECT E.employeeno, datehired, sectioncode, department, lastname,
firstname
FROM dbo.Employees E LEFT OUTER JOIN
dbo.empBenefits T ON E.employeeno = T .employeeno
SELECT employeeno, startdate, amount AS
oldbenefits, enddate, benefitcode,
(SELECT
MAX([amount])
FROM
empbenefits AS T2
WHERE
T2.employeeno = T .employeeno AND startdate >= '2000-01-01' AND
startdate <= '2007-03-30') AS newbenefits
FROM dbo.empBenefits T
WHERE startdate =
(SELECT TOP 1
([startdate])
FROM
empbenefits AS T1
WHERE
T1.employeeno = T .employeeno AND benefitcode <> 'HON' AND startdate >=
'2000-01-01' AND startdate <= '2007-03-30')
GROUP BY employeeno, amount, benefitcode,
startdate, enddate, amount
Thanks,
Heri|||Hi David,
Here are the scripts for employees and empbenefits table hope this
could help thanks again
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[empBenefits]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[empBenefits]
GO
CREATE TABLE [dbo].[empBenefits] (
[EmployeeNo] [int] NOT NULL ,
[BenefitCode] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[Amount] [decimal](10, 2) NULL ,
[Status] [tinyint] NOT NULL ,
[Remarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Employees]
GO
CREATE TABLE [dbo].[Employees] (
[EmployeeNo] [int] NOT NULL ,
[LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[MiddleName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Department] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[SectionCode] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
) ON [PRIMARY]
GO

Joining Two Tables

I've been trying to think about how I can do this. I have forums that I have written built around SQL Server. Basicly you have:

-A users Table
-A Posts Table
-A Replies Table.

Posts and replies have very similar structures. I'd like to be able to merge them and pick out the earliest post for said forum.

1 - is there a way to merge them so that the post date for both the replies and posts tables is contained in 1 column. If not is there a better alternative.

I'd also like to add indexing to the posts so I can do paging. Is there a way for me to add an index number to them while I can sort them anyway i want.Yes. You can use the UNION operator to create the reultset you are looking for. See BOL for details, but basically:

SELECT UserID, PostTitle AS Title, PostDate AS PostDate
FROM Posts
WHERE UserID = @.UserID

UNION

SELECT UserID, ReplyTitle AS Title, ReplyDate AS PostDate
FROM Replies
WHERE UserID = @.UserID

ORDER BY PostDate DESC

(...or something along those lines; haven't UNIONed in quite a while...)sql

Joining two tables

I have two tables:
1. Incomes
Income Amount
Salary 10000
Sales 55000
and 2. Expenses

Expense Amount
House Rent 3000
Tution 4000
Fooding 4000
Travelling 2000

I want to join these to tables to form:
Salary 10000 Expense Amount
Sales 55000 House Rent 5000
(null) (null) Tution 4000
(null) (null) Fooding 4000
(null) (null) Travelling 2000

How can it be done. Thanksit cannot be done unless you specify some way of joining them

in other words, what conditions would determine that

-- Salary 10000 is matched with Expense Amount
(seems to be a typo here -- Expense and Amount are column names, not column values)
-- Sales 55000 is matched with House Rent 5000

are you planning any primary keys for these tables?

rudy
http://r937.com/|||There are a couple of ways you can handle this:

1.
select income, amount, 'income' as type from incomes
union
select expense, amount, 'expense' as type from expenses
order by type

2.
select income, amount, '','' from incomes
union
select '','',expense, amount from expenses

Or another variation.

Joining Two tables

hi everyone
i have got 2 tables one is customer and the other is agents. i want to display all the firstnames and lastnames of the customers from the customer table and all the agentnames from the agents table and i wana see all data under one columnIt would help if you described those tables, so now we have to guess how to join those tables. My lucky guess will be that tables look like this:CUSTOMER (cust_id, first_name, last_name, agent_id)
AGENT (agent_id, first_name, last_name)If that's similar to your situation, you might try this query:SELECT c.first_name ||' '||c.last_name ||' - '||
a.first_name ||' '|| a.last_name customer_and_agent
FROM customer c, agent a
WHERE c.agent_id (+) = a.agent_id;Concatenation shows all the data in one column, while outer join shows agents that don't have any customers.

I hope this helps ...|||agreed, table layouts and relationships would have been valuable information

but this sounds more like a UNION than a JOIN to me

select 'customer' as nametype
, lastname ||', '|| firstname as fullname
from customers
union all
select 'agent ' as nametype
, agentname as fullname
from agents

Joining two tables

I imported two excel spreadsheets from excel to Access. Invoice Register 2005 table and Invoice Register 2006 table. These excel files were exports from peachtree.

Each client can have multiple invoices in each of these registers. Each client though does have a unique client numbers but in these tables there are duplicate client numbers because off the multiple invoices created throughout the year.

I created a query linking these two tables together and what is happening when I link the two tables using the Client ID numbers the invoices are listing more than once.

Example there is a client with 3 invoices in 2005 and 3 invoices in 2006 (different invoice numbers) so in stead of listing six invoices for this one client there are nine records. The query shows nine invoices but there are only six for this one client - it is duplicating some of these invoices twice.

What I am trying to show is the client ID and Client name and the invoice register for 2005 and 2006. Is this possible?

Lborshard

I think what you need is to UNION the two table instead of JOIN them.|||

I will read up on UNION and give it a try.

Thank you.

lborshard

|||

phe,

Thanks for the help the Union did work - what a learning experience.

The only thing now, is that I need the 2005 Invoice Column/field and the 2006 Invoice column/ field to be seperate columns as I started with in the import.

The query combined the 2005 and 2006 invoices underneath each other. Is there any way I can list these two columns in the union query?

lborshard

Originally when I linked these two seperate tables together it seemed to copy/duplicate some of the invoice. For example: one client had 3 invoices in 2005 and 3 invoices in 2006 - the client ID was the link - Instead of showing 6 individual invoice for that specific client it showed nine.

Joining two tables

Hi all,
Can anyone tell how to join two tables where first table is in db1
and second table in db2..
Looking forward for ur response..
Thanx in advance...
SELECT *
FROM [DB1].[dbo].[Table1] A
JOIN [DB2].[dbo].[Table2] B
ON A.[Key] = B.[Key]
Nik Marshall-Blank MCSD/MCDBA
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1128077633.696943.139960@.z14g2000cwz.googlegr oups.com...
> Hi all,
> Can anyone tell how to join two tables where first table is in db1
> and second table in db2..
> Looking forward for ur response..
> Thanx in advance...
>
|||Hi marshall...
Thanx for ur response...

joining two tables

Hi
let say I have record:s in two tables, namely tblTable1 and tblTable2
tblTable1:
Year SerialA SerialB
1998 1 3
2000 3 2
1999 2 2
2001 5 3
1998 1 1
1999 2 1
2001 3 2
tblTable2:
Year SerialA SerialB
1998 2 0
1999 1 2
1999 0 2
2001 3 3
1998 2 2
1999 0 1
2001 4 2
I want to have it "Group by Year Order by Year" by joining these two tables
and sum each serial for each year.
The output will be
Year SerialA SerialB
1998 6 6
1999 5 8
2000 3 2
2001 15 10
What will be the SELECT statement to achieve the ouput as mentioned above ?
Thank you.
Regards.SELECT Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
FROM (select * from tblTable1
UNION ALL
select * from tblTable2) as T
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:

>Hi
>let say I have record:s in two tables, namely tblTable1 and tblTable2
>tblTable1:
>Year SerialA SerialB
>1998 1 3
>2000 3 2
>1999 2 2
>2001 5 3
>1998 1 1
>1999 2 1
>2001 3 2
>tblTable2:
>Year SerialA SerialB
>1998 2 0
>1999 1 2
>1999 0 2
>2001 3 3
>1998 2 2
>1999 0 1
>2001 4 2
>
>I want to have it "Group by Year Order by Year" by joining these two tables
>and sum each serial for each year.
>The output will be
>Year SerialA SerialB
>1998 6 6
>1999 5 8
>2000 3 2
>2001 15 10
>
>What will be the SELECT statement to achieve the ouput as mentioned above ?
>
>Thank you.
>Regards.
>|||are you sure this is working ?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
> SELECT Year,
> SUM(SerialA) as SerialA,
> SUM(SerialB) as SerialB
> FROM (select * from tblTable1
> UNION ALL
> select * from tblTable2) as T
> Roy Harvey
> Beacon Falls, CT
> On Thu, 22 Jun 2006 23:01:10 +0800, "magix" <magix@.asia.com> wrote:
>|||Did you try it and get unexpected results? Perhaps I do not
understand your requirements. I did not build your tables and write
INSERTS for your test data to test it, but yes, I think it works.
It is certainly not the only way to write it. If both tables always
have exactly the same years, one alternative would be:
SELECT T1.Year,
T1.SerialA + T2.SerialA as SerialA,
T1.SerialB + T2.SerialB as SerialB
FROM (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable1
group by Year) as T1
JOIN (select Year,
SUM(SerialA) as SerialA,
SUM(SerialB) as SerialB
from tblTable2
group by Year) as T2
ON T1.Year = T2.Year
Roy Harvey
Beacon Falls, CT
On Thu, 22 Jun 2006 23:33:55 +0800, "magix" <magix@.asia.com> wrote:

>are you sure this is working ?
>"Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>|||How could we possible know?
You did not provide table DDL, and you did not provide data in the form of
INSERT statements. so the best you should expect is suggestions in the
'right' direction.
If you want better help, provide better information.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"magix" <magix@.asia.com> wrote in message news:449ab86a_1@.news.tm.net.my...
> are you sure this is working ?
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:6pcl92hk7dk5s3kk4cq7ehhn6ei40ov9n3@.
4ax.com...
>sql

Joining two tables

Hi all,
Can anyone tell how to join two tables where first table is in db1
and second table in db2..
Looking forward for ur response..
Thanx in advance...SELECT *
FROM [DB1].[dbo].[Table1] A
JOIN [DB2].[dbo].[Table2] B
ON A.[Key] = B.[Key]
Nik Marshall-Blank MCSD/MCDBA
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1128077633.696943.139960@.z14g2000cwz.googlegroups.com...
> Hi all,
> Can anyone tell how to join two tables where first table is in db1
> and second table in db2..
> Looking forward for ur response..
> Thanx in advance...
>|||Hi marshall...
Thanx for ur response...

Joining two tables

Hi all,
Can anyone tell how to join two tables where first table is in db1
and second table in db2..
Looking forward for ur response..
Thanx in advance...SELECT *
FROM [DB1].[dbo].[Table1] A
JOIN [DB2].[dbo].[Table2] B
ON A.[Key] = B.[Key]
--
Nik Marshall-Blank MCSD/MCDBA
"Prince" <princevictor.moses@.gmail.com> wrote in message
news:1128077633.696943.139960@.z14g2000cwz.googlegroups.com...
> Hi all,
> Can anyone tell how to join two tables where first table is in db1
> and second table in db2..
> Looking forward for ur response..
> Thanx in advance...
>|||Hi marshall...
Thanx for ur response...

Joining two SQL queries with different conditions

Ok I am stumped on this. i tried an inner join where posted_jrnl_line as a
and posted_jrnl_line as b, also tried subqueries where (select jrnlId [then
conditions]) to no avail...
I have two queries that execute perfectly. the two queries are retrieving
data from one table but the conditions vary between the queries.
I would like to roll the two queries into one queries so I can create a
report for users.
Query #1
select
jrnl_id as 'System Jrnl',
jrnl_user_alpha_fld_3 as 'Proj Jrnl',
SUM(trans_amt) as 'Proj Jrnl Total'
from
DBSjepc.dbo.posted_jrnl_line
where
dbs_gl_account = '3475'
and jrnl_origin_code = 'PJ'
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_user_alpha_fld_3 like 'TBC%'
Group by
jrnl_id, jrnl_user_alpha_fld_3)
So the output of Query 1 is
System Jrnl | Proj Jrnl | Proj Jrnl Total
Query #2
select jrnl_id as 'Ldr Jrnl',
jrnl_user_alpha_fld_3 as 'Ldr None',
SUM(trans_amt) as 'Ldr Jrnl Total'
from DBSjepc.dbo.posted_jrnl_line
where dbs_gl_account = '3475'
and not jrnl_origin_code in ('PJ','PR')
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_id like 'TBC%'
Group by jrnl_id,jrnl_user_alpha_fld_3
So the output of Query 2 is
Ldr Jrnl | Ldr None | Ldr Jrnl Total
I would like the output to be
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
Total
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1I noticed that the WHERE criteria is not the same. Which criteria you you
want?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message
news:621d8e5ca05d2@.uwe...
> Ok I am stumped on this. i tried an inner join where posted_jrnl_line as
> a
> and posted_jrnl_line as b, also tried subqueries where (select jrnlId
> [then
> conditions]) to no avail...
> I have two queries that execute perfectly. the two queries are retrieving
> data from one table but the conditions vary between the queries.
> I would like to roll the two queries into one queries so I can create a
> report for users.
> Query #1
> select
> jrnl_id as 'System Jrnl',
> jrnl_user_alpha_fld_3 as 'Proj Jrnl',
> SUM(trans_amt) as 'Proj Jrnl Total'
> from
> DBSjepc.dbo.posted_jrnl_line
> where
> dbs_gl_account = '3475'
> and jrnl_origin_code = 'PJ'
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_user_alpha_fld_3 like 'TBC%'
> Group by
> jrnl_id, jrnl_user_alpha_fld_3)
> So the output of Query 1 is
> System Jrnl | Proj Jrnl | Proj Jrnl Total
>
> Query #2
> select jrnl_id as 'Ldr Jrnl',
> jrnl_user_alpha_fld_3 as 'Ldr None',
> SUM(trans_amt) as 'Ldr Jrnl Total'
> from DBSjepc.dbo.posted_jrnl_line
> where dbs_gl_account = '3475'
> and not jrnl_origin_code in ('PJ','PR')
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_id like 'TBC%'
> Group by jrnl_id,jrnl_user_alpha_fld_3
> So the output of Query 2 is
> Ldr Jrnl | Ldr None | Ldr Jrnl Total
> I would like the output to be
> System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
> Total
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi arnie,
that is correct the where clauses are different by the jrnl_origin field.
Query #1 must contain PJ for its output and Query #2 must NOT contain PJ and
PR for its output
Arnie Rowland wrote:
>I noticed that the WHERE criteria is not the same. Which criteria you you
>want?
>
>[quoted text clipped - 50 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||You have in effect two distinctly different queries. Your desired output pro
vides nothing in common between the two queries.
Is all the data on a single line somehow related?
Are you trying for:
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
OR
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data data data data
If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even A
ccess.
From the limited information, I don't see how you will get to the second exa
mple. If you would send in the table DDL and some represtentative sample dat
a in the form of INSERT statements -you may be able to get more help.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message news:621dd08c3fcbe@.uwe...darkred">
> Hi arnie,
>
> that is correct the where clauses are different by the jrnl_origin field.
> Query #1 must contain PJ for its output and Query #2 must NOT contain PJ a
nd
> PR for its output
>
> Arnie Rowland wrote:
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi Arnie,
Actually a mix but Option #1 is what is what I am trying to strive for. the
output does have a relation but not a database one. Users enter a project
journal must use the same journal ID when making their ledger journal.
This is desired.
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data data NULL data
However if placed on two lines that is desirable as well: see below
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
Arnie Rowland wrote:
>You have in effect two distinctly different queries. Your desired output pr
ovides nothing in common between the two queries.
>Is all the data on a single line somehow related?
>Are you trying for:
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data NULL NULL NULL
>NULL NULL NULL data data data
>OR
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data data data data
>If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even
Access.
>From the limited information, I don't see how you will get to the second ex
ample. If you would send in the table DDL and some represtentative sample da
ta in the form of INSERT statements -you may be able to get more help.
>Regards,
>
>[quoted text clipped - 10 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

joining two results

I have two sets of results built up. Each set has a state value. Although one set has 38 states in the results while the other has 23. Is it possible to join these sets and show a result for each of the 38 states even if there is not a corresponding value in the other set. All attempts I've made lose the 15 states that do not appear in the other set. Any help would be great. Thanks in advanceIf your database engine supports LEFT JOIN, or better yet FULL JOIN, then you are in business. If not, you can kind of "fake it" using a sub-select, but it is hokey.

-PatP

Joining two results

I have these results:
ID Worked
1 25
2 30
5 20
8 32
9 27
10 26
ID Spent
1 35
2 24
3 22
4 50
5 30
6 28
Required result:
ID Worked Spent
1 25 35
2 30 24
3 0 22
4 0 50
5 20 30
6 0 28
8 32 0
9 27 0
10 26 0
I think I can make it but it will be complicated, am I missing
something or isn't there an easy way to do this?
Thanks in advance,
Stijn Verrept.Stijn,
Try:
SELECT T1.ID, ISNULL(WORKED,0)AS 'WORKED', ISNULL(SPENT,0)AS 'SPENT'
FROM T1 LEFT JOIN T2
ON T1.ID = T2.ID
UNION
SELECT T2.ID, ISNULL(WORKED,0), ISNULL(SPENT,0)
FROM T2 LEFT JOIN T1
ON T2.ID = T1.ID
HTH
Jerry
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:8qOdnfs8vuCJGMjenZ2dnUVZ8qCdnZ2d@.sc
arlet.biz...
>I have these results:
> ID Worked
> 1 25
> 2 30
> 5 20
> 8 32
> 9 27
> 10 26
> ID Spent
> 1 35
> 2 24
> 3 22
> 4 50
> 5 30
> 6 28
>
> Required result:
> ID Worked Spent
> 1 25 35
> 2 30 24
> 3 0 22
> 4 0 50
> 5 20 30
> 6 0 28
> 8 32 0
> 9 27 0
> 10 26 0
> I think I can make it but it will be complicated, am I missing
> something or isn't there an easy way to do this?
>
> Thanks in advance,
> Stijn Verrept.|||Stijn,
Depending on the structure of your data storage there are a number of differ
ent
way to go about this. One possibility is to try:
1. Assume the data is stored in 2 tables (workedTable and spentTable).
2. Create a list of ids by unioning a distinct list of id's from both tables
.
3. Left join this list with workedTable.
4. Left join this with spentTable.
5. Dont forget to isnull the values to return 0's instead of nulls
Code would look something like:
select idTable.ID, isNull(wTable.worked,0), isNull(sTable.spent,0) from
(select distinct id from workedTable union select dictinct if from spentTabl
e)
idTable
left join workedTable wTable on idTable.id=wTable.id
left join spentTable sTable on idTable.id=sTable.id
I'm writing this code off the top of my head, so it may not be exact, byt
hopefully it should get across the idea.
Let me know if you have any success.
Regards,
Andrew McNerlin

> I have these results:
> ID Worked
> 1 25
> 2 30
> 5 20
> 8 32
> 9 27
> 10 26
> ID Spent
> 1 35
> 2 24
> 3 22
> 4 50
> 5 30
> 6 28
> Required result:
> ID Worked Spent
> 1 25 35
> 2 30 24
> 3 0 22
> 4 0 50
> 5 20 30
> 6 0 28
> 8 32 0
> 9 27 0
> 10 26 0
> I think I can make it but it will be complicated, am I missing
> something or isn't there an easy way to do this?
> Thanks in advance,
> Stijn Verrept.
>|||Jerry Spivey wrote:

> Stijn,
> Try:
> SELECT T1.ID, ISNULL(WORKED,0)AS 'WORKED', ISNULL(SPENT,0)AS 'SPENT'
> FROM T1 LEFT JOIN T2
> ON T1.ID = T2.ID
> UNION
> SELECT T2.ID, ISNULL(WORKED,0), ISNULL(SPENT,0)
> FROM T2 LEFT JOIN T1
> ON T2.ID = T1.ID
That indeed works out nicely and isn't too complicated! Thanks.|||You could also do it like so:
SELECT COALESCE(T1.ID, T2.ID) as ID,
COALESCE(WORKED,0)AS 'WORKED',
COALESCE(SPENT,0) AS 'SPENT'
FROM T1 FULL OUTER JOIN T2
ON T1.ID = T2.ID
It may perform better if you have a large number of rows.|||Stu wrote:

> You could also do it like so:
> SELECT COALESCE(T1.ID, T2.ID) as ID,
> COALESCE(WORKED,0)AS 'WORKED',
> COALESCE(SPENT,0) AS 'SPENT'
> FROM T1 FULL OUTER JOIN T2
> ON T1.ID = T2.ID
> It may perform better if you have a large number of rows.
Thanks!sql