Jump to report

Hi

I want to use Jump to Report option under Navigation tab.

I have two reports both with Same parameters and parameters are multiple value selectable.

In Jump to Report this is how I set the parameter value.

LeadershipTeamId =Join(Parameters!LeadershipTeamId.Value,", ")

CostCentreId =Join(Parameters!CostCentreId.Value,", ")

CostElementGroupId =Fields!CostElementGroupId.Value

This works fine if I have only one value selected for CostCentre and LeadershipTeam but when I select the multiple value I get the following error.

The value provided for the report parameter 'LeadershipTeamId' is not valid for its type. (rsReportParameterTypeMismatch)

Am I missing anything here?

Any help would be highly appreciated.

Best Regards

I used this expression and it seems to be working.

=Split(Join(Parameters!LeadershipTeamId.Value,", "),",")

Correct me if this is not the right approach.

Now my problem is When I jump to second report the CostCentre Parameter value is not set.

Because, costcentre value is depend on the LeadershipTeam value.

So when I jump to second report I have LeadershipTeam value set but I have to select costcentre value.

Is it possible to make CostCentre selected when someone jump to the report?

Thanks for your help

Regards

Update: By the way it works if I select only one CostCentre.

|||

Solved it using this thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

Thanks guys

Amit

joning result of dynamic queries

i m making the code of search button in aspx page using C#.

i have multiple dynamic queries that take data from multiple tables

but i dnt know how to join the result of these queries.

example:

1) first query using table1

2)second query using table2

3)third query using table3

how to join the result of these queries at asp.net page?

thanx in advance

If the result sets of all the queries are similar (ie. same number of columns of the same data types)

Then just issue one command with all three queries union'd together

Code Snippet

SELECT x,y,z From Table1

UNION ALL

SELECT x, y z From Table2

UNION All

SELECT x, y, z From Table 3

|||

thanx for reply but it is not useful at all

because i m asking how to combine the result of dynamic queries that relate multiple tables and u r sending simple sql query.i willl try to explain my problem in moredetail as follow.

TABLE NAME COLUMNNAMES

1) tbQualification -- ResumeId,Graduation,PostGraduation

2) tbStrength-- Resumeid,Strength

3)tbExperience Resumeid,YearExperience,MonthExperience

there is a button of SEARCH .clicking on this button search is to be made in all columns fields.this is to be done throgh dynamic query or stored procedure.

this cant be done using simple sql query because we are writting code in aspx page.

plizz help me out

all replies are welcome

Thanx.

|||

You can store the result of each query in the temp table then join those temp tables for your final query...

Code Snippet

Create Table #tbQualificationResult (

ResumeId int,

Graduation varchar(20),

PostGraduation varchar(20)

)

Create Table #tbStrength (

Resumeid int,

Strength int

)

Create Table #tbExperience (

Resumeid int,

YearExperience int,

MonthExperience int

)

Insert Into #tbQualificationResult

Exec sp_executesql 'Your Dynamic Search Query'

Insert Into #tbStrength

Exec sp_executesql 'Your Dynamic Search Query'

Insert Into #tbExperience

Exec sp_executesql 'Your Dynamic Search Query'

Select

COALESCE(A.ResumeId, B.ResumeId, C.ResumeId)

,A.Graduation

,A.PostGraduation

,C.Strength

,C.YearExperience

,C.MonthExperience

From

#tbQualificationResult A

Full OUTER JOIN #tbStrength B on A.ResumeId = B.ResumeId

FULL OUTER JOIN #tbExperience C On A.ResumeId = C.ResumeId

|||

One approach to your issue, from my response and Mani's, is to code the combinations on the back-end in SQL Server.

However, this can also be done at the BI or UI tier.

See these articles for creating datasets with multiple tables and establishing datarelations between those tables.

http://msdn2.microsoft.com/en-us/library/ss7fbaez(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/ay82azad(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/d6s958d6(VS.80).aspx

|||

thanx a lot for reply.

plizz tel how i write query where u say"your dynamic query" because i dnt know how to write dynamic query in sql.

i know only in aspx page and it is different to write in sql so pliz tel.

need urgently

thanx

sql

Joins

I am trying to join multiple tables using FROM joins. I want to pick up
only OB/GYN Companies but am instead getting everything.
Here's my SQL...
SELECT COMPANY.[NAME],
COMPANY.[ADDRESS],
COMPANY.[CITY],
COMPANY.[STATE],
COMPANY.[ZIP],
COMPANY.[MAIN_CONTACT],
COMPANY.[MAINCONTCTPHONE],
CMDBSPEC.[CMDB_SPEC_DESC],
CMDBCOMP.[CMDB_COMPANY_PRODUCT_PURCHASE]
FROM [magictsd].[_SMDBA_].[_COMPANY_] COMPANY
INNER JOIN [magictsd].[_SMDBA_].[CMDB_COMPANY] CMDBCOMP
ON COMPANY.SEQUENCE = CMDBCOMP.SEQUENCE
INNER JOIN [magictsd].[_SMDBA_].[CMDB_SPEC] CMDBSPEC
ON CMDBCOMP.CMDB_COMPANY_SEQ_SPECIALTY = CMDBSPEC.SEQUENCE
AND CMDBSPEC.CMDB_SPEC_DESC LIKE '%Ob%'
OR CMDBSPEC.CMDB_SPEC_DESC LIKE '%Gyn%'
Can anyone help me? I thought INNER JOIN was the answer since I want
ONLY the OB/GYN Companies.
Any help is GREATLY appreciated!Without DDLs & sample data this is only a guess: Try changing the OR in your
JOIN clause to an AND.
Anith|||Well the last two Join condiditons should be surrounded by Parentheses, but
actually even better, would be to put them in a Where clause instead of as a
Join Condition...
Select C.Name, C.Address, C.City, C.State,
C.Zip, C.Zip,Main_Contact, C.Zip,MainContactPhone,
S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
From _Company C
Join Cmdb_Company CC
On CC.Sequence = C.Sequence
Join Cmdb_Spec S
On S.Sequence = CC.Cmdb_Company_Seq_Speciality
Where S.Cmdb_Spec_Desc Like '%Ob%'
Or S.Cmdb_Spec_Desc Like '%Gyn%'|||Sorry, your field names are a bit unusual, I typoed trying t ocopy them...
Here is corrected version...
Select C.Name, C.Address, C.City, C.State,
C.Zip, C.Main_Contact, C.MainContctPhone,
S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
From _Company C
Join Cmdb_Company CC
On CC.Sequence = C.Sequence
Join Cmdb_Spec S
On S.Sequence = CC.Cmdb_Company_Seq_Speciality
Where S.Cmdb_Spec_Desc Like '%Ob%'
Or S.Cmdb_Spec_Desc Like '%Gyn%'
"CBretana" wrote:

> Well the last two Join condiditons should be surrounded by Parentheses, bu
t
> actually even better, would be to put them in a Where clause instead of as
a
> Join Condition...
> Select C.Name, C.Address, C.City, C.State,
> C.Zip, C.Zip,Main_Contact, C.Zip,MainContactPhone,
> S.Cmdb_Spec_Desc, CC.Cmdb_Company_Product_Purchase
> From _Company C
> Join Cmdb_Company CC
> On CC.Sequence = C.Sequence
> Join Cmdb_Spec S
> On S.Sequence = CC.Cmdb_Company_Seq_Speciality
> Where S.Cmdb_Spec_Desc Like '%Ob%'
> Or S.Cmdb_Spec_Desc Like '%Gyn%'

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

My thread titles need work, I know. :o

Ok, lets say I've got:

tblDocuments
id INT PK
documentName VARCHAR

tblUsers
id INT PK
userName VARCHAR

tblDocumentApprovals
userID INT
documentID INT
approvalDate DATETIME

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Joining two tables multiple times

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

Joining Tables Across Multiple Datasets

I am trying to join two tables from two different datasets in SQL
Server 2005 Reporting Services. How would one go about doing this?
Since there are technically two spots to have SQL queries (one for
each dataset), how does one go about doing SQL? Is joining across
multiple datasets even possible? If so, are there clever workarounds?You cannot join datasets in RS. You either need to do this in a stored
procedure OR you should think about subreports. Usually what you need to do
can be accomplished with subreports. For a 1:1 or 1:M relationship
subreports will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rdracer58@.gmail.com> wrote in message
news:1183746250.948449.38960@.o61g2000hsh.googlegroups.com...
>I am trying to join two tables from two different datasets in SQL
> Server 2005 Reporting Services. How would one go about doing this?
> Since there are technically two spots to have SQL queries (one for
> each dataset), how does one go about doing SQL? Is joining across
> multiple datasets even possible? If so, are there clever workarounds?
>sql

joining table on on last entered record

Dear All,

What's the most efficient way of joining a 1 to many relation, where a record in table A will have multiple records in table B.

I'd like to select every record in table A but only joining the last relevant record from table B. So:

Table A:

A1 Prj1
A2 Prj2

Table B:

B1 A1 23/12/2005
B2 A1 26/12/2005
B3 A1 2/1/2007
B4 A2 25/12/2006
B5 A2 1/1/2007

So I'd like to list using the most efficient way this:

A1 Prj1 B3 2/1/2007
A2 Prj2 B5 1/1/2007

I'm assuming this is NOT the most efficient way:

select A, (select top 1 date from B orderBy ...)

Any suggestions?Maybe this:

select A, B
from A
inner join A on A.Aid = B.Aid
where B.date = (select max(B.date) from B where...) ...

this works faster but is there a better way? (I'm sure there is)

Anyone?|||I use this:
select A,
B
from A
inner join --LastRecords
select A,
max(date) as date
from A
group by A) LastRecords
on A.A = LastRecords.A
and A.date = LastRecords.date
...but I can't promise that it is faster.|||Another possibility is:
SELECT A.aid
,A.prj
,MAX(B.DATE)
FROM A
INNER JOIN B ON B.aid = A.aid
GROUP BY A.aid, A.prj
Don't know how this will perform but as long as you have the right index (I'd recomment one on "B.Aid, B.date") I don't think it will differ much between the various methods.|||Thanks for that, how about when the record in table A have no records in table B yet, but I'd still like to list it, but with a NULL value in the columns from table B?|||use a LEFT OUTER JOIN instead of INNER JOIN|||Thanks for that

Joining multiple tables in a view.

I have three tables

1^st table is Student

StudnetID (pk)

Other fields…

2^nd table is PhoneType

PhoneTypeID (pk)

PhoneType

3^rd table is StudentHasPhone

SHPID (pk)

StudnetID (fk)

PhoneTypeID (fk)

PhoneNumber

PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…

StudnetID: 1

Name: John

HomePhone: 123-456-7890

WorkPhone: 123-456-7890

CellPhone:

Pager: 123-456-7890

Fax:

Memo: This is one student record.

Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won't have.

Thanks in advanced,

Nathan Rover

What you need is a View with UNION ALL but your tables must be UNION compatible which means same data type facing the same direction. Try the link below for sample code. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

|||

You can join to the phone table multiple times, as follows:

SELECT StudentID, HP.PhoneNumber, WP.PhoneNumber, CP.PhoneNumber, Pg.PhoneNumber, Fx.PhoneNumber
FROM Student S
LEFT OUTER JOIN StudentHasPhone HP
ON S.StudentID = HP.StudentID
AND HP.PhoneTypeID = 1 --Home Phone
LEFT OUTER JOIN StudentHasPhone WP
ON S.StudentID = WP.StudentID
AND WP.PhoneTypeID = 2 --Work Phone
LEFT OUTER JOIN StudentHasPhone CP
ON S.StudentID = CP.StudentID
AND CP.PhoneTypeID = 3 --Cell Phone
LEFT OUTER JOIN StudentHasPhone Pg
ON S.StudentID = Pg.StudentID
AND Pg.PhoneTypeID = 4 --Pager
LEFT OUTER JOIN StudentHasPhone Fx
ON S.StudentID = Fx.StudentID
AND Fx.PhoneTypeID = 5 --Fax

BTW: StudentHasPhone is not a good table name. StudentPhone, or simply Phone, would be much better.

|||

Thanks, that was exactly what I was looking for… It worked perfect.

--Nathan

Joining multiple tables in a view.

I have three tables
1st table is Student
StudnetID (pk)
Other fields…
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell
phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make
a view of a view that would allow me to ultimately end up with…
StudnetID: 1
Name: John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager: 123-456-7890
Fax:
Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have
one or two. If possible I would like to do a setup like this in my database
to keep from having to have null fields for 4 phone numbers that the majorit
y
of records won’t have.
Thanks in advanced,
Nathan RoverHi
You can self join the tables, but that would reduce the flexibility of your
design e.g.
SELECT s.StudnetID. s.Name, a.PhoneNumber As HomePhone,
b.PhoneNumber As WorkPhone,
c.PhoneNumber As CellPhone
d.PhoneNumber As Pager
e.PhoneNumber As Fax
FROM Student s
LEFT JOIN StudentHasPhone a ON a.StudnetID = s.StudnetID AND a.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Home phone' )
LEFT JOIN StudentHasPhone b ON b.StudnetID = s.StudnetID AND b.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Work phone' )
LEFT JOIN StudentHasPhone c ON c.StudnetID = s.StudnetID AND c.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Cell phone' )
LEFT JOIN StudentHasPhone d ON d.StudnetID = s.StudnetID AND d.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Pager' )
LEFT JOIN StudentHasPhone e ON e.StudnetID = s.StudnetID AND e.PhoneTypeID
= ( SELECT PhoneTypeID FROM PhoneType WHERE PhoneType = 'Fax' )
The best solution is to process this at the client.
John
"NateDawg" wrote:

> I have three tables
> 1st table is Student
> StudnetID (pk)
> Other fields…
> 2nd table is PhoneType
> PhoneTypeID (pk)
> PhoneType
> 3rd table is StudentHasPhone
> SHPID (pk)
> StudnetID (fk)
> PhoneTypeID (fk)
> PhoneNumber
> PhoneType is an auxiliary table that has 5 records in it Home phone, Cell
> phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe ma
ke
> a view of a view that would allow me to ultimately end up with…
> StudnetID: 1
> Name: John
> HomePhone: 123-456-7890
> WorkPhone: 123-456-7890
> CellPhone:
> Pager: 123-456-7890
> Fax:
> Memo: This is one student record.
> Some students will have no phone number, some will have all 5 most will ha
ve
> one or two. If possible I would like to do a setup like this in my databas
e
> to keep from having to have null fields for 4 phone numbers that the major
ity
> of records won’t have.
> Thanks in advanced,
> Nathan Rover
>

Joining multiple tables

I need your help on this. I am not getting the results i wanted in this script.

select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"

from student as s join studentrecord as sr

on (s.student#=sr.student#)

join course as c on (sr.course#=c.course#)

join courseenrollment as ce on (s.student#=ce.student#)

where ce.[status]=1

Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.

Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.

1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students

2nd, join the student record table by the student# - this joins to the resultset of the 1st join

3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.

Code Snippet

SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]

FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1

INNER JOIN studentrecord sr on s.[student#] = sr.[student#]

INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]

Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.

|||

This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records

Adamus

Joining multiple tables

I need your help on this. I am not getting the results i wanted in this script.

select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"

from student as s join studentrecord as sr

on (s.student#=sr.student#)

join course as c on (sr.course#=c.course#)

join courseenrollment as ce on (s.student#=ce.student#)

where ce.[status]=1

Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.

Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.

1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students

2nd, join the student record table by the student# - this joins to the resultset of the 1st join

3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.

Code Snippet

SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]

FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1

INNER JOIN studentrecord sr on s.[student#] = sr.[student#]

INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]

Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.

|||

This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records

Adamus

joining multiple tables

I have a quick question regarding the joining of multiple tables.

I have one main table that contains a TranID and some other fields. Then, I have about five tables that have detail information for each of the records in the main table. There is a one to one relationship between a detail record and the main record. The reason they are split between several detail tables is because the detail information is different based on TypeID.

My question is this: How can I join more than one of the detail tables?

This returns nothing....can someone explain why and hopefully provide a solution?

select * from MainTable m
Inner Join DetailTable1 d1 on m.TranID = d1.TranID
Inner Join DetailTable2 d2 on m.TranID = d2.TranID
Inner Join DetailTable3 d3 on m.TranID = d3.TranID
Inner Join DetailTable4 d4 on m.TranID = d4.TranID
Inner Join DetailTable5 d5 on m.TranID = d5.TranID

Thanks very much...If it returns nothing then one or more of the inner join conditions conditions are not met. That would be something like no d1TranID matching any m.TranID. Because they are all inner joins it only takes one bad join condition to break the whole thing.

To diagnose the problem you might try using Left Outer Joins and Right Outer Joins to see where matches are not being made between MainTable and each of the DetailTables. Something like:

Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Left Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Left Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Left Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Left Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Left Outer Join DetailTable5 d5 on m.TranID = d5.TranID

and then to see where the detail table(s) have TranIDs that the MainTable doesn't have:

Select m.TrandID,d1,TranID,d2,TranID,d3,TranID,d4,TranID,d5,TranID
from MainTable m
Right Outer Join DetailTable1 d1 on m.TranID = d1.TranID
Right Outer Join DetailTable2 d2 on m.TranID = d2.TranID
Right Outer Join DetailTable3 d3 on m.TranID = d3.TranID
Right Outer Join DetailTable4 d4 on m.TranID = d4.TranID
Right Outer Join DetailTable5 d5 on m.TranID = d5.TranID

This should indicate what the problem(s) are.

Joining multiple tables

Hi,
I need write a script get data from 3 different. All 3 tables will have
emailAddr, modify_date and sum details. But information may be duplicated in
the 3 tables, having same emailAddr but different modify_date eg. My end
result(new table) will contain the entry with latest modify_date if there is
a duplication of emailAddr, else the entry that is only available in single
table will also be insert into the new table.
Thanx..Can you give the primary key foriegn key trlationships with the table
definition and few sample data and expected result?
--
"yingying" wrote:

> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||The tables are not related to each other.. only field that is similiar is th
e
emailAddr.. this is the field i expected to check..
Eg.. (table structure)
tblA (a_Id, emailAddr, dateModify, fname, lname)
tblB (b_Id, emailAddr, dateModify, addr, contact)
tblC (c_Id, emailAddr, dateModify, work1, work2)
tblResult (r_Id, emailAddr, dateModify)
(data in db)
tblA
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 28/04/2006, bb, bb
tblB
1, aa@.abc.com, 30/04/2006, bb, bb
2, cc@.abc.com, 01/05/2006, cc, cc
3, bb@.abc.com, 03/05/2006, bb, bb
tblC
1, aa@.abc.com, 03/05/2006, aa, aa
2, dd@.abc.com, 01/05/2006, dd, dd
(Expected result stored into tblResult)
tblResult
1, aa@.abc.com, 03/05/2006
2, bb@.abc.com, 03/05/2006
3, cc@.abc.com, 01/05/2006
4, dd@.abc.com, 01/05/2006
"Omnibuzz" wrote:
> Can you give the primary key foriegn key trlationships with the table
> definition and few sample data and expected result?
> --
>
>
> "yingying" wrote:
>|||try this and let me know if this was what you required
select emailAddr,max(datemodify) as datemodify
from
(
select emailAddr,datemodify from tblA
union all
select emailAddr,datemodify from tblB
union all
select emailAddr,datemodify from tblC
) as A
group by emailaddr|||of course r_id in the tbl_result can be an identity
--
"yingying" wrote:

> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||Thanx.. I got the idea how it works..
Another question.. If i was to have same email addr in the same table how am
i goin to get the data with the latest date having the same email addr.
(table structure)
tblA (a_Id, emailAddr, dateModify, lname, fname)
(data in table)
tblA
1, aa@.abc.com, 03/05/2006, aa, aa
2, aa@.abc.com, 01/05/2006, bb, bb
3, aa@.abc.com, 29/04/2006, cc, cc
4, dd@.abc.com, 01/05/2006, dd, dd
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
(Expected result)
1, aa@.abc.com, 03/05/2006, aa, aa
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
"Omnibuzz" wrote:

> try this and let me know if this was what you required
> select emailAddr,max(datemodify) as datemodify
> from
> (
> select emailAddr,datemodify from tblA
> union all
> select emailAddr,datemodify from tblB
> union all
> select emailAddr,datemodify from tblC
> ) as A
> group by emailaddr
>|||The same query will work for your requirement.
--
"yingying" wrote:
> Thanx.. I got the idea how it works..
> Another question.. If i was to have same email addr in the same table how
am
> i goin to get the data with the latest date having the same email addr.
> (table structure)
> tblA (a_Id, emailAddr, dateModify, lname, fname)
> (data in table)
> tblA
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 2, aa@.abc.com, 01/05/2006, bb, bb
> 3, aa@.abc.com, 29/04/2006, cc, cc
> 4, dd@.abc.com, 01/05/2006, dd, dd
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
> (Expected result)
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
>
> "Omnibuzz" wrote:
>|||I used same query but it did not return the right values i need..
select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
from
(
select emailAddr, datestamp, fname, lname from tblA
) As A
group by emailAddr, fname, lname
(data in tblA)
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
(Expected result)
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
but wat i got was
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
1, aa@.abc.com, 29/04/2006, aa, aa
4, aa@.abc.com, 03/05/2006, aa2, aa2
If cases with entry of same email addr, i will need to get the entry with
the latest date. When i didn't include the fname and lname, the result was
ok.. but after i add those 2 fields, the result was not wat i need.
Isit that i need to do this in 2 different steps in order to get the
required data'
Another question, when i have fname, lname in the 'select' query and not
having them in the 'group by', it gave me an error..
-->>
'A.fname' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
what does this mean?
Thanx..
"Omnibuzz" wrote:
> The same query will work for your requirement.
> --
>
>
> "yingying" wrote:
>|||If you want the additional details with the latest date, try
select
emailAddr, datestamp as datemodify, fname, lname
into #temp
from tblA
where datestamp = (
select max(datestamp) from tblA as A2
where A2.emailAddr = tblA.emailAddr
)
nearly equivalent variations (differing in the case
where there are ties for the datestamp value, or
nullable columns) include
...
from tblA
where datestamp = (
select top 1 datestamp from tblA as A2
where A2.emailAddr = tblA.emailAddr
order by datestamp desc
)
and
...
from tblA
where not exists (
select * from tblA as A2
where A2.emailAddr = tblA.emailAddr
and A2.datestamp > tblA.datestamp
)
or in SQL Server 2005,
with Ranked(emailAddr, datestamp, fname, lname, rk) as (
select
emailAddr, datestamp, fname, lname,
rank() over (partition by emailAddr order by datestamp desc)
from tblA
)
select emailAddr, datestamp as datemodify, fname, lname
into #temp
from Ranked
where rk = 1
Steve Kass
Drew University
yingying wrote:
>I used same query but it did not return the right values i need..
>select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
>from
>(
>select emailAddr, datestamp, fname, lname from tblA
> ) As A
>group by emailAddr, fname, lname
>(data in tblA)
>1, aa@.abc.com, 29/04/2006, aa, aa
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>(Expected result)
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>but wat i got was
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>1, aa@.abc.com, 29/04/2006, aa, aa
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>If cases with entry of same email addr, i will need to get the entry with
>the latest date. When i didn't include the fname and lname, the result was
>ok.. but after i add those 2 fields, the result was not wat i need.
>Isit that i need to do this in 2 different steps in order to get the
>required data'
>Another question, when i have fname, lname in the 'select' query and not
>having them in the 'group by', it gave me an error..
>-->>
>'A.fname' is invalid in the select list because it is not contained in
>either an aggregate function or the GROUP BY clause.
>what does this mean?
>Thanx..
>"Omnibuzz" wrote:
>
>|||If you are selecting more columns than what you had specified, then you will
have to use a correlated sub-query.
--
"yingying" wrote:
> I used same query but it did not return the right values i need..
> select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
> from
> (
> select emailAddr, datestamp, fname, lname from tblA
> ) As A
> group by emailAddr, fname, lname
> (data in tblA)
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> (Expected result)
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> but wat i got was
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> If cases with entry of same email addr, i will need to get the entry with
> the latest date. When i didn't include the fname and lname, the result was
> ok.. but after i add those 2 fields, the result was not wat i need.
> Isit that i need to do this in 2 different steps in order to get the
> required data'
> Another question, when i have fname, lname in the 'select' query and not
> having them in the 'group by', it gave me an error..
> -->>
> 'A.fname' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
> what does this mean?
> Thanx..
> "Omnibuzz" wrote:
>

Joining multiple result sets together

Hi All,
I've been trying this for over a week now, and I simply cannot get this to work. Basically, what I am trying to do is get a result set that has the 4 columns, PID, LAST, SMOKER, HYPER for every person in the table (every person has a unique PID). My problem is that these columns are from different tables, so I have been trying to do 2 left joins to make the result set. This is what I came up with, but it has syntax problems from what I can gather.

SELECT E.PID, E.LAST, M.SMOKER, C.HYPER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)

After looking at the above query, I got the feeling that the C.HYPER might have been the problem as it is getting its data from the first left join. I tried to fix that with the statement below. It's probably terribly wrong too, but my SQL experience is limited, and i've never actually had to do a join from 3 tables before.

SELECT TT.PID, TT.LAST, TT.SMOKER_PAST, C.HYPER FROM (SELECT E.PID, E.LAST, M.SMOKER FROM ENROLLMENT E LEFT JOIN MEDICALHISTORY M ON (E.PID = M.PID) AS TT) LEFT JOIN CARDIACHISTORY C ON (TT.PID = C.PID)

What I was aiming for here was to make the second select result set act like a table of its own (TT). This didn't work too well either :)

Any help at all would be greatly appreciated.

Thanks,
Michael

BTW this forum is great. I only just found it, but I think I'll be coming here a lot more now. The amount of knowledge in here is unbelievable.try this --SELECT E.PID, E.LAST, M.SMOKER, C.HYPER
FROM ENROLLMENT E
LEFT
JOIN MEDICALHISTORY M
ON E.PID = M.PID
LEFT
JOIN CARDIACHISTORY C
ON E.PID = C.PIDrudy
http://r937.com/|||Thanks for the reply. I don't have access to the database right now, but I'll get back to you on Monday to let you know how it worked.|||Hi Rudy,
Thanks for the help. I just tried it out on the database, and whilst it didn't work for me, it got me thinking and I managed to get something that was workable. I'd post up what I ended up using, but it's a mess and hardly the most efficient way of doing it (it had to be generated by code on the fly, so the actual solution I ended up with is really redundant, but is easy to build in the code when certain buttons are pressed).

Thanks for the help, mate.

Cheers,
Michael

Joining multiple pks

I have 2 tables that I need to pull data from.

Table 1
PK ID
PK Name
PK Address
PK State
Postion
Status

Table2
PK ID (FK)
PK Name (FK)
PK Address (FK)
PK State (FK)
PK Actions
PK History

Now i dont believe this db was designed in the most efficient way to begin with, but I'm trying to write a query to effectively pull data from it.

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Position, Table1.Status, Table2.Address, Table2.State, Table2.Actions, Table2.History
FROM Table1, Table2
WHERE Table1.ID=Table2.ID AND
Table1.Name=Table2.Name AND
Table1.Address=Table2.Address AND
Table1.State=Table2.State AND
Table1.ID = "123"

This is what i got so far. Not very pretty, and the SQL is still running. Basically I need to join these 2 tables, by 4 fields. The field names have been changed bc the real ones are kind of confusing.

I'd appreciate any help possible,

Thanks,
Charlieif your query is really slow, then most likely the FK in Table2 needs an index, but your SQL is fine -- i prefer JOIN syntax over table list syntax, but the query is fine

well, except for the DISTINCT, you probably don't need that (and it does involve a total sort of all columns in all rows, so removing it will definitely speed up the query)|||I'll second R937's suggestion for a non-unique index on Table 2 (ID, Name, Address, State). That should improve performance a great deal.

I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State), and that you make sure that ID is the first (leftmost) column in that index so that the optimizer can quickly find the row by id.

Just curious, but what database engine are you using? There are some engine specific tips that could apply, especially if you have very large tables.

-PatP|||On some systems it might even help to add the condition
AND Table2.ID = '123'
(which logically speaking is of course redundant).
Certainly when that column has an index, this could speed up the query a lot!|||I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State) ... i think the PK adequately covers this requirement, no?|||i think the PK adequately covers this requirement, no?No. Not all database engines generate an index to enforce the PK definition, although most of them do. As a side note, a PK index might include the ID column, but not as the first column in the definition (different engines that support DRI have different rules for how they manage the PK definition).

-PatP|||just so that i don't look like a complete idiot the next time someone asks me if PK uniqueness is enforced by means of a unique index, would you kindly give an example of a database engine which does not do this

also, please be careful not to proliferate the idea of declaring a separate unique index on the PK column(s), because in most databases this will be redundant, superfluous, inefficient, and redundant|||Many implementations of MySQL do nothing whatsoever with DRI. They allow you to declare it, then completely ignore that declaration because the data file they are using doesn't support it. Very few implementations that I've seen even can support FK definitions, and a significant number of commercial implementations choose not to implement PK declarations to improve insert performance and reduce their internal tech support load.

Some versions of DB2 use "interesting" ways to determine how the PK will be enforced, for example they'll create no index for very small tables (because a table scan is cheaper than an index lookup for small amounts of data in that specific implementation), and don't have a way for the engine to change that decision if the table grows. They also tend to force integers and dates toward the end of any index unless you get very specific about it.

That's exactly why I was asking what engine the poster was using in my first response. There can be all kinds of engine specific quirks that can cause performance problems like this, and they are decidedly NOT intuitively obvious. At least if we knew what engine they were having problems with, we might have a better chance at helping them.

Besides all of the things you pointed out about creating a separate unique index to "back up" an existing PK, that can be overkill too. ;)

-PatP|||Many implementations of MySQL do nothing whatsoever with DRI. so what? PK indexes <> support DRI

i'm pretty sure MySQL uses an index to enforce PK uniqueness

the DB2 example (no index created for the PK) is nice, though|||No, not at all! What I'm saying is that most of the MySQL installations that I have experience using do not have any support at all for DRI.

Out of the box, the current generation of MySQL uses MyISAM. The default install does provide an index to enforce the PK, but most of the "web farm" operators disable that to increase performance and to reduce the amount of tech support that they need to provide their users. We won't go into what I think of that practice, it would just infuriate me for no good reason! At least as far as I know, MyISAM does not provide any support for FKs no matter what you do with it.

I guess that my point was that there are a number of ways to set up databases that use SQL or SQL-like languages, with varying degrees of support for DRI. We can't assume that just because a poster thinks that they have DRI that they've even formally declared it, and without confirming the details such as database engine, etc we can't assume that they have the features that we take for granted.

-PatP|||stop with the DRI already

engine creates unique index for PK, yes or no? that's all, yes or no -- forget the DRI stuff

that story about web farms disabling indexes to enforce the PK, i'm going to look into that, because that's insane|||How can you have a PK without DRI? If I can't declare it using standard SQL constructs, then have the database engine enforce that declaration, it is simply a pleasant notion to me. There are ways to coerce many of the database engines into doing what we expect a Relational Algebra Primary Key to do, but those fall into the category of what I consider to be "engine specific tricks", not what I consider to be a PK.

-PatP|||aw come on pat, give it up

how can you have a PK without DRI? like this --

create table patp
( id integer not null primary key
, foo varchar(9)
, bar varchar(37)
)

voila, i have declared a primary key

you have admitted that yes, this does create a unique index, unless one happens to be using a nefarious web farm

is that more or less what you're saying?|||how can you have a PK without DRI? like this --

create table patp
( id integer not null primary key
, foo varchar(9)
, bar varchar(37)
)You used DRI.

-PatP|||Oh yeah, if you are using Microsoft SQL, Oracle, or Sybase, then you'll create a unique index to enforce the primary key that you declared using Declared Referential Integrity.

-PatP|||<voice type="mr. burns">ehhhhhhhhhhhhxcellent</voice>

can we now revisit some earlier posts, like, say, starting around post #5?

pat: you (original poster) should declare a unique index
me: wouldn't the PK do?
pat: no, not all engines create an index for the PK
me: oh? which ones don't?
pat: mysql doesn't
me: what?! surely it does...
pat: not if it has been turned off
me: what? you can do that?|||create table patp
( id integer not null primary key
, foo varchar(9)
, bar varchar(37)
)
...this does create a unique index, unless one happens to be using a nefarious web farm
DB2 for z/OS does not automatically create an index in this case.

The unique index has to be created (manually) before inserting into this table.

Only when using schema's (which were only introduced in DB2 for z/OS v8) one gets the "automatic index creation".
But even in v8, schema's need not be used.|||After some offline discussion with R937, I want to reiterate what I said earlier, with added emphasis.

I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State), and that you make sure that ID is the first (leftmost) column in that index so that the optimizer can quickly find the row by id.Using most of the major database products, these indicies will be created automagically for you. What I wanted the poster to do was verify that the expected indicies actually do exist for them, for their engine in their database. We (both R937 and I) expect those indicies to exist, but I'm a "belt and suspenders" type that is willing to take an extra minute or two to ensure that what I expect really exists where the rubber meets the road.

-PatP|||thanks pat

and the fact that there are engines where you can create a primary key but it's not going to be unique, well, that just frosts my petunias...

:)|||This is an example of my favourite type of thread. In ever so many ways :)|||Very interesting material in this thread.

I'm using DB2 v8.2 on AIX by the way.

joining multiple mdfs

Hi all,
I have a database that I'll be moving to a new machine. The existing setup
has the data in 3 separate mdf files. The new machine has more than enough
space to accommodate the 3 files in the same location, as well as enough
room
to grow for the foreseeable future.
I've done a test load of the existing data onto the new server from the .bak
dump file. Since the data was in 3 files I had to restore it into 3 files. I
was wondering if it's possible (and how I would go about it) to join the
separate mdfs into one large one.
Thanks in advance for any replies.
Ricknever done this but I think you can do this with ALTER DATABASE. Check it
out in BOL or http://support.microsoft.com/?kbid=814576
"Rick" <reddinger@.serif.com> wrote in message
news:%237o9PqbFEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I have a database that I'll be moving to a new machine. The existing setup
> has the data in 3 separate mdf files. The new machine has more than enough
> space to accommodate the 3 files in the same location, as well as enough
> room
> to grow for the foreseeable future.
> I've done a test load of the existing data onto the new server from the
.bak
> dump file. Since the data was in 3 files I had to restore it into 3 files.
I
> was wondering if it's possible (and how I would go about it) to join the
> separate mdfs into one large one.
> Thanks in advance for any replies.
> Rick
>
>|||Thanks, that worked. Come to realize you can also do this through the GUI in
the "Shrink files" dialog, saves some typing
Thanks again.
"ME" <mail@.moon.net> wrote in message
news:OHpXEAcFEHA.2408@.TK2MSFTNGP10.phx.gbl...
> never done this but I think you can do this with ALTER DATABASE. Check it
> out in BOL or http://support.microsoft.com/?kbid=814576
> "Rick" <reddinger@.serif.com> wrote in message
> news:%237o9PqbFEHA.624@.TK2MSFTNGP10.phx.gbl...
setup
enough
> .bak
files.
> I
the
>

joining multiple mdfs

Hi all,
I have a database that I'll be moving to a new machine. The existing setup
has the data in 3 separate mdf files. The new machine has more than enough
space to accommodate the 3 files in the same location, as well as enough
room
to grow for the foreseeable future.
I've done a test load of the existing data onto the new server from the .bak
dump file. Since the data was in 3 files I had to restore it into 3 files. I
was wondering if it's possible (and how I would go about it) to join the
separate mdfs into one large one.
Thanks in advance for any replies.
RickOne possible solution is to restore the database on to the new server and
then use DTS transfer the objects to a new database.
There is not a way to combine the mdf files.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Joing Multiple Columns

Hi All,

Bit of a newbie question i'm afraid, so sorry if this is a really
stupid question,

I am not sure if I am trying to do this in the right place as I am sure
SQL Server has a far better way of doing this

What I am trying to do is join the contents of several columns and
present the entry into a new column. the entries of all the columns I
am trying to join are different types such as dates and numbers. I want
to join all columns as a text string. To make it just a even more
tricky I wish to insert a # between all fields.

Just in case my poor description has got you thinking What the F*&K
here is an example of what I am trying to do

C1 C2 C3 C4 C5
Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890
I have been trying to do this in the Formula in the design veiw of the
table.

Any Advice would be fantastic.

Many Thank

IanTry:

select
C1 + '#' + cast (C2 as varchar (10)) + '#' + convert (char (10), C3,
103) + '#' + cast (C4 as varchar (10))
from
MyTable

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1140655591.245227.133490@.f14g2000cwb.googlegr oups.com...
Hi All,

Bit of a newbie question i'm afraid, so sorry if this is a really
stupid question,

I am not sure if I am trying to do this in the right place as I am sure
SQL Server has a far better way of doing this

What I am trying to do is join the contents of several columns and
present the entry into a new column. the entries of all the columns I
am trying to join are different types such as dates and numbers. I want
to join all columns as a text string. To make it just a even more
tricky I wish to insert a # between all fields.

Just in case my poor description has got you thinking What the F*&K
here is an example of what I am trying to do

C1 C2 C3 C4 C5
Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890
I have been trying to do this in the Formula in the design veiw of the
table.

Any Advice would be fantastic.

Many Thank

Ian

Join to select a 'weighted' column

Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.

declare @.inv table (item varchar(50), supplierid int)

declare @.supplier table (supplierid int, weight int)

set nocount on

insert into @.inv values ('item1', 1)

insert into @.inv values ('item1', 2)

insert into @.inv values ('item2', 2)

insert into @.inv values ('item2', 3)

insert into @.supplier values(1, 30)

insert into @.supplier values(2, 20)

insert into @.supplier values(3, 10)

-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3

select item, ps2.supplierid from @.supplier ps2 join

(select item, min(ps.weight)'weight'

from @.inv inv join @.supplier ps on inv.supplierid=ps.supplierid

group by item) iw on ps2.weight=iw.weight

Is there a better alternative to this?

Thanks in advance,

Mike

You can do the following in SQL Server 2005:

select item, supplierid
from (
select i.item, s.supplierid, row_number() over(partition by i.item order by s.weight) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
) as si
where wt = 1

But the most efficient way is to do below:

select item, cast(substring(wt, 5, 4) as int) as supplierid
from (
select i.item, min(cast(s.weight as binary(4)) + cast(s.supplierid as binary(4))) as wt
from @.supplier as s
join @.inv as i
on i.supplierid = s.supplierid
group by i.item
) as si

The second method will work only if the weight/supplierid values are greater than or equal to zero due to the conversion to binary. You can make it work for negative values also by modifying the expression. The trick is to get a sortable value using a combination of weight/supplierid that you can apply the aggregate function on and then get the individual values out.

|||

Thank you very much,

I will look at both.

Mike

Join Table Key to Multiple Table Names

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

ThxWouldn't it be a lot less trouble to have one table for stock, one table for jobs, and one table to show job-stock-usage? That way you could have a practical infinity of jobs and stocks with only three tables.

This is a fundamental database design process called normalization. It is the key to maintaining your sanity as your projects grow!

-PatP|||I totally agree...I don't like this design whatsoever but its all I have to work with because I didn't design it and it's the way things are being done right now.|||I believe that particular schema is taken directly from Chapter 6, page 142 of the ever popular and best-selling book, "WORLD'S WORST DATABASE DESIGNS".

You are going to have to use dynamic SQL to solve this. Essentially, you will construct your SQL statement as a string concatenating the value of jobID in as the table name, and then execute the string.

Developers like that ought to be shot.|||thx for the reply blindman. I was leaning that way but I'm fairly new to using ms sql. I'm currently reading up on dynamic sql and seeing how it works. I need to generate a view out of this somehow.

Thx again|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)|||-----
lol|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)I don't know that it has ever been officially published, it is more of a "work in progress" kind of thing. Every time they think they're ready to publish, somebody runs in yelling "You've got to see this one" and they go right back to editing!

-PatP|||73% of the database designs in that book, WORLD'S WORST DATABASE DESIGNS, were written by php programmers, who design tables like that all the time

18% of them were written by people who've spent too much time at dbdebumph.com and have drunk the koolaid -- not a null in sight!!

9% were written by oracle developers who thought that storing a whole nested table inside a field was a neat idea and supports their concept of object-oriented encapsulation|||Man, you NAILED it with #3!!! I'll buy you a round (12 pack or a bottle of your choice) whenever you're in town!!! Though I see so much of #1 that it's not even funny any more...I wish images from Unreal Tornament were real sometimes...|||thank you, thank you, i'll be here all week, try the veal and don't forget to tip your waitress

:cool:|||no sooner do i say it, and another example of a design in the first category pops up: this thread (http://forums.devshed.com/t199186/s.html)

happens all the time|||This is SO ironic...I just declined an offer from a real estate marketing company which excercised a similar design "strategy" ... They even asked me at the interview how I would resolve this situation... Of course my answer was to FIRE the designer of the current database first ;)