SQL HELP

[ldadams]

I have a SQL question along the lines of the one posted earlier, is there a way to return data from two or more cells in a table to one value. Such as I have one record that has multiple records associated with it and I would like to return 1 line with:

Would like to return:
___________________________________________________

Main_record Associated_Records
"Main_data1" "assoc._data1, Associted_data2, Associated_data3,etc."
“Main_data2” “assoc._data1, Associted_data2. etc..”
____________________________________________________


Currently returns:
____________________________________________________


Main_record Associated_Records
"Main_data1" "assoc._data1"
“Main _data1” “assoc._data2”
“Main_data1” “assoc._data3”
“Main_data2” “accoc._data1”
“Main_data2” “assoc._data2”
“etc…” “etc…”

____________________________________________________



There are three tables that contain this data:

Table one - Main record Table,
Table two - Associated Records table
Table three – Relating main and associated data Tables.

Since the user will be performing searches I never know how many main records or associated records will be returned. I will note that I am also using MSSQL for this. Is there a way to do this in SQL or am I stuck doing in the code? Hope this makes sense. Thanks

 

[Catadmin]

Sounds like you could do a Left Outer Join on the three tables, but you need a way to link the tables. Is there a primary key in Table One that carries over to Table Two and then a PK in Table Two that carries over to Table Three?

If the last, you'll do something like

Select * from Table1 t1
left outer join Table2 t2 on t1.PrimaryKeyField = t2.PrimaryKeyField
left outer join Table3 t3 on t2.PrimaryKeyField = t3.PrimaryKeyField

BTW, the t1, t2 and t3 are aliases. Look up Joins in Books Online. Left outer gives you all of Table1, the records in Table 2 that have a corresponding record in T1 and the records in Table 3 that have a corresponding record in T2. There are several types of Joins, so you'll need to be sure of what data you want from which table before you write this up.

On the other hand, if all three tables contain exactly the same types of info in the same table structure / column order, then you can use a Union statement such as...

Select * from Table1
Union
Select * from Table2
Union
Select * from Table3

Union does not work if your fields don't map exactly. You can look it up in Books Online also.

Write up your query in Query Analyzer, test it, and then save it as a .SQL file. That way, the users can re-use the query time and again. Or you can save it as a Stored Procedure or User Defined Function for the users to run. Depends on what else you want to do with the query and its results.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[ldadams]

This is my current stored proc. The co_name and ru_name are the associated records and op_id is the id of the main record I spoke of in my first post..hope this helps.Thanks

<code>
CREATE PROCEDURE dbo.usp_OpinionSearch

@caseNumber VARCHAR(10)=NULL,
@judge INT=NULL,
@debtor_defendant VARCHAR(50)=NULL,
@startDate DATETIME=NULL,
@endDate DATETIME=NULL,
@codeNumber VARCHAR(10)=NULL,
@ruleNumber VARCHAR(10)=NULL,
@keyWord VARCHAR(512)=NULL


AS
SET NOCOUNT ON


SELECT
op_id,
op_bkcase,
op_datetime,
op_title,
op_description,
op_debtor_defendant,
c.co_name,
r.ru_name,
ju_lname

FROM judges,opinion o
LEFT OUTER JOIN opinioncodes oc ON oc.oc_opinionid=o.op_id
LEFT OUTER JOIN opinionrules [or] ON [or].or_opinionid=o.op_id
INNER JOIN codes c ON c.co_id=oc.oc_codeid
INNER JOIN rules r ON r.ru_id=[or].or_ruleid
WHERE
(@caseNumber IS NULL OR op_bkcase = @caseNumber)
AND (@judge IS NULL OR op_judgeid = @judge)
AND (@debtor_defendant IS NULL OR op_debtor_defendant = @debtor_defendant)
AND (@startDate IS NULL OR op_datetime >= @startDate)
AND (@endDate IS NULL OR op_datetime >= @endDate)
AND (@codeNumber IS NULL OR co_name = @codeNumber)
AND (@ruleNumber IS NULL OR ru_name = @ruleNumber)
AND (@keyWord IS NULL OR op_description LIKE '%' + @keyWord + '%' OR or_description LIKE '%' + @keyWord + '%' OR oc_description LIKE '%' + @keyWord + '%')



RETURN
GO
</code>