Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > .NET 2.0 and Visual Studio. 2005 > Visual Studio 2005
|
Visual Studio 2005 For discussing Visual Studio 2005. Please post code questions about a specific language (C#, VB, ASP.NET, etc) in the correct language forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Studio 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 2nd, 2007, 12:18 PM
Registered User
 
Join Date: May 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Retrieve different data from same table /field

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.


 
Old August 2nd, 2007, 09:52 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old August 5th, 2007, 02:43 AM
Authorized User
 
Join Date: May 2007
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

look below:
 
Old August 5th, 2007, 02:54 AM
Authorized User
 
Join Date: May 2007
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 6th, 2007, 09:45 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old August 6th, 2007, 09:56 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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:
Code:
Table: Category
CatID CatName
----- -----------
1     Tools
2     Power Tools
Then we have a join table with a parent/child relationship:
Code:
Table: CategoryJoin
ParentID ChildID
-------- -------
1        2
This record creates the relationship between the categories.

Now we actually want to see the names of the categories ("Tools" & "Power Tools"):
Code:
SELECT cat1.CatName, cat2.CatName
FROM CatJoin cj
   JOIN Category cat1 ON cj.ParentID=cat1.CatID
   JOIN Category cat2 ON cj.ChildID=cat2.CatID
WHERE cj.ParentID=1
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
 
Old August 6th, 2007, 10:27 AM
Friend of Wrox
 
Join Date: Feb 2006
Posts: 133
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to gaurav_jain2403
Default

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.
 
Old August 6th, 2007, 10:40 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieve Autonumber field and update it carrie09 Access VBA 3 November 2nd, 2007 07:32 AM
Retrieve Field Name & Value from XML in asp andieh XML 6 October 3rd, 2006 12:52 PM
Retrieve data from word table Paul_Tic Access VBA 2 May 26th, 2006 11:31 AM
Retrieve recordset field value tryntolearn Classic ASP Databases 1 August 8th, 2005 10:22 AM
How to retrieve data from an array to a table? penta Access 4 September 22nd, 2004 03:05 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.