Hi,

Retrieve different data from The same table and same column(field) for a form

This is reports on website. The report has 3 fields from 3 tables( Acturally many link tables)
For example:
__________________________________________________ ____________________________________________
Type ( from Type table) User Name(from User table), Firm Name(From Firm table)
A John A1 Firm
A John A2 Firm
A Joe A3 firm
A Joe A4 firm

B Reb B1 firm
B Reb B2 firm
B Mary B1 firm
__________________________________________________ _______________________________________
If type is “A”, will retrieve user name John, joe … and Frim Name A1, A2… Each user name has more than one firms
If type is “B”, will retrieve user name Reb, Mary … and Frim B1, B2…
Each user name has more than one firms
I use the code below:

SELECT allt(user.uname)+ allt(user.name2) as name, allt(firm.fname) as firm FROM user inner join type on user.user_id = type.user_id
inner join firm on type.type_id = firm.pty_id
where type.type="A and Id=@id

SELECT allt(user.uname)+ allt(user.name2) as name, allt(firm.fname) as firm FROM user inner join type on user.user_id = type.user_id
inner join firm on type.type_id = firm.pty_id
where type.type="B” and Id= @id

How to code together for a form?
Thanks.

I don't see what the difference is between your queries except for the different type.type filter. Why can't you put them together into a single query?

SELECT allt(user.uname)+ allt(user.name2) AS name, allt(firm.fname) AS firm
FROM user
    INNER JOIN type ON user.user_id = type.user_id
    INNER JOIN firm ON type.type_id = firm.pty_id
WHERE (type.type='A' OR type.type='B') AND Id=@id

The multiple records in the firm table will result in multiple records returned for each user.

Maybe I don't understand your question: "How to code together for a form?" What do you want together?

-Peter

look below:

Peter,

Thansk for your reply.
Maybe my description is not clear. The report should be like the below:

__________________________________________________ ____________________________________
Action ID: 0001-ca-2001

No#: 0001
Location: CA
Year: 20001

----------------------------------------------------------------------------------

Type ( from Type table) Name(from User table), Firm Name(From Firm table)


Plaintiff (P) John (for people name) (for) P1 Firm, P2 Firm,P3 Firm,


                    (to )

Defendants (D) ABC.Inc ( for company name) (for) D1 firm ,D2 Firm

__________________________________________________ __________________________________________
Here
(1) John name and ABC.Inc are the same Table and the Same Column(field) and the different Rows. P1, P1.. and D1, D2.... are the same Table and same Column(field) and the different Rows

(2) the John with P1, P2, and ABC.Inc with D1, D2 are different Type. If Type is "P" then show Name JOHN and John serve for P1 firm, p1 firm...
If Type is "D" then show ABC.Inc and for D1 firm, D1 firm...


Database can not be change.

So how to make? Please let me know ASAP. Thank you very much.
Pj

I'm still having trouble understanding what you want. Can you do this for us:

Post the simplest form of the data you need in RAW data form (post everything needed, but nothing more). Also, please use the forum's formatting capability. You can use the code format to make the text fixed width to line it up so it's easier to read. (Use the "Preview Reply" button to see what the post will look like to us.)

Again, please post the ORIGINAL data so we can see what you are starting with. If you like, also post the desired output. From those, someone should be able to determine how to get from point A to point B.

-Peter

After I looked again, I think I have an idea of what you want to do. You can join to the same table twice if you need to get two different sets of data. If you have join table that references two different records from the same table you need to use the same source table with two different join filters. For example, let's take a catalog category table:
Then we have a join table with a parent/child relationship:
This record creates the relationship between the categories.

Now we actually want to see the names of the categories ("Tools" & "Power Tools"):
We are using the Category table twice, but with completely different join criteria, so they are treated as different table lookups.

Hope this helps.

-Peter

Peter,
The requirement that I have observed is that:
Suppose I have two tables,
Table1
Id Name Phone
1 A 9999999999
2 B 9843242343

Table2
IDFromTable1 Address
1 Address1
1 Address2
1 Address3
2 Address1
2 Address5

The required output is:
ID Name Address
1 A Address1,Address2,Address3
2 B Address1,Address5

The solution that I have for this is Use cursor for retrieving each row, get the required format for Address, insert the row in temporary table and select the data from temporary table.
But this will take time to execute. Do you have any query in mind that will produce the required output without using the solution that I think of.

In all my years doing SQL (although I am certainly not a T-SQL guru) I have never seen a straightforward way to perform this kind of value concatenation.

However a quick google: http://www.google.com/search?q=t-sql...+concatenation

produced the following article that seems pretty thorough:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

If you data is coming from SQL2005 then I would recommend going with the (Common Table Expression) approach. I learned recently that CTEs can perform recursively. This article provides an example of that approach.

-Peter