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 Deletions From Different Tables in SQL Server Trigger

I have written a trigger that's supposed to go out and delete
corresponding records from multiple tables once I delete a specific
record from a table called tblAdmissions.

This does not work and I'm not sure why...

Here's the code that's supposed to run, let's say, if a user (via a VB
6.0 interface) decides to delete a record. If the record in the
tblAdmissions table has the primary key (AdmissionID) of "123", then
the code below is supposed to search other tables that have related
information in them and also have an AdmissionID of "123" and delete
that information as well.

Any ideas? Here's the code:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
-- and here is the table name
ON tblAdmissions
-- the operation type goes here
FOR DELETE

AS
-- I just need one variable this time
DECLARE @.AdmissionID int
-- Now I'll make use of the deleted virtual table
SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

-- And now I'll use that value to delete the data in
-- the tblASIFollowUp Table
DELETE FROM tblASIFollowUp
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblProgramDischarge Table
DELETE FROM tblProgramDischarge
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblRoomAssignment Table
DELETE FROM tblRoomAssignment
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblTOADS Table
DELETE FROM tblTOADS
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblUnitedWaySurvey Table
DELETE FROM tblUnitedWaySurvey
WHERE AdmissionID = @.AdmissionID

-- And now I'll use that value to delete the data in
-- the tblWFGMSurvey Table
DELETE FROM tblWFGMSurvey
WHERE AdmissionID = @.AdmissionIDDoes it all not work or if you break it down into sections does it
still not work. Also how does it handle null values. When I started
using triggers comparisons with Nulls were a right pain. I take it that
all these tables are all in the same database with the same
permissions.

Ginters

bmccollum wrote:
> I have written a trigger that's supposed to go out and delete
> corresponding records from multiple tables once I delete a specific
> record from a table called tblAdmissions.
> This does not work and I'm not sure why...
> Here's the code that's supposed to run, let's say, if a user (via a
VB
> 6.0 interface) decides to delete a record. If the record in the
> tblAdmissions table has the primary key (AdmissionID) of "123", then
> the code below is supposed to search other tables that have related
> information in them and also have an AdmissionID of "123" and delete
> that information as well.
> Any ideas? Here's the code:
> CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
> -- and here is the table name
> ON tblAdmissions
> -- the operation type goes here
> FOR DELETE
> AS
> -- I just need one variable this time
> DECLARE @.AdmissionID int
> -- Now I'll make use of the deleted virtual table
> SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)
> -- And now I'll use that value to delete the data in
> -- the tblASIFollowUp Table
> DELETE FROM tblASIFollowUp
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblProgramDischarge Table
> DELETE FROM tblProgramDischarge
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblRoomAssignment Table
> DELETE FROM tblRoomAssignment
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblTOADS Table
> DELETE FROM tblTOADS
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblUnitedWaySurvey Table
> DELETE FROM tblUnitedWaySurvey
> WHERE AdmissionID = @.AdmissionID
> -- And now I'll use that value to delete the data in
> -- the tblWFGMSurvey Table
> DELETE FROM tblWFGMSurvey
> WHERE AdmissionID = @.AdmissionID|||What does "does not work" mean? Could you be a bit more specific. Why
not use cascading deletes on foreign keys for this? See the ON DELETE
CASCASE option in Books Online for details.

Your trigger will fail to delete all related rows if more than one row
is deleted from the Admissions table. Don't write triggers that way. To
do it in a trigger, try this:

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE

AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE admissionid = tblASIFollowUp.admissionid)

... etc

If you need more help, please post some code that will actually
reproduce the problem, including the CREATE, INSERT and DELETE
statements (simplified if possible please).
--
David Portas
SQL Server MVP
--|||On 31 Jan 2005 08:13:54 -0800, bmccollum wrote:

>This does not work and I'm not sure why...

Hi bmccollum,

Well, "does not work" is not exactly an accurate description of what's
happening. Is the delete rejected? Is the delete accepted, but the action
that the trigger should do is not done? Do you get error messages? Is
white smoke bellowing out of your server?

>Any ideas? Here's the code:
(snip)

Based on your code, I can do a wild guess. In fact, you've got two
problems. Both are here:

>SELECT @.AdmissionID = (SELECT @.AdmissionID FROM Deleted)

First, the second @. should be left out. This will simply set the variable
@.AdmissionID equal to itself.

But if you change it to

SELECT @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SET @.AdmissionID = (SELECT AdmissionID FROM Deleted)
or
SELECT @.AdmissionID = AdmissionID FROM Deleted

you'll still have problems. Not if you delete only one row, but you'll get
an error as soon as one DELETE operation deletes more than one row from
the admissions table. It's important to know that triggers fire once per
statement, not once per row. If three rows are deleted, the deleted
pseudo-table will hold three rows. This will cause the first two versions
of the assignment to error; the third will simply assign the value from
one of these three rows to @.AdmissionID.

Even if your present application will never delete more than one row at a
time, you should always ensure that your triggers handle multi-row
inserts, updates and deletes well. Someday, your application will be
changed...

CREATE TRIGGER tr_DeleteAdmissionRelatedInfo
ON tblAdmissions
FOR DELETE
AS

DELETE FROM tblASIFollowUp
WHERE EXISTS
(SELECT *
FROM deleted
WHERE deleted.AdmissionID = tblASIFollowUp.AdmissionID)

(etc)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||A few things to start with. Read ISO-11179, so you will stop putting
those silly prefixes on data element names. Besides violating
standards, it makes a data dictionary almost impossible to use. The
reason I call it silly is that SQL only has one data structure, so the
prefix is redundant, improper and useless all at once.

You name a thing for that it is; you do not name a thing for how it is
modeled, where it is stored, its datatype, etc. Think logical AND NOT
physical.

Do you really have tables with only one row in them? That is what a
singular name says; tables out to be collective or plural. A table is
a set, not am object instance.

Do not depend on the use of the "little snail" to identify your
parameter to the guy maintaining or porting your code. What does it
mean in the data model?

Use "SET <var> = <exp>;" instead of "SELECT <var> = .." so that you do
not create confusion and the code will port. SQL Server has a lot of
options for standard code now, so use them.

Now the real question. Why are you still thinking of procedural code
in a declarative language, like SQL? You can use DRI (declarative
referential integrity) actions to do this. Try this skeleton:

CREATE SCHEMA Foobar ..
...
CREATE TABLE Admissions -- the source of the data element
(admission_id INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE ASI_Followups
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE ProgramDischarges
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE RoomAssignments
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE Toads -- weird name!
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE UnitedWaySurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

CREATE TABLE WFGMSurvey
( ..
admission_id INTEGER NOT NULL
REFERENCES Admissions (admission_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

Besides being easier to code, this gives the optimizer information
about the relationships among the tables, so ALL your queries improve.
It is also faster than a TRIGGER. For example, in Sybase SQL Anywhere
there would be a single occurrence of each admission_id value and
pointer chains to all the table referencing it. Updates and deletes
are almost immediate even on huge tables.

You are still un-learning procedural code -- your "tbl-" prefixes were
a good sign that your real problem is foundations. After cleaning up
SQL code for 15-20 years, I have a good set of diagnostics :)
Go to BOL and look teh DRI you need.

Multiple datasources

Hi,
I've written a report that uses a matrix to display some data that all
comes from a funky select statement. The report is month based, so
there is a where clause specifying the month in the select
The user now wants to add a Year to Date column. Because of the data
structure of the data i don't believe it is possible to pull the YTD
figure in the same statment.
Is it possible to have one of the columns in a matrix (or any other
control) that is based on a different dataset which takes a parameters
from the matrix row?
I'm guessing theres not, but I just want to make sure, theres alot of
smart people out there :)
Thanks
AndrewWhat you want to do is to investigate subreports. You can embed a subreport
into a column (I have done this). If the subreport returns multiple rows
then that column would go down multiple rows. In your case you will only be
returning a single row. You will want the subreport to be very simple
(obviously) since you are embeding it. Note that the subreport is called for
every row. What I do in this case is hide the report in list view so users
don't see it when selecting a report to run.
To develop a subreport you develop it like a normal report with parameters.
Make sure it works. Drag and drop the report into the column you want (add
an extra column/field that is blank and drop it into it). Then do a right
mouse click on the embeded subreport and select parameters to map the
subreport parameters to the appropriate field.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<ajharvey@.gmail.com> wrote in message
news:1114049920.896557.185450@.o13g2000cwo.googlegroups.com...
> Hi,
> I've written a report that uses a matrix to display some data that all
> comes from a funky select statement. The report is month based, so
> there is a where clause specifying the month in the select
> The user now wants to add a Year to Date column. Because of the data
> structure of the data i don't believe it is possible to pull the YTD
> figure in the same statment.
> Is it possible to have one of the columns in a matrix (or any other
> control) that is based on a different dataset which takes a parameters
> from the matrix row?
> I'm guessing theres not, but I just want to make sure, theres alot of
> smart people out there :)
> Thanks
> Andrew
>|||Thanks,
I'll give that a try

Multiple Columns Map to Different Rows in Join Table

Can a SQL statement be written that returns all of the information in
the Project table and the corresponding FullName for both the
CreatedBy and LastModifiedBy fields? Table structures are below.
A typical JOIN clause seems insufficient because every row in the
Project table maps to 2 rows in the User table. When doing reporting,
I've always avoided this problem by using subreports to do individual
lookups. However, I'm curious to see if there is a more direct
approach using SQL only.
Project
- ProjectNo
- ProjectName
- CreatedByUserNo
- LastModifiedByUserNo
User
- UserNo
- FullName
Your input is greatly appreciated.
Best Regards,
DavidSELECT P.projectno, P.projectname,
C.fullname AS createdbyuser,
M.fullname AS lastmodifiedbyuser
FROM Project AS P
JOIN User AS C
ON P.createdbyuserno = C.userno
LEFT JOIN User AS M
ON P.lastmodifiedbyuserno = M.userno
(untested)
--
David Portas
--
Please reply only to the newsgroup
--