I have a mutli-valued, (cascading? dependent on other parameter
values) parameter whose values are retrieved from a database query. I
want to set the default value of the parameter to be the same as the
"Select All" function. How can I acheive this?
To explain, I have two budget reports. The first is a summary, which
groups lines by expense codes and then displays a total. Each dollar
figure is set to click through to the detail report, and filter the
results based on the expense code from the line clicked on. However,
if the user clicks on the total figure (all expense codes), the
resulting detail report only shows the detail for the first object code
in the list. It should display the detail for all object codes.
Thanks -
SarahSarah,
<sarah.lambrite@.gmail.com> wrote in message
news:1158007893.402465.74110@.b28g2000cwb.googlegroups.com...
>I have a mutli-valued, (cascading? dependent on other parameter
> values) parameter whose values are retrieved from a database query. I
> want to set the default value of the parameter to be the same as the
> "Select All" function. How can I acheive this?
SELECT 0 as Value, 'Select All' as Description
UNION ALL
SELECT ID as Value, Columnname as Description FROM YourTable
When you set =0 as your default value, you should be fine.
> To explain, I have two budget reports. The first is a summary, which
> groups lines by expense codes and then displays a total. Each dollar
> figure is set to click through to the detail report, and filter the
> results based on the expense code from the line clicked on. However,
> if the user clicks on the total figure (all expense codes), the
> resulting detail report only shows the detail for the first object code
> in the list. It should display the detail for all object codes.
It looks like RS is having troubles to determine what to put in the
parameter, I think you have the parameter set up to get the contents of the
field (expense code) of the line you click on. But on the total line there
is no such field. This can be solved by altering your subreport parameters.
Set them up so that the subreport gives the details of all the expense
codes.
Perry
> Thanks -
> Sarah
>sql
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Joins??
Hi,
I am stuck with a problem...
I have to query dat from two tables...
PO_hdr and po_addl_cost
now some po's have additional costs and if they do have there will be an entry in po_addl_cost table. They are linked via the PO_GRP_NO.
Now I want to get an extract of data of specific fields..for all po's
I want the extract to show
po_no po_desc po_cost po_addl_costid po_addl_cost_value
The first three fields are from po_hdr and the last two from po_addl_cost
now if there are no entries for that particular po_grp_no i want the two fields blank but still want the other data.
This is my query:
select po.po_no,po.PO_PROJ_NM,po.LOGIN_ID,addl.PO_ADDL_CO ST_TYPE_ID,addl.PO_ADDL_COST_BUY_PRICE from po_hdr po,po_addl_cost_dtl addl
where
po.SITE_ID=41
And po.PO_NO in(287,58)
and po.STATUS_CD=5
and addl.SITE_ID=41
and addl.STATUS = 'A'
and addl.PO_GRP_NO=po.PO_GRP_NO
Pleaseeeeeeeeeeee help!!!select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po
LEFT OUTER JOIN
po_addl_cost_dtl addl ON
addl.PO_GRP_NO=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID=41 AND
addl.STATUS = 'A';|||Thanks , but I am getting an error when trying to execute this using toad ...it gives ORA-00933 sql comman not properly ended ...highlighting "LEFT"|||just a guess but perhaps your version of oracle does not support LEFT OUTER syntax
you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on
(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)|||All sarcasm welcome...
but I amstill having issues...
first of all from what i remember the query with (+) goes like this
select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po ,po_addl_cost_dtl addl where
addl.PO_GRP_NO=po.PO_GRP_NO (+)
AND po.SITE_ID=41
AND po.PO_NO in(287,58) AND po.STATUS_CD=5
AND addl.SITE_ID=41 AND addl.STATUS = 'A';
I have absolutely no idea of joins...but this doesnt seem to retireve two rows...which is what i want.
it gives just one row po_no of which is present in the addl_cost table.|||Originally posted by r937
you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on
(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)
It goes on the "outer" (dark) side:
select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po,
po_addl_cost_dtl addl
where
addl.PO_GRP_NO(+)=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID(+)=41 AND
addl.STATUS (+)= 'A';
But tell me: what is "LEFT" about an outer join? Especially when if written on one line the "outer" table appears on the right... ;o)|||That worked!!! thanks a lot!!!!!|||dunno which one you'd call the outer table, but it's trivial to decide which one's the left table
here, give it a try --
... FROM FOO LEFT OUTER JOIN BAR
now, you've got FOO on the left, and BAR on the right, right?
so, um, FOO is the left table and BAR is the right table
gee i hope i've got that right :cool:
i know it's probably confusing because when i write sql i never put them on the same line, i always write them on separate lines like this --
FROM FOO
LEFT OUTER
JOIN BAR
but that's because i'm an old keyboard jockey, and when i edit text, for example to replace INNER with LEFT OUTER as sometimes is necessary, then i use the arrow keys to position myself on that line, press the Home key if i'm not at the front of the line, and then while pressing the shift key, arrow down to highlight the entire line, and begin typing the replacement text
i don't use a mouse for text editing, and consequently prefer to have stuff on multiple source lines|||Hmm, maybe I've always had it wrong about what the word "outer" really means in this context. I would have called BAR the "outer" table in your example, because in my warped mind you sort of stick the matching rows from BAR on the "outside" of the FOO records...?
But if LEFT OUTER implies that the "outer" table is on the left (i.e FOO), then perhaps the analogy is more with program logic:
-- Outer query
for foo_row in (select * from foo) loop
-- Inner query
begin
select * into bar_row from bar where ...;
exception
when no_data_found then
bar_row := null;
end;
Display(foo_row, bar_row);
end loop;
Presumably there is a RIGHT OUTER that does the opposite?|||yes, RIGHT OUTER is the opposite of LEFT OUTER
did not really understand your code, there is no looping in sql ;)
i would not get into the semantic morass of which one to call the outer table, since in an outer join, one of the tables brings a few extra rows to the table (if you'll pardon the pun), i.e. extra rows which aren't there in the inner join, so these extra rows would be outside the inner rows, and since in a LEFT join they come from the left table, it might make more sense to call the left table the outer table, if you know what i mean
in any case, like i said, i don't call either of them the outer table, i just use the words left and right, because there's no ambiguity there
sample data:
Pets
1 dog
2 cat
3 bird
4 ferret
People
35 curly
38 larry
39 moe
PeoplePets
35 2
35 3
39 1
list all pets, and their people if any (RIGHT join) --
moe dog
curly cat
curly bird
NULL ferret
see this other thread (http://www.dbforums.com/showthread.php?threadid=976339&postid=3597190#post3597190) for LEFT and INNER joins|||Well, my code was supposed to represent what SQL might be doing "under the covers". Or at least, the procedural code you could write to simulate an outer join.
Yes, I agree there is nothing ambigous about LEFT and RIGHT, but then there is nothing particularly meaningful either:
Originally posted by r937
... FROM FOO LEFT OUTER JOIN BAR
now, you've got FOO on the left, and BAR on the right, right?
so, um, FOO is the left table and BAR is the right table
My response to that is:
Originally posted by me
... FROM FOO RIGHT OUTER JOIN BAR
now, you've got FOO on the left, and BAR on the right, right?
so, um, FOO is the left table and BAR is the right table
What's the difference? ;o)
I am sure that the word OUTER must be intended to convey some meaning, but I am no longer so sure what that meaning is...|||i wrote
... FROM FOO LEFT OUTER JOIN BAR
and you suggested
... FROM FOO RIGHT OUTER JOIN BAR
and then asked "What's the difference?"
well, the difference is, the first is a left outer join, and the second is a right outer join
did my people/pets example not help?
lemme know when you want to get into the FULL OUTER JOIN
:cool:|||oh, and by the way, i never write RIGHT OUTER joins anyway
i always re-write them as LEFT OUTER joins
that's because
... FROM FOO RIGHT OUTER JOIN BAR
is exactly equivalent to
... FROM BAR LEFT OUTER JOIN FOO
helps?|||I have absolutely no problem understanding what LEFT, RIGHT and FULL outer joins do, I just don't quite understand why LEFT and RIGHT are so named!
I am stuck with a problem...
I have to query dat from two tables...
PO_hdr and po_addl_cost
now some po's have additional costs and if they do have there will be an entry in po_addl_cost table. They are linked via the PO_GRP_NO.
Now I want to get an extract of data of specific fields..for all po's
I want the extract to show
po_no po_desc po_cost po_addl_costid po_addl_cost_value
The first three fields are from po_hdr and the last two from po_addl_cost
now if there are no entries for that particular po_grp_no i want the two fields blank but still want the other data.
This is my query:
select po.po_no,po.PO_PROJ_NM,po.LOGIN_ID,addl.PO_ADDL_CO ST_TYPE_ID,addl.PO_ADDL_COST_BUY_PRICE from po_hdr po,po_addl_cost_dtl addl
where
po.SITE_ID=41
And po.PO_NO in(287,58)
and po.STATUS_CD=5
and addl.SITE_ID=41
and addl.STATUS = 'A'
and addl.PO_GRP_NO=po.PO_GRP_NO
Pleaseeeeeeeeeeee help!!!select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po
LEFT OUTER JOIN
po_addl_cost_dtl addl ON
addl.PO_GRP_NO=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID=41 AND
addl.STATUS = 'A';|||Thanks , but I am getting an error when trying to execute this using toad ...it gives ORA-00933 sql comman not properly ended ...highlighting "LEFT"|||just a guess but perhaps your version of oracle does not support LEFT OUTER syntax
you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on
(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)|||All sarcasm welcome...
but I amstill having issues...
first of all from what i remember the query with (+) goes like this
select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po ,po_addl_cost_dtl addl where
addl.PO_GRP_NO=po.PO_GRP_NO (+)
AND po.SITE_ID=41
AND po.PO_NO in(287,58) AND po.STATUS_CD=5
AND addl.SITE_ID=41 AND addl.STATUS = 'A';
I have absolutely no idea of joins...but this doesnt seem to retireve two rows...which is what i want.
it gives just one row po_no of which is present in the addl_cost table.|||Originally posted by r937
you will need to use that silly plus sign in parentheses and i'm sorry i can't remember which side of the equal sign it goes on
(sorry for the sarcasm but the sql standard for JOIN syntax has been out for, what, over a decade? and oracle finally decided to implement it in oracle 9?)
It goes on the "outer" (dark) side:
select po.PO_NO, po.PO_PROJ_NM, po.LOGIN_ID, addl.PO_ADDL_COST_TYPE_ID, addl.PO_ADDL_COST_BUY_PRICE
from po_hdr po,
po_addl_cost_dtl addl
where
addl.PO_GRP_NO(+)=po.PO_GRP_NO AND
po.SITE_ID=41 AND
po.PO_NO in(287,58) AND
po.STATUS_CD=5 AND
addl.SITE_ID(+)=41 AND
addl.STATUS (+)= 'A';
But tell me: what is "LEFT" about an outer join? Especially when if written on one line the "outer" table appears on the right... ;o)|||That worked!!! thanks a lot!!!!!|||dunno which one you'd call the outer table, but it's trivial to decide which one's the left table
here, give it a try --
... FROM FOO LEFT OUTER JOIN BAR
now, you've got FOO on the left, and BAR on the right, right?
so, um, FOO is the left table and BAR is the right table
gee i hope i've got that right :cool:
i know it's probably confusing because when i write sql i never put them on the same line, i always write them on separate lines like this --
FROM FOO
LEFT OUTER
JOIN BAR
but that's because i'm an old keyboard jockey, and when i edit text, for example to replace INNER with LEFT OUTER as sometimes is necessary, then i use the arrow keys to position myself on that line, press the Home key if i'm not at the front of the line, and then while pressing the shift key, arrow down to highlight the entire line, and begin typing the replacement text
i don't use a mouse for text editing, and consequently prefer to have stuff on multiple source lines|||Hmm, maybe I've always had it wrong about what the word "outer" really means in this context. I would have called BAR the "outer" table in your example, because in my warped mind you sort of stick the matching rows from BAR on the "outside" of the FOO records...?
But if LEFT OUTER implies that the "outer" table is on the left (i.e FOO), then perhaps the analogy is more with program logic:
-- Outer query
for foo_row in (select * from foo) loop
-- Inner query
begin
select * into bar_row from bar where ...;
exception
when no_data_found then
bar_row := null;
end;
Display(foo_row, bar_row);
end loop;
Presumably there is a RIGHT OUTER that does the opposite?|||yes, RIGHT OUTER is the opposite of LEFT OUTER
did not really understand your code, there is no looping in sql ;)
i would not get into the semantic morass of which one to call the outer table, since in an outer join, one of the tables brings a few extra rows to the table (if you'll pardon the pun), i.e. extra rows which aren't there in the inner join, so these extra rows would be outside the inner rows, and since in a LEFT join they come from the left table, it might make more sense to call the left table the outer table, if you know what i mean
in any case, like i said, i don't call either of them the outer table, i just use the words left and right, because there's no ambiguity there
sample data:
Pets
1 dog
2 cat
3 bird
4 ferret
People
35 curly
38 larry
39 moe
PeoplePets
35 2
35 3
39 1
list all pets, and their people if any (RIGHT join) --
moe dog
curly cat
curly bird
NULL ferret
see this other thread (http://www.dbforums.com/showthread.php?threadid=976339&postid=3597190#post3597190) for LEFT and INNER joins|||Well, my code was supposed to represent what SQL might be doing "under the covers". Or at least, the procedural code you could write to simulate an outer join.
Yes, I agree there is nothing ambigous about LEFT and RIGHT, but then there is nothing particularly meaningful either:
Originally posted by r937
... FROM FOO LEFT OUTER JOIN BAR
now, you've got FOO on the left, and BAR on the right, right?
so, um, FOO is the left table and BAR is the right table
My response to that is:
Originally posted by me
... FROM FOO RIGHT OUTER JOIN BAR
now, you've got FOO on the left, and BAR on the right, right?
so, um, FOO is the left table and BAR is the right table
What's the difference? ;o)
I am sure that the word OUTER must be intended to convey some meaning, but I am no longer so sure what that meaning is...|||i wrote
... FROM FOO LEFT OUTER JOIN BAR
and you suggested
... FROM FOO RIGHT OUTER JOIN BAR
and then asked "What's the difference?"
well, the difference is, the first is a left outer join, and the second is a right outer join
did my people/pets example not help?
lemme know when you want to get into the FULL OUTER JOIN
:cool:|||oh, and by the way, i never write RIGHT OUTER joins anyway
i always re-write them as LEFT OUTER joins
that's because
... FROM FOO RIGHT OUTER JOIN BAR
is exactly equivalent to
... FROM BAR LEFT OUTER JOIN FOO
helps?|||I have absolutely no problem understanding what LEFT, RIGHT and FULL outer joins do, I just don't quite understand why LEFT and RIGHT are so named!
Joins Vs Where clause - Performance Query
Hi There !!
To finetune performance for some of our queries,
I have come across suggestions to use
- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases
Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ??
Also,
I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer
tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )
USING A WHERE CLAUSE and Alias
--------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null
USING A JOIN
------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL
******
I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:
Any comments/ suggestions.
Thanks,
Have a great time
-Ranjit.
------------
It pays to be honest to your DBAmy experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.
If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.
Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.|||Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).
and avoid using AliasesNope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not. :)
I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
http://www.sql-server-performance.com/articles_performance.asp
HTH|||avoiding aliases is not "reasonable"
:)|||Some people think aliases make code easier to read, blindman does not. :)
My reputation preceeds me.
But even I don't claim the aliases hurt performance.
To finetune performance for some of our queries,
I have come across suggestions to use
- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases
Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ??
Also,
I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer
tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )
USING A WHERE CLAUSE and Alias
--------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null
USING A JOIN
------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL
******
I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:
Any comments/ suggestions.
Thanks,
Have a great time
-Ranjit.
------------
It pays to be honest to your DBAmy experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.
If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.
Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.|||Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).
and avoid using AliasesNope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not. :)
I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
http://www.sql-server-performance.com/articles_performance.asp
HTH|||avoiding aliases is not "reasonable"
:)|||Some people think aliases make code easier to read, blindman does not. :)
My reputation preceeds me.
But even I don't claim the aliases hurt performance.
Joins query help
Hi all,
Just after some help with a query (Stored Procedure) I've managed to get wrapped round my head.
The DB is as such:
COMPANY
Company_id
Company_name
COMPANY_GROUP
Group_id
Group_name
USER
User_id
User_name
Bridging tables
COMPANY_GROUP_BRIDGE
company_id
group_id
USER_COMPANY_BRIDGE
user_id
company_id
Basically, the only parameter I have for the query is a User_id.
I need to get the Group linked to the User and return all the companies within that group.
I'vetried reading up on all the join types again but have just got thiscompletely wrapped round my neck. I keep thinking along the lines ofSELECT all the companies linked to all the groups linked to all thecompanies linked to the User_id :s I must be able to dothis without using two Company tables...?
Any help much appreciated,
Pete
I don't think the table structure is good. You only need one bridge table in stead of two. In this one table, just put company_id, group_id, and user_id together. Otherwise, you will join too much.
|||There seems to be a contradiction here. You wrote
"I need to get the Group linked to the User and return all the companies within that group."
But you don't have any table that stores the users assigned to a company group.
In case a user belongs to a group and not a company, create a table that will store user id and group id.
Then you can write a query to get all the companies linked to the group to which the user belongs
|||
Cheers for the replies guys, you've pretty much asserted what I've been dreading all along.
The DB is a complete messed up and looks like I'm gonna have to completely overhaul it :/
(BTW:It is not currently possible for a user to be linked to a group but nota company. The groups are not in there for this purpose - In fact Imnot sure why there are there!)
|||Hmm OK,
Any reccomendations on what a DB 'should' look like based on thisveryextremely light spec:
A Distributor can have many Resellers
A Reseller can have many Groups
A Group can have many Companies
A Company can have many Users
A User may belong to many Companies
(Looking at it - it has been designed so that a Company can belong to many Groups but I dont think that would ever happen )
I think the problem originally was that a User may belong to certaincompanies within a group but not neccessarilly all of them - hence theUser is linked to individual companies rather than a Group.
Also, in the admin backend the problem is which companies to show for options such as edit etc.
A Group Administrator may see all the companies within a group but aCompany Administrator may only see the Companies they are associatedwith in the bridge table. Looks like I'll need the User's'access_level' in all the SProcs.
OK, back to basics. My head hurts.
|||
It is often helpful to think about it this way: every noun is a table, so:
Distributor table
Reseller table
Group table
Company table
User tables
A Distributor can have many Resellers
A Reseller can have many Groups
A Group can have many Companies
A Company can have many Users
A User may belong to many Companies
Every time you write, "can have many" it implies the presence of a foreign key (FK). Many-to-many relationships (company-user) requires a cross reference table. So:
Distributor table
- Distributor ID (probably an identity column)
- Reseller ID (FK to Reseller table)
- other fields
Reseller table
- Reseller ID (probably an identity column)
- Group ID (FK to Group table)
- other fields
Group Table
- Group ID (probably an identity column)
- Company ID (FK to Company table)
- other fields
Company Table
- Company ID (probably an identity column)
- other fields
User Table
- User ID (probably an identity column)
- other fields
Company-User-Cross-Reference Table
- Company ID (FK to Company table)
- User Id (FK to User Table)
NOTE: The Primay Key of this table is Company ID + User ID
|||
Hi David, thanks for that!
That does reassure me that I am on the right track with my structure as that is the way I have gone about things.
I think I rushed into it though and ended up creating astructure where a group could belong to multiple resellers which iswrong (and also that resellers could belong to multiple Distributorswhich is also wrong) So I shall remove the cross-reference tables forDistributors and Resellers.
However, I think I need to make the following minor changes to your suggestion:
Group Table
- Group ID (probably an identity column)
- other fields
Company Table
- Company ID (probably an identity column)
- Group ID (FK to Group table)
- other fields
This is so that a Group can have many Companies. (and a Company can only belong to one group)
(And also changes so Reseller Table contains the DistributorID and the Group table contains the ResellerID)
Could you just clear something up for me though please: Youappear to have placed the CompanyID in the Group table - would this notmean that a Group can only have one company? Is it not correct to putthe GroupID in the Company table. Then each Company can be linked to aparticular group?
What do you think..?
Again, thanks for your advice it is greatly appreciated,
Pete
|||
pete_m:
Could you just clear something up for me though please: You appear to have placed the CompanyID in the Group table - would this not mean that a Group can only have one company? Is it not correct to put the GroupID in the Company table. Then each Company can be linked to a particular group?
Yes, you're right, I was doing this too fast. I think you have the idea now.
Joins Position
Hi
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
Lalit
Can you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit
|||This is SQL Statement that performed fine.
I interchanged second and third JOINs. Both are Linked
with the first one.
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Following SQL Statement is that one that had the problem
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Lalit
|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>
sql
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
Lalit
Can you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit
|||This is SQL Statement that performed fine.
I interchanged second and third JOINs. Both are Linked
with the first one.
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Following SQL Statement is that one that had the problem
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Lalit
|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>
sql
Joins Position
Hi
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
LalitCan you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
LalitCan you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>
Joins Performance Problem
Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation â'
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
-----
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> -----
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in message
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>> Hello,
>> It is taking too long to run the following query:
>> Note: I have indexes on all of the columns in conditions. My temp db
>> size
>> is 18 GB. Pds_txn table size is 165 GB.
>> The execution plan showing:
>> Table pool/easer spool operation -
>> Row Count: 9 M
>> Disk i/o: 11k
>> Row size: 1089
>> Estimated cost: 11 k (57%)
>> CPU cost: 3.3
>> Sub tree cost: 20 K
>> Any help/hint will be appreciated.
>> Thanks,
>> Alim
>> -----
>>
>> FROM
>> dbo.pds_txn T1
>> INNER JOIN
>> dbo.GROUPS T2 ON
>> T1.GROUP_ID = T2.ID_200
>> INNER JOIN
>> dbo.DIVISIONS T3 ON
>> T1.DIVISION = T3.ID_102
>> INNER JOIN
>> dbo.BILLING_AREAS T4 ON
>> T1.BILLING_AREA = T4.ID_202
>> INNER JOIN
>> dbo.PROVIDERS T6 ON
>> T1.PROVIDER = T6.ID_3
>> INNER JOIN
>> dbo.LOCATIONS T7 ON
>> T1.LOCATION = T7.ID_100
>> INNER JOIN
>> dbo.PROCEDURES T8 ON
>> T1.[PROCEDURE] = T8.ID_1
>> INNER JOIN
>> dbo.FSC T9 ON
>> T1.ORIG_FSC = T9.ID_19
>> INNER JOIN
>> dbo.DIAGNOSIS T10 ON
>> T1.TXN_DX_1 = T10.ID_36
>> INNER JOIN
>> dbo.pds_invoice T11 ON
>> T1.INVOICE_NUM = T11.INVOICE_NUM AND
>> T1.GROUP_ID = T11.GROUP_ID
>> LEFT OUTER JOIN
>> dbo.PROVIDERS T6A ON
>> T11.PERFORMING_PHYS = T6A.ID_3
>> WHERE
>> T1.POSTING_PD_DTE >= '05/01/2003' AND
>> T1.PAY_CODE = 21 AND
>> T2.EXCLUSION_FLAG = 0 AND
>> T3.DIV_NUM <> '2901'
>>
>
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation â'
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
-----
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> -----
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in message
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>> Hello,
>> It is taking too long to run the following query:
>> Note: I have indexes on all of the columns in conditions. My temp db
>> size
>> is 18 GB. Pds_txn table size is 165 GB.
>> The execution plan showing:
>> Table pool/easer spool operation -
>> Row Count: 9 M
>> Disk i/o: 11k
>> Row size: 1089
>> Estimated cost: 11 k (57%)
>> CPU cost: 3.3
>> Sub tree cost: 20 K
>> Any help/hint will be appreciated.
>> Thanks,
>> Alim
>> -----
>>
>> FROM
>> dbo.pds_txn T1
>> INNER JOIN
>> dbo.GROUPS T2 ON
>> T1.GROUP_ID = T2.ID_200
>> INNER JOIN
>> dbo.DIVISIONS T3 ON
>> T1.DIVISION = T3.ID_102
>> INNER JOIN
>> dbo.BILLING_AREAS T4 ON
>> T1.BILLING_AREA = T4.ID_202
>> INNER JOIN
>> dbo.PROVIDERS T6 ON
>> T1.PROVIDER = T6.ID_3
>> INNER JOIN
>> dbo.LOCATIONS T7 ON
>> T1.LOCATION = T7.ID_100
>> INNER JOIN
>> dbo.PROCEDURES T8 ON
>> T1.[PROCEDURE] = T8.ID_1
>> INNER JOIN
>> dbo.FSC T9 ON
>> T1.ORIG_FSC = T9.ID_19
>> INNER JOIN
>> dbo.DIAGNOSIS T10 ON
>> T1.TXN_DX_1 = T10.ID_36
>> INNER JOIN
>> dbo.pds_invoice T11 ON
>> T1.INVOICE_NUM = T11.INVOICE_NUM AND
>> T1.GROUP_ID = T11.GROUP_ID
>> LEFT OUTER JOIN
>> dbo.PROVIDERS T6A ON
>> T11.PERFORMING_PHYS = T6A.ID_3
>> WHERE
>> T1.POSTING_PD_DTE >= '05/01/2003' AND
>> T1.PAY_CODE = 21 AND
>> T2.EXCLUSION_FLAG = 0 AND
>> T3.DIV_NUM <> '2901'
>>
>
Joins on 3 tables
Hi All,
I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables
Main table
Label table
textbox table
The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .
MAIN Table
pk Moduleid ItemName itemtype
label
pk Main_fk labeltext
Textbox
pk Main_fk textboxtext
I did infact manage to perform a join on these these tables.
Select * From tb_Main
inner join tb_Label
on tb_Main.pk = tb_Label.main_fk
where moduleID = @.moduleID
Select * From tb_Main
inner join tb_textbox
on tb_Main.pk = tb_textbox.main_fk
where moduleID = @.moduleID
The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.
Is what im asking possible? I would appreciate if some exmaples are posted
I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!
Thanks in advance
Matt
Hai,
You can try this query, hope this will work.
DECLARE @.ModuleID int
SET @.ModuleID = 372
SELECT
*
FROM tb_Main AS M
JOIN tb_Label AS L
ON M.pk = L.main_fK
JOIN tb_textbox AS T
ON M.pk = T.main_fk
WHERE ModuleID = @.ModuleID
Regards,
Kiran.Y
|||Thanks Y.Kiran for your suggestion.
I had already tried that out, it returns no rows.
Regards,
Matt
|||
Hai,
I didn't change any thing in the given query. Now I'm giving you the table definitions of all tables, Main, Label, TextBox. Check this one.
-- Create Main, Label, TextBox Tables.
CREATE TABLE tb_Main
( PK int,
Moduleid int,
ItemName varchar(50),
itemtype int,
CONSTRAINT PK_tb_Main_PK PRIMARY KEY CLUSTERED
(
PK ASC
)
)
CREATE TABLE tb_Label
(
PK int,
Main_fk int CONSTRAINT FK_tb_Main_Main_fk REFERENCES tb_Main(PK),
labeltext varchar(50),
CONSTRAINT PK_tb_Label_PK PRIMARY KEY CLUSTERED
(
PK ASC
)
)
CREATE TABLE tb_TextBox
(
PK int,
Main_fk int CONSTRAINT FK_tb_TextBox_Main_fk REFERENCES tb_Main(pk),
TextBoxText varchar(50),
CONSTRAINT PK_tb_TextBox_PK PRIMARY KEY CLUSTERED
(
PK ASC
)
)
-- Insert data into Main, Label, TextBox tables.
INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(36,372,'test1',4)
INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(37,372,'test2',4)
INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(38,372,'test3',4)
INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(39,372,'test4',6)
INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(40,372,'test5',4)
INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(4,36,'labeltext1')
INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(5,37,'labeltext2')
INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(6,38,'labeltext3')
INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(7,40,'labeltext4')
INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(8,39,'labeltext5')
INSERT INTO tb_textbox(pk, Main_fk, textboxtext) VALUES(1,39,'textbox1')
-- Get the records based on the @.ModuleID variable.
DECLARE @.ModuleID int
SET @.ModuleID = 372
SELECT
*
FROM tb_Main AS M
JOIN tb_Label AS L
ON M.pk = L.main_fK
JOIN tb_textbox AS T
ON M.pk = T.main_fk
WHERE ModuleID = @.moduleID
Let me know, If I did any wrong.
Regards,
Kiran.Y
|||You should be able to run the following statement to get what you need.
SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
INNER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
INNER_JOIN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID
The problem that I see is in your data. If this is a true representation of you data then an INNER JOIN is not going to return anything because when you join to the third table the only match you will find is 39, which is not in the second table, hence you get no results. If you were to do a LEFT OUTER JOIN, you could get all of the results with NULL values also represented. Try the query below to attempt to get results.
SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
LEFT OUTER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
LEFT OUTER_JOiN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID
Results:
pk ModuleID ItemName ItemType LabelText TextBox
-- -- -- -- -
36 372 test1 4 labeltext1 NULL
37 372 test2 4 labeltext2 NULL
38 372 test3 4 labeltext3 NULL
39 372 test4 6 NULL textbox1
40 372 test5 4 labeltext4 NULL
Hope this helps. Anyone feel free to correct if there are any inaccuracies. I'm relatively new to SQL Server.
|||No Data was returned from the query that Kumar provided, BECAUSE there is NO common data between all three tables.
MAIN Table
pk Moduleid ItemName itemtype
36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4 label
pk Main_fk labeltext
4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4 Textbox
pk Main_fk textboxtext
1 39 textbox1
There is NO [Mail_fk] for 39 in the table [Label], therefore no matching link between [Textbox], [Label] and [Main]
You 'could' use LEFT JOIN in both of the joins to have a resultset that includes ALL rows from [MAIN] even if there is NO matching links in the other tables.
|||DBaker,
Excellent explanation and corrected query!
|||
Thanks guys for your help it worked great !
Matt
Joins issue
Sorry if this is the wrong group but..
I have a query that still has a few minor issues the main problem i had with
nulls is sorted however i am joining 5 tables together and if a row doesnt
exist in a table i dont get a row at all, i have a table that i know a
record always exists in and i am using left outer joins to join it to other
tables. I thought that a left join would get a record regardless of whether
or not there is a matching record. My query is posted below so you can
maybe let me know whats wrong with it, i am sorry for the lack of aliases
and probably readibility but i havent really had time to sort it.
SELECT dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance, dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC +
dbo.DM_OtherCredit.redemtionsecured AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee, dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee, dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear, dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails LEFT OUTER JOIN
dbo.DM_Valuation ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_Valuation.FK_ApplicationID LEFT
OUTER JOIN
dbo.DM_Payout ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_Mortgage ON dbo.DM_LoanDetails.FK_ApplicationID
= dbo.DM_Mortgage.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_OtherCredit ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1') AND
(dbo.DM_LoanDetails.FieldIdent = '1:1') AND (dbo.DM_Valuation.FieldIdent =
'1:1') AND
(dbo.DM_Payout.FieldIdent = '1:1') AND
(dbo.DM_OtherCredit.FieldIdent = '1:1' OR
dbo.DM_OtherCredit.FieldIdent = '1:2' OR
dbo.DM_OtherCredit.FieldIdent = '1:3' OR
dbo.DM_OtherCredit.FieldIdent = '1:4' OR
dbo.DM_OtherCredit.FieldIdent = '1:5' OR
dbo.DM_OtherCredit.FieldIdent = '1:6' OR
dbo.DM_OtherCredit.FieldIdent = '1:7' OR
dbo.DM_OtherCredit.FieldIdent = '1:8' OR
dbo.DM_OtherCredit.FieldIdent = '1:9' OR
dbo.DM_OtherCredit.FieldIdent = '1:10')
Thanks in advanceA LEFT OUTER JOIN will always return rows, provided that your WHERE
criteria doesn't limit the results of your query using a column from
the inner side of the join. In your case, the criteria :
(dbo.DM_Mortgage.FieldIdent = '1:1')
tells SQL Server to limit the results to include data from both
DM_LoanDetails (all rows) and DM_Mortgage (only those rows where
FieldIDent = '1:1'). Basically, you've nullified your OUTER JOIN.
HTH,
Stu|||So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didn=B4t mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||Yeah the query is right the 1:1 condition needs to be there otherwise it
returns other iterations of the record and you end up with dupes i didnt
design the database its software that was ourchased a few years before i
started here, its hard to explain why the iterations are there and why they
work, I do need that clause in there though i have tried it without and
still get the same results.
I can better explain my problem now i think. The sql i have given is used
in another view that performs some calculations and basically if the value
is null makes it zero, the problem lies in the dm_payout and dm_valuation
tables, basically the case has died before anyone has been able to complete
the fields i need from those tables.
However i need to show what the value of the deal was regardless of whether
or not we got to add our fees on top, so if they wanted 100k but no other
fields were completed then it should show 100k
As i have mentioned this calculation is done in another view, the problem
lies in the fact that no record exists in the payout or valuation table so
it is for some unknown reason causing it not to get any results at all.
This other view (main view) is as follows
We have a table of phone numbers of people who have called in on a certain
number that we got from our dialler database this is joined to a table in
the database that has the phone number so that we can get the
fk_applicationID, this is present in all the tables as it is the unique
identifier. We then join this table to another table to get the persons
surname and 2 views, one of the views tells us what the applications status
is of the record, the 2nd view accesses the information in the view which is
the SQL i posted. Basically this view only pulls the required information
that is needed from the view i posted and if the value is null sets it to
zero. I then in this (main view) add the fields together i need.
I get all the records i would expect but i get null where the value of the
calculation should be because in the view i posted no row is returned.
I hope this is making sense. Maybe i wont be able to have a value here and
null is all i can expect but as i said a left join should as far as i know
just give me the rest of the information which then shouldnt mess up my
calc.
thanks for the help so far
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132142679.094129.141370@.g14g2000cwa.googlegroups.com...
So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didnt mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||On Wed, 16 Nov 2005 10:46:25 -0000, Steven Scaife wrote:
>Sorry if this is the wrong group but..
>I have a query that still has a few minor issues the main problem i had wit
h
>nulls is sorted however i am joining 5 tables together and if a row doesnt
>exist in a table i dont get a row at all, i have a table that i know a
>record always exists in and i am using left outer joins to join it to other
>tables. I thought that a left join would get a record regardless of whethe
r
>or not there is a matching record. My query is posted below so you can
>maybe let me know whats wrong with it, i am sorry for the lack of aliases
>and probably readibility but i havent really had time to sort it.
Hi Stevan,
A quick visit to http://www.sqlinform.com/ was all it took to get the
SQL a whole lot more readable. Here's a better formatted version of your
query:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1')
AND (dbo.DM_LoanDetails.FieldIdent = '1:1')
AND (dbo.DM_Valuation.FieldIdent = '1:1')
AND (dbo.DM_Payout.FieldIdent = '1:1')
AND (dbo.DM_OtherCredit.FieldIdent = '1:1'
OR dbo.DM_OtherCredit.FieldIdent = '1:2'
OR dbo.DM_OtherCredit.FieldIdent = '1:3'
OR dbo.DM_OtherCredit.FieldIdent = '1:4'
OR dbo.DM_OtherCredit.FieldIdent = '1:5'
OR dbo.DM_OtherCredit.FieldIdent = '1:6'
OR dbo.DM_OtherCredit.FieldIdent = '1:7'
OR dbo.DM_OtherCredit.FieldIdent = '1:8'
OR dbo.DM_OtherCredit.FieldIdent = '1:9'
OR dbo.DM_OtherCredit.FieldIdent = '1:10')
Now, it is immediately clear that the reason for your query not working,
is that you build WHERE clauses on columns from all outer-join'ed
tables. Stu already explained why that is bad - but it seems that he
only catched one of the culprits.
If you really need these joins to be outer joins, then you'll have to
move all selections from the WHERE clause to the ON clauses:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
AND dbo.DM_Valuation.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
AND dbo.DM_Payout.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
AND dbo.DM_Mortgage.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
AND dbo.DM_OtherCredit.FieldIdent IN ('1:1', '1:2', '1:3', '1:4',
'1:5', '1:6', '1:7', '1:8', '1:9', '1:10')
WHERE dbo.DM_LoanDetails.FieldIdent = '1:1'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have a query that still has a few minor issues the main problem i had with
nulls is sorted however i am joining 5 tables together and if a row doesnt
exist in a table i dont get a row at all, i have a table that i know a
record always exists in and i am using left outer joins to join it to other
tables. I thought that a left join would get a record regardless of whether
or not there is a matching record. My query is posted below so you can
maybe let me know whats wrong with it, i am sorry for the lack of aliases
and probably readibility but i havent really had time to sort it.
SELECT dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance, dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC +
dbo.DM_OtherCredit.redemtionsecured AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee, dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee, dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear, dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails LEFT OUTER JOIN
dbo.DM_Valuation ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_Valuation.FK_ApplicationID LEFT
OUTER JOIN
dbo.DM_Payout ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_Mortgage ON dbo.DM_LoanDetails.FK_ApplicationID
= dbo.DM_Mortgage.FK_ApplicationID LEFT OUTER JOIN
dbo.DM_OtherCredit ON
dbo.DM_LoanDetails.FK_ApplicationID = dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1') AND
(dbo.DM_LoanDetails.FieldIdent = '1:1') AND (dbo.DM_Valuation.FieldIdent =
'1:1') AND
(dbo.DM_Payout.FieldIdent = '1:1') AND
(dbo.DM_OtherCredit.FieldIdent = '1:1' OR
dbo.DM_OtherCredit.FieldIdent = '1:2' OR
dbo.DM_OtherCredit.FieldIdent = '1:3' OR
dbo.DM_OtherCredit.FieldIdent = '1:4' OR
dbo.DM_OtherCredit.FieldIdent = '1:5' OR
dbo.DM_OtherCredit.FieldIdent = '1:6' OR
dbo.DM_OtherCredit.FieldIdent = '1:7' OR
dbo.DM_OtherCredit.FieldIdent = '1:8' OR
dbo.DM_OtherCredit.FieldIdent = '1:9' OR
dbo.DM_OtherCredit.FieldIdent = '1:10')
Thanks in advanceA LEFT OUTER JOIN will always return rows, provided that your WHERE
criteria doesn't limit the results of your query using a column from
the inner side of the join. In your case, the criteria :
(dbo.DM_Mortgage.FieldIdent = '1:1')
tells SQL Server to limit the results to include data from both
DM_LoanDetails (all rows) and DM_Mortgage (only those rows where
FieldIDent = '1:1'). Basically, you've nullified your OUTER JOIN.
HTH,
Stu|||So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didn=B4t mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||Yeah the query is right the 1:1 condition needs to be there otherwise it
returns other iterations of the record and you end up with dupes i didnt
design the database its software that was ourchased a few years before i
started here, its hard to explain why the iterations are there and why they
work, I do need that clause in there though i have tried it without and
still get the same results.
I can better explain my problem now i think. The sql i have given is used
in another view that performs some calculations and basically if the value
is null makes it zero, the problem lies in the dm_payout and dm_valuation
tables, basically the case has died before anyone has been able to complete
the fields i need from those tables.
However i need to show what the value of the deal was regardless of whether
or not we got to add our fees on top, so if they wanted 100k but no other
fields were completed then it should show 100k
As i have mentioned this calculation is done in another view, the problem
lies in the fact that no record exists in the payout or valuation table so
it is for some unknown reason causing it not to get any results at all.
This other view (main view) is as follows
We have a table of phone numbers of people who have called in on a certain
number that we got from our dialler database this is joined to a table in
the database that has the phone number so that we can get the
fk_applicationID, this is present in all the tables as it is the unique
identifier. We then join this table to another table to get the persons
surname and 2 views, one of the views tells us what the applications status
is of the record, the 2nd view accesses the information in the view which is
the SQL i posted. Basically this view only pulls the required information
that is needed from the view i posted and if the value is null sets it to
zero. I then in this (main view) add the fields together i need.
I get all the records i would expect but i get null where the value of the
calculation should be because in the view i posted no row is returned.
I hope this is making sense. Maybe i wont be able to have a value here and
null is all i can expect but as i said a left join should as far as i know
just give me the rest of the information which then shouldnt mess up my
calc.
thanks for the help so far
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132142679.094129.141370@.g14g2000cwa.googlegroups.com...
So I guess that your always existing row is stored in the
DM_LoanDetails table, right ? (You didnt mentioned that). If so the
query is right. Try to eliminate the conditions at the end step by step
to see if these are chopping your result in any way.
HTH, jens Suessmeyer.|||On Wed, 16 Nov 2005 10:46:25 -0000, Steven Scaife wrote:
>Sorry if this is the wrong group but..
>I have a query that still has a few minor issues the main problem i had wit
h
>nulls is sorted however i am joining 5 tables together and if a row doesnt
>exist in a table i dont get a row at all, i have a table that i know a
>record always exists in and i am using left outer joins to join it to other
>tables. I thought that a left join would get a record regardless of whethe
r
>or not there is a matching record. My query is posted below so you can
>maybe let me know whats wrong with it, i am sorry for the lack of aliases
>and probably readibility but i havent really had time to sort it.
Hi Stevan,
A quick visit to http://www.sqlinform.com/ was all it took to get the
SQL a whole lot more readable. Here's a better formatted version of your
query:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
WHERE (dbo.DM_Mortgage.FieldIdent = '1:1')
AND (dbo.DM_LoanDetails.FieldIdent = '1:1')
AND (dbo.DM_Valuation.FieldIdent = '1:1')
AND (dbo.DM_Payout.FieldIdent = '1:1')
AND (dbo.DM_OtherCredit.FieldIdent = '1:1'
OR dbo.DM_OtherCredit.FieldIdent = '1:2'
OR dbo.DM_OtherCredit.FieldIdent = '1:3'
OR dbo.DM_OtherCredit.FieldIdent = '1:4'
OR dbo.DM_OtherCredit.FieldIdent = '1:5'
OR dbo.DM_OtherCredit.FieldIdent = '1:6'
OR dbo.DM_OtherCredit.FieldIdent = '1:7'
OR dbo.DM_OtherCredit.FieldIdent = '1:8'
OR dbo.DM_OtherCredit.FieldIdent = '1:9'
OR dbo.DM_OtherCredit.FieldIdent = '1:10')
Now, it is immediately clear that the reason for your query not working,
is that you build WHERE clauses on columns from all outer-join'ed
tables. Stu already explained why that is bad - but it seems that he
only catched one of the culprits.
If you really need these joins to be outer joins, then you'll have to
move all selections from the WHERE clause to the ON clauses:
SELECT
dbo.DM_LoanDetails.FK_ApplicationID,
dbo.DM_Mortgage.MortgageBalance,
dbo.DM_Mortgage.Redemption,
dbo.DM_OtherCredit.BALANCESEC + dbo.DM_OtherCredit.redemtionsecured
AS Secured_Borrowing,
dbo.DM_OtherCredit.Balance,
dbo.DM_LoanDetails.EXTRAFUNDS,
dbo.DM_LoanDetails.RulesArrangementfee,
dbo.DM_LoanDetails.RulesLegals,
dbo.DM_Payout.BrokerAdminFee,
dbo.DM_Payout.ASUFee,
dbo.DM_Valuation.Cost,
dbo.DM_OtherCredit.ToClear,
dbo.DM_OtherCredit.FieldIdent,
dbo.DM_Payout.ProcFee
FROM dbo.DM_LoanDetails
LEFT OUTER JOIN
dbo.DM_Valuation
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Valuation.FK_ApplicationID
AND dbo.DM_Valuation.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Payout
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Payout.FK_ApplicationID
AND dbo.DM_Payout.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_Mortgage
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_Mortgage.FK_ApplicationID
AND dbo.DM_Mortgage.FieldIdent = '1:1'
LEFT OUTER JOIN
dbo.DM_OtherCredit
ON dbo.DM_LoanDetails.FK_ApplicationID =
dbo.DM_OtherCredit.FK_ApplicationID
AND dbo.DM_OtherCredit.FieldIdent IN ('1:1', '1:2', '1:3', '1:4',
'1:5', '1:6', '1:7', '1:8', '1:9', '1:10')
WHERE dbo.DM_LoanDetails.FieldIdent = '1:1'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, March 23, 2012
Joins in Crystal Reports
Hi,
I'm newbie in Crystal report. I've a query which i'm unable to implement in CR. Eventhough the query looks simple because of the join condition I'm struggling . Pls help me to implement the join condition. I run the query in DB2
select sum(bank_ledger_bal_amt),calendar_date
from table_a a
inner join table_date b
on a.bank_acct_eff_dt <= b.calendar_date
and a.bank_acct_disc_dt > b.calendar_date
where month_id = 'feb'
group by b.calendar_date
order by b.calendar_date
;
Thanks,
JashUse the add command of Crystal and paste in your SQL query in there.
GJ|||Sorry I didnt get what do you mean|||In the Database Expert there is an option to select "Add Command" there you can type out your sql. Crystal will let you know if there is an error in your SQL.
GJsql
I'm newbie in Crystal report. I've a query which i'm unable to implement in CR. Eventhough the query looks simple because of the join condition I'm struggling . Pls help me to implement the join condition. I run the query in DB2
select sum(bank_ledger_bal_amt),calendar_date
from table_a a
inner join table_date b
on a.bank_acct_eff_dt <= b.calendar_date
and a.bank_acct_disc_dt > b.calendar_date
where month_id = 'feb'
group by b.calendar_date
order by b.calendar_date
;
Thanks,
JashUse the add command of Crystal and paste in your SQL query in there.
GJ|||Sorry I didnt get what do you mean|||In the Database Expert there is an option to select "Add Command" there you can type out your sql. Crystal will let you know if there is an error in your SQL.
GJsql
JOINs and SUMs Not Giving Expected Results
I have just added a third table to a query and I am no longer getting
the results I am expecting.
the results I am expecting.
Three Tables:
CUSTINVOICEJOUR (Header Table)
CUSTINVOICETRANS (Line Item Table)
MARKUPTRANS (Additional Header Info)
CUSTINVOICEJOUR has a one to many relationship to CUSTINVOICETRANS.
CUSTINVOICEJOUR has a one to many relationship to MARKUPTRANS.
I need to sum an integer column from MARKUPTRANS, in rows that are
related to CUSTINVOICEJOUR, and include that output with my query
below, which right now has a row for each CUSTINVOICETRANS record:
SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm')
The above works fine - a row for each record in CUSTINVOICETRANS with
the header info in there as well.
I tried the query below to add a SUM() from MARKUPTRANS, but when I run
it, I get one row with strange results in it - not what I expected.
What am I doing wrong?
SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
SUM(MARKUPTRANS.VALUE) AS FreightValue
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID INNER JOIN
MARKUPTRANS ON CUSTINVOICEJOUR.RECID =
MARKUPTRANS.TRANSRECID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') AND (MARKUPTRANS.DATAAREAID =
'acm')
GROUP BY CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
MARKUPTRANS.MARKUPCODE
HAVING (MARKUPTRANS.MARKUPCODE = 'Freight')"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message news:ci581o$h9v@.odak26.prod.google.com...
> I have just added a third table to a query and I am no longer getting
> the results I am expecting.
<snip
If you're aggregating a column from MARKUPTRANS, then you shouldn't include another column from that same table in the
GROUP BY clause. I would rewrite that as a correlated subquery in the SELECT list.
SELECT
CUSTINVOICEJOUR.INVOICEAMOUNT,
CUSTINVOICETRANS.QTY,
(Select SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE CUSTINVOICEJOUR.RECID = MARKUPTRANS.TRANSRECID
and MARKUPTRANS.DATAAREAID = 'acm'
and MARKUPTRANS.MARKUPCODE = 'Freight' ) AS FreightValue
FROM
CUSTINVOICEJOUR
INNER JOIN
CUSTINVOICETRANS
ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID
WHERE
(CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') ;
--
Paul Horan[TeamSybase]
Joins Across Servers/Databases
I am trying to determine how best to create a query that will allow me to perform 'joins across servers'.
I want to create a single join that will join my production database (Oracle) with my Land Database (MSSQL) on a common key.
From what I have gathered on other sites, it appears that SQLServer can perform this function, but I am not that technical and I am not a SQL programmer (eventhough I have access to one), and I am hoping there are some point and click apps that will let me perform this function.
Any thoughts ?Hello,
the best way to join two databases for example from Oracle to MSSQL is to use ADBC. ADBC is a software package that expands the Oracle database. Once installed, you can select the MSSQL tables that should be visible in Oracle.
If you like, we can assist you installing ADBC and developing the necessary functions for MSSQL to Oracle access.
Please send me a mail to m.peter@.alligatorsql.com if you need further help in this way.
Hope that helps ?
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com
I want to create a single join that will join my production database (Oracle) with my Land Database (MSSQL) on a common key.
From what I have gathered on other sites, it appears that SQLServer can perform this function, but I am not that technical and I am not a SQL programmer (eventhough I have access to one), and I am hoping there are some point and click apps that will let me perform this function.
Any thoughts ?Hello,
the best way to join two databases for example from Oracle to MSSQL is to use ADBC. ADBC is a software package that expands the Oracle database. Once installed, you can select the MSSQL tables that should be visible in Oracle.
If you like, we can assist you installing ADBC and developing the necessary functions for MSSQL to Oracle access.
Please send me a mail to m.peter@.alligatorsql.com if you need further help in this way.
Hope that helps ?
Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com
joins
hello,
I have a noob question
I have a table called Alpha
aplha_id team1 team2
and a table called Team
teamid teamname
I cannot get the correct query in order to retrieve all the data from table
Alpha where team1 to exist in table Team and team2 also to exist in table
Team
(I used the keyword in plus a subquery but it is very slow)
can u help?
Thanx in advanceP wrote on Thu, 9 Mar 2006 15:22:04 +0200:
> hello,
> I have a noob question
> I have a table called Alpha
> aplha_id team1 team2
> and a table called Team
> teamid teamname
> I cannot get the correct query in order to retrieve all the data from
> table Alpha where team1 to exist in table Team and team2 also to exist in
> table Team
> (I used the keyword in plus a subquery but it is very slow)
> can u help?
> Thanx in advance
How about
SELECT a.*
FROM Alpha a
INNER JOIN Team t1 on a.team1 = t1.teamid
INNER JOIN Team t2 on a.team2 = t2.teamid
Dan|||THANX A LOT!!!
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:udwRIB4QGHA.5296@.tk2msftngp13.phx.gbl...
>P wrote on Thu, 9 Mar 2006 15:22:04 +0200:
>
> How about
> SELECT a.*
> FROM Alpha a
> INNER JOIN Team t1 on a.team1 = t1.teamid
> INNER JOIN Team t2 on a.team2 = t2.teamid
>
> Dan
>
I have a noob question
I have a table called Alpha
aplha_id team1 team2
and a table called Team
teamid teamname
I cannot get the correct query in order to retrieve all the data from table
Alpha where team1 to exist in table Team and team2 also to exist in table
Team
(I used the keyword in plus a subquery but it is very slow)
can u help?
Thanx in advanceP wrote on Thu, 9 Mar 2006 15:22:04 +0200:
> hello,
> I have a noob question
> I have a table called Alpha
> aplha_id team1 team2
> and a table called Team
> teamid teamname
> I cannot get the correct query in order to retrieve all the data from
> table Alpha where team1 to exist in table Team and team2 also to exist in
> table Team
> (I used the keyword in plus a subquery but it is very slow)
> can u help?
> Thanx in advance
How about
SELECT a.*
FROM Alpha a
INNER JOIN Team t1 on a.team1 = t1.teamid
INNER JOIN Team t2 on a.team2 = t2.teamid
Dan|||THANX A LOT!!!
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:udwRIB4QGHA.5296@.tk2msftngp13.phx.gbl...
>P wrote on Thu, 9 Mar 2006 15:22:04 +0200:
>
> How about
> SELECT a.*
> FROM Alpha a
> INNER JOIN Team t1 on a.team1 = t1.teamid
> INNER JOIN Team t2 on a.team2 = t2.teamid
>
> Dan
>
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. :(
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. :(
Wednesday, March 21, 2012
joining two queries ?
How can I return the results of two different queries ?
So that the recordset returned has all the fields from query A, plus the
results from query B ?
Both return the same columns.
ALookup UNION in Books Online.
David Portas
SQL Server MVP
--
So that the recordset returned has all the fields from query A, plus the
results from query B ?
Both return the same columns.
ALookup UNION in Books Online.
David Portas
SQL Server MVP
--
Joining two fields in a query
I am trying to join two fields in a query in SQL 2000. For example.
Update myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + field_2_value
Is this even possible.
I want the user to input values for fields 1 and 2, then in the background combine the two and insert that value in field 3.
Thanks in advance,
Scotty_C
the ' x ' should also be inserted between the values.
|||that'll work! there's just a typo for the @.field_2_valueUpdate myTable SET field_1 = @.field_1_value , field_2 = @.field_2_value, field_3 = @.field_1_value + ' x ' + @.field_2_value|||
I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.
Thank you for you input.
When I attempt to execute the Statement I get the Error Message:
"Syntax Error Converting the varChar value ' x ' to a column of datatype int."
The actual SQL Statement being used is this:
UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)
The datatype for the column "Length" is varChar(50)
|||excuse me, it has been a long day, the datatype for the column "Label" is varChar(50) and the dataype for the columns "Length" and "Width" is int.|||Scotty_C wrote:
I agree that there was a typo, however, the given SQL Statement was just fabricated for the forum as an example.
Thank you for you input.
When I attempt to execute the Statement I get the Error Message:
"Syntax Error Converting the varChar value ' x ' to a column of datatype int."
The actual SQL Statement being used is this:
UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = @.Width + ' x ' + @.Length
WHERE (SheetSizeID = @.SheetSizeID)The datatype for the column "Length" is varChar(50)
you have to use CAST or CONVERT before concatenating your values...
UPDATE SheetSizes
SET Width = @.Width, Length = @.Length, Standard = @.Standard, Label = CAST(@.Width AS varchar(10)) + ' x ' + CAST(@.Length AS varchar(10))
WHERE (SheetSizeID = @.SheetSizeID)
HTH,|||
Yes CryptoKnight,
That works very well, thank you!
Thanks,
Scotty_C
Joining tables in several ways withing the same query
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
HiTry using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
Joining tables in several ways withing the same query
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated :)
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated :)
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)
Joining tables in several ways withing the same query
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>
|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegr oups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
sql
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated
>
|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegr oups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated
sql
Monday, March 19, 2012
joining results of 2 queries
Does anyone know how I can 'join' the results of
one SQL query to the bottom of another?
one SQL query to the bottom of another?
Eg. I have two queries:
1. SELECT Name, Surname FROM People WHERE Surname = Smith
NAME SURNAME
Adam Smith
John Smith
Michael Smith
Steve Smith
2. SELECT Name, Surname FROM People WHERE Surname = Jones
NAME SURNAME
Bob Jones
Larry Jones
Tom Jones
What I want to produce is:
NAME SURNAME
Adam Smith
John Smith
Michael Smith
Steve Smith
Bob Jones
Larry Jones
Tom Jones
However, if I use UNION like this:
SELECT Name, Surname FROM People WHERE Surname = Smith
UNION
SELECT Name, Surname FROM People WHERE Surname = Jones
it mixes up all the results:
NAME SURNAME
Adam Smith
Bob Jones
John Smith
Larry Jones
Michael Smith
Steve Smith
Tom Jones
(I guess it's sorting by the first field, NAME).
Is there a way to stop it sorting the results, so that it
just tacks the second query results to the bottom of the
first query results?
(I realise I could use "ORDER BY Surname" to get the same result
in this simple example, but for the more complicated queries
I want to use it won't work).
Thanks for any help,
Matt.Hi Matt,
Union sorts the data and tries to remove duplicates. Use UNION ALL to
override this behavior. If the data is stored in a table, use an
identity column to preserve the order. - Louis|||odonnell_matthew@.hotmail.com (Matt O'Donnell) wrote in message news:<cc73ca09.0311150527.4f160205@.posting.google.com>...
> Does anyone know how I can 'join' the results of
> one SQL query to the bottom of another?
> Eg. I have two queries:
>
> 1. SELECT Name, Surname FROM People WHERE Surname = Smith
>
> NAME SURNAME
> Adam Smith
> John Smith
> Michael Smith
> Steve Smith
>
> 2. SELECT Name, Surname FROM People WHERE Surname = Jones
>
> NAME SURNAME
> Bob Jones
> Larry Jones
> Tom Jones
>
> What I want to produce is:
>
> NAME SURNAME
> Adam Smith
> John Smith
> Michael Smith
> Steve Smith
> Bob Jones
> Larry Jones
> Tom Jones
>
> However, if I use UNION like this:
> SELECT Name, Surname FROM People WHERE Surname = Smith
> UNION
> SELECT Name, Surname FROM People WHERE Surname = Jones
>
> it mixes up all the results:
> NAME SURNAME
> Adam Smith
> Bob Jones
> John Smith
> Larry Jones
> Michael Smith
> Steve Smith
> Tom Jones
>
> (I guess it's sorting by the first field, NAME).
> Is there a way to stop it sorting the results, so that it
> just tacks the second query results to the bottom of the
> first query results?
>
> (I realise I could use "ORDER BY Surname" to get the same result
> in this simple example, but for the more complicated queries
> I want to use it won't work).
> Thanks for any help,
> Matt.
WHy not use
SELECT Name, Surname FROM People WHERE Surname = 'Jones' or 'SMITH'
ORDER BY Surname, Name
Simple and avoids the need for a UNION
HOpe this helps
Duncan|||I agree that in this case you should just use an OR, but assuming your real
case is more complicated, how about
SELECT 1 AS ResultSetNum, Name, Surname FROM People WHERE Surname = Smith
UNION ALL
SELECT 2 AS ResultSetNum, Name, Surname FROM People WHERE Surname = Jones
ORDER BY ResultSetNum
"duncan" <dwang@.woodace.co.uk> wrote in message
news:8fe85207.0311151415.3ece8226@.posting.google.c om...
> odonnell_matthew@.hotmail.com (Matt O'Donnell) wrote in message
news:<cc73ca09.0311150527.4f160205@.posting.google.com>...
> > Does anyone know how I can 'join' the results of
> > one SQL query to the bottom of another?
> > Eg. I have two queries:
> > 1. SELECT Name, Surname FROM People WHERE Surname = Smith
> > NAME SURNAME
> > Adam Smith
> > John Smith
> > Michael Smith
> > Steve Smith
> > 2. SELECT Name, Surname FROM People WHERE Surname = Jones
> > NAME SURNAME
> > Bob Jones
> > Larry Jones
> > Tom Jones
> > What I want to produce is:
> > NAME SURNAME
> > Adam Smith
> > John Smith
> > Michael Smith
> > Steve Smith
> > Bob Jones
> > Larry Jones
> > Tom Jones
> > However, if I use UNION like this:
> > SELECT Name, Surname FROM People WHERE Surname = Smith
> > UNION
> > SELECT Name, Surname FROM People WHERE Surname = Jones
> > it mixes up all the results:
> > NAME SURNAME
> > Adam Smith
> > Bob Jones
> > John Smith
> > Larry Jones
> > Michael Smith
> > Steve Smith
> > Tom Jones
> > (I guess it's sorting by the first field, NAME).
> > Is there a way to stop it sorting the results, so that it
> > just tacks the second query results to the bottom of the
> > first query results?
> > (I realise I could use "ORDER BY Surname" to get the same result
> > in this simple example, but for the more complicated queries
> > I want to use it won't work).
> > Thanks for any help,
> > Matt.
>
> WHy not use
> SELECT Name, Surname FROM People WHERE Surname = 'Jones' or 'SMITH'
> ORDER BY Surname, Name
>
> Simple and avoids the need for a UNION
> HOpe this helps
>
> Duncan
Subscribe to:
Posts (Atom)