Multiple Joins - Need Help

have the following code for ONE Inner Join, but I want to add another join for another Table and Fields... can you help me with the syntax:

SELECT DISTINCT

CTR.ReqID, CTR.SpecimenID, CTR.LabID, CTR.ProcedureID, CTR.TestID, CTR.Isolate, CTR.Problem,

CTR.ProblemComments, CP.ProcedureID, CP.Description AS CPProcedureDescription

FROM ClinicalTestsRequested CTR inner join ClinicalProcedures CP

ON CTR.ProcedureID=CP.ProcedureID

WHERE (CTR.SpecimenID = @.Accession)

I want to add another Join to the above where:

Table = ClinicalTests CT
Fields = CT.TestID, CT.Description AS CTTestDescription

and Compare = CTR.TestID to CT.TestID

Thanks !!

after your on clause for the first inner join add:

inner join clinicalTests ct on ctr.testid = ct.testid

add your columns to the list in the select.

Better yet, use sql server management studio express to design the query for you (it doensn't matter if your database is sql2005 or 2000). Create a new query for one of your tables and then use the query designer. This will allow you to drag and drop joins, make them into outer joins, add columns, etc, using a design tool.

--JJ

Multiple fields with the Additive Resolver?

Is there a way to specify more than one field for the Additive Resolver
argument? I have multiple fields in a table that I would like to be summed
from multiple subscriber updates.
regrettably, no. You can only sum the contents of a single column together.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Thirsh" <Thirsh@.discussions.microsoft.com> wrote in message
news:4E7CF966-7E82-471B-B885-E6AF1D9856AA@.microsoft.com...
> Is there a way to specify more than one field for the Additive Resolver
> argument? I have multiple fields in a table that I would like to be
summed
> from multiple subscriber updates.

Multiple fields in where statement

In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record. Below was my first attempt, which of course doesn't work.

Delete from oop_test where acct_no, curr_seq, acct_type in (select acct_no, curr_seq, acct_type from oop_temp)What about this?

Delete from oop_test
where exists(select 'ok' from oop_temp i where i.acct_no=oop_test.acct_no and ...)|||YES! THAT IS IT! Thanks a TON! I am still a bit confused about the use of the variable "i" in the query. Explain it's use to me. Maybe it is my lack of SQL skills.|||This can also be done with a simple join:

Delete oop_test
from oop_test
inner join oop_temp
on oop_test.acct_no = oop_test.acct_no
and oop_test.curr_seq = oop_test.curr_seq
and oop_test.acct_type = oop_test.acct_type

blindman|||I will have to give this a shot. While the first method works like a champ, I had a difficult time figuring it out. This is real straight forward.

THANKS!|||The "i" in Snail's code is just an alias for the table name oop_temp. Many coders assign shorter alias name for tables in their queries to reduce the amount of typing required. Otherwise, Snail's code just checks each record in oop_test to see is a matching record "exists" in the oop_temp based on the three columns specified.

Both methods work. I think the join method might be more efficient, though for small-to-midsize tables probably not enough to be noticable. Go with whichever method you find easiest to read.

blindman|||Actually, the part I had a problem with was the select 'ok' part. Do you know what that means?|||All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman|||Originally posted by blindman
All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

How it works is like this...

The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

where exists(select * from oop_temp...

...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

blindman

I agree - there is no difference in performance between select 'OK' and select * inside exists, but 'OK' looks much better for me ;)|||So SSchuler, its just a matter of style, and lookin' good! :cool:

blindman|||HEHEH! Good one. Ya learn something new everyday. I can't believe that tripped me up like it did. Now that you point it out, it is quite obvious. Don't let anyone say that us computer jockeys don't have style! ;-)|||* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)|||Originally posted by ms_sql_dba
* also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)

I believe that's changed...Where SELECT * is actually optimized to perform better.|||Brett is correct. Select * is optimized, and the columns are not expanded.

blindman|||Originally posted by SSchuler
In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record.

To find "a record in which three specific fields match the same three fields in another table," you would use a JOIN clause in a SELECT query.

To delete those fields, you use this select query as a sub-select in a DELETE query, something like this:

DELETE FROM victim WHERE victim_id IN
(SELECT id FROM
table1 A JOIN table2 B USING
A.F1 = B.F1 AND A.F2 = B.F2 AND A.F3 = B.F3
)

(Sub-select italicized for emphasis. Caution: extemporaneous SQL coding... do not try this at home.) ;-)|||A little late, sundial. Read other member's posts first!

blindman

multiple fields in SELECT WHERE IN

Hi All

I'm in the process of trying to write a query (in access 2000) that copys data between tables, then updates related tables.

The problem:

One of the tables is linked on 2 fields, and I need to do a where not in query, so I can't use a join.

Is it possible to do something along the lines of:

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

Any help much appreciated

Mark MiddlemistYES YOU CAN

select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

this works fine, but check that field1 and ref1 should have same datatype etc.,|||This won't be upgradable to sql server though.
You could

select * from table1
where not exists
(select * from table2 where table1.field1 = table2.ref1 and table1.field2 = table2.ref2)

or

select table1.*
from table1 left outer join table2
on table1.field1 = table2.ref1 and table1.field2 = table2.ref2
where table1.field1 is null

these will still bring back results if the fields allow null whereas the in clause may well not.
In all cases the results may not be correct for nullable fields.

multiple fields from common lookup table

I haven't written SQL in some time and I'm extremely rusty, so help
would be appreciated and thanks in advance.
I have a table (main) that has multiple fields that contain a lookup id
to a common lookup table (lookup). How do I write the query to pull
back one result set that has the form below?
select ?
where main.key = @.key
result set
--
key (from main)
behavior description (from lookup)
appearance description (from lookup)
other description (from lookup)
main
--
key
behaviorID
appearanceID
otherID
lookup
--
lookupID
descriptionPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Sba,
Try:
DECLARE @.KEYVAL INT
SET @.KEYVAL = 1
SELECT KEYVAL, L1.DESCRIPTION AS 'BEHAVIOR', L2.DESCRIPTION AS 'APPEARANCE',
L3.DESCRIPTION AS 'OTHER'
FROM MAIN M
JOIN LOOKUP L1
ON M.BEHAVORID = L1.LOOKUPID
JOIN LOOKUP L2
ON M.APPEARANCEID = L2.LOOKUPID
JOIN LOOKUP L3
ON M.OTHERID = L3.LOOKUPID
WHERE KEYVAL = @.KEYVAL
HTH
Jerry
"sba" <pub9@.s105192480.onlinehome.us> wrote in message
news:1129764599.221796.33700@.g49g2000cwa.googlegroups.com...
>I haven't written SQL in some time and I'm extremely rusty, so help
> would be appreciated and thanks in advance.
> I have a table (main) that has multiple fields that contain a lookup id
> to a common lookup table (lookup). How do I write the query to pull
> back one result set that has the form below?
> select ?
> where main.key = @.key
>
> result set
> --
> key (from main)
> behavior description (from lookup)
> appearance description (from lookup)
> other description (from lookup)
>
> main
> --
> key
> behaviorID
> appearanceID
> otherID
>
> lookup
> --
> lookupID
> description
>|||Looks like you have the famous One True Lookup Table. A classic database
design error.
Anyway, you can join multiple times to the same table:
SELECT M.key, T1.description, T2.description, T3.description
FROM main AS M
JOIN lookup AS T1
ON M.behaviourid = T1.id
JOIN lookup AS T2
ON M.appearanceid = T2.id
JOIN lookup AS T3
ON M.otherid = T3.id ;
David Portas
SQL Server MVP
--

multiple field statment

I have a table that has 9 fields.
in some cases a record may almost be a duplicate of another record except
for one of the fields.
so I was thinking I need to write a statment to where I have to say what
each field equals, but I haven't had any luck
here is what I tried.
Select * from MyTable where field1 = "this" and field2 = "a" and field3 =
"test"
How do I write that statement correctly?
Thanks
Replace " with '. SQL uses a single quote to delimit literal strings rather
than a double quote. If that doesn't solve your problem then please tell us
exactly the wording of any error message you are getting.
David Portas
SQL Server MVP
sql

multiple field statment

I have a table that has 9 fields.
in some cases a record may almost be a duplicate of another record except
for one of the fields.
so I was thinking I need to write a statment to where I have to say what
each field equals, but I haven't had any luck
here is what I tried.
Select * from MyTable where field1 = "this" and field2 = "a" and field3 = "test"
How do I write that statement correctly'
ThanksReplace " with '. SQL uses a single quote to delimit literal strings rather
than a double quote. If that doesn't solve your problem then please tell us
exactly the wording of any error message you are getting.
--
David Portas
SQL Server MVP
--

multiple dataset fields in one table

How to use multiple dataset fields in one table.

Example:

I have one table --Table1.

Two DataSets --DataSet1,DataSet2

Table1 refers DataSet1.I want to use DataSet2 field in Table1.It is taking SUM if did this but I don't need sum.

A table can only be associated with 1 dataset.|||

thanx Adam.

Can you give me the solution

|||

It depends on the data and what else you are doing in the report. What I've done in the past is either

combine the data into 1 dataset in the query or|||

if you only want to use a SUM-Value from your second dataset,

this shouldnt be a problem

go to the field in your table where you want the SUM-Value

-> Expression -> Datasets -> <Dataset2> -> Sum(<yourValueToSum>)

this inserts a Sum-Function for your value over the scope of dataset 2

greets

|||

you could do something like this

=Sum(Fields!fieldnam.Value, "Dataset Name")

and it would get the value from the specified dataset

Multiple data insertion with For clause

Suppose I have a table named test with two fields(sl int,age int).
I want to insert 10 records all at a time.The records are in an incremental manner.I like to insert 1,2,3,4,5,6,7,8,9,10 for sl column and
22,23,24,25,26,27,28,29,30,31 for age column.But the procedure should follow C protype using for (j=1;j<10,j++) clause.
Is it possible to insert in SQL SERVER following c protype?
What is the fastest way for inserting multiple sequevcial data?
SubhasishFor relatively small sequential sets, do something like this...

INSERT INTO TEST(sl, age)
SELECT a.i, a.i+21
FROM (
SELECT i = 1 UNION
SELECT i = 2 UNION
SELECT i = 3 UNION
SELECT i = 4 UNION
SELECT i = 5 UNION
SELECT i = 6 UNION
SELECT i = 7 UNION
SELECT i = 8 UNION
SELECT i = 9 UNION
SELECT i = 10 ) as a

You can build up the derived table query quickly with cut-and-paste, then go back and fix the values

For bigger sequential sets, build yourself a temporary table of sequential integers like this:

CREATE TABLE #i
(x INT IDENTITY(1,1),
y INT)

INSERT INTO #i
VALUES(NULL)

INSERT INTO #i
SELECT y FROM #i

Running the last statement over and over will populate table #i with sequential integers in the x column. 11 executions gets you 1K rows, 21 gets you 1M rows, ... Then use the temporary table to drive your insert.

INSERT INTO test(sl, age)
SELECT #i.x, #i.x+21
FROM #i
WHERE #i.x < 100 -- for 100 rows|||I think s/he's looking for a loop as well...

USE Northwind
GO

CREATE TABLE myTable99 (sl int,age int)
GO

DECLARE @.x int, @.y int

SELECT @.x = 1, @.y = 1

WHILE @.x < 100
BEGIN
INSERT INTO myTable99 (sl, age)
SELECT @.X, 1*@.y UNION ALL
SELECT @.X, 2*@.y UNION ALL
SELECT @.X, 3*@.y UNION ALL
SELECT @.X, 4*@.y UNION ALL
SELECT @.X, 5*@.y UNION ALL
SELECT @.X, 6*@.y UNION ALL
SELECT @.X, 7*@.y UNION ALL
SELECT @.X, 8*@.y UNION ALL
SELECT @.X, 9*@.y UNION ALL
SELECT @.X, 10*@.y

SELECT @.x = @.x + 1, @.y = @.y + 1
END

SELECT COUNT(*) FROM myTable99
GO

DROP TABLE myTable99
GO

multiple data flows

Easy: read a SQL table with 500 fields, transform, write to flat file using SSIS.

But, I have hundreds of transformations to define using Lookups and Aggregates, Derived Column transformations. I wan to group the data flow transformations in usable (reasonable size) groups (packages, containers, subroutines, whatever you want to call it).

I cannot figure out a simple easy way of doing this most "simple" obvious thing.

Am I the only one on the planet who needs to do this?

Thx.

Newbie.

Hi Paul,

No you are not the only one that has seen a need to modularize the data flows. The current product is the version 1.0 and unfortunately it does not give you an intuitive way to do this.

Currently, you may be able to achieve some level of modularization by breaking your data flows and connecting them through staging objects (raw files, flat files, temporary tables, etc).

We are actively looking into improving this experience, though.

It will be there in one of the future releases.

Thanks,

Bob

|||I think your question is how to break up what would ordinarily be a very large data flow (hundreds of transformations?!), into multiple smaller data flows.

If so, you would use Raw Files to save the data pipeline to the disk so one data flow can end and another can begin. The Raw File destination will write to a file on the disk at the end of one data flow and the Raw File source will read from it to begin the next data flow. The raw file contains the pipeline metadata, so you don't have to worry about defining your columns (500?!) each time.
|||Okay, this makes sense and I was looking to do exactly what the two responses above suggest. Just thought this should be a LOT easier than this... and it sounds like it will be some day...

In case anyone is listening, what I would really like is to highlight a set of (connected) data flows and right-click and select Save As Sub Data Flow... and then give it a name... That's all. I understand that the mechanics behind the scene makes this not so easy -but, whatever, it should be possible to do this. MS: please make it so.

Thanks !
|||Thanks!|||

Paul Des wrote:

Okay, this makes sense and I was looking to do exactly what the two responses above suggest. Just thought this should be a LOT easier than this... and it sounds like it will be some day...

In case anyone is listening, what I would really like is to highlight a set of (connected) data flows and right-click and select Save As Sub Data Flow... and then give it a name... That's all. I understand that the mechanics behind the scene makes this not so easy -but, whatever, it should be possible to do this. MS: please make it so.

Thanks !

Paul,

I don't think I'm giving too much away by saying this will appear in the product one day. I'm hoping beyond hope that it will be in katmai.

Note to MSFT, whatever it takes to get this into katmai - do it!!!!

-Jamie

|||

Paul - thanks for the feedback. We have been working on this as a key enhancement to the product and the experience is somewhat similar to what you describe above. As Jamie knows, not everything we're working on are for the next release, but it will not be very long before you have this available.

multiple columns in details section

I have three fields in my report that make up each record. I would like to display these in detail section.

so instead of

1. alam Dept1 10000
2. khaiser dept2 20000
3. mujeeb dept2 20000

I would have

alam khaiser mujeeb ...
dept1 dept2 dept2 ...
10000 20000 20000 ..Use Crosstab Report|||1. Right-click on the dtails section, to open the Section Expert dialog box.

2. On the 'Sections' list, click the details section.

3. On the 'Common' tab, select 'Format with multiple columns' check box. A new tab called 'Layout' appears.

4. On the 'Layout' tab, specify the formatting for the columns:

Enter the width of the column in the 'Width' box. (you can set the height by dragging the section borders in the Design view of the report)

Enter the space between labels going across the page in the 'Horizontal gap' checkbox.

Enter the space between labels going down the page in the 'Vertical gap' box.

Select the printing direction, 'Across then Down' or 'Down then Across'.

Good Luck|||after using the Scetion expert i got mutliple columns but the sum of salary should be in right side but it is not coming ......

multiple column histograms in Yukon

Hi,
I have a simple query (sql 2000):
select * from table where feld1 = 'X' and feld2 <> 'Y'
Both fields are not very selective, but together they are
very selective and will find only 4 rows out of 1700000
rows. I have an multiple column index on both fields but
the optimizer chooses always a table scan. I heard Yukon
will handle such situations much better (multiple column
histograms) but I tried the version I got from PDC and
YUKON is still choosing a table scan too.
Any hints?
Andreas
Austria> the optimizer chooses always a table scan. I heard Yukon
> will handle such situations much better (multiple column
> histograms) but I tried the version I got from PDC and
> YUKON is still choosing a table scan too.
I wouldn't try to judge the delivery of all of Yukon's enhancements based
solely on the PDC build. I'm sure many of the optimizations are
forthcoming.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Multipe text file import to single table

All,
I need to import the contents of 1000+ text files into a single table
in SQL Server 7.0. The files are plain ascii, fields seperated by
"/".
Does anyone have ideas of how this can be achieved?
Regards,
A.Multiple BULK INSERT commands?
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.|||Do they all have exactly the same format?
If so it may be easier to concatenate them into one single file... From a
DOS prompt you could use:
TYPE *.TXT > BIGFILE.TXT
(Assuming they all have .TXT extension and that they're the only files in
the folder with .TXT extension, of course)
From there you could then use either BULK COPY or BCP.
If for some reason you don't want to or can't concatenate them, you could
write a batch file that would call BCP, replacing the filename parameter in
the batch file with %1, then call the batch file for each file using (again
from a DOS prompt):
FOR %i IN (DIR *.TXT) DO INSERT.BAT %i
Or you could write the entire thing in a SQL script using a cursor and a
call to xp_cmdshell to get a list of the files... IMO the DOS methods are a
lot easier.
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.