October 22, 2004 at 8:12 am
Dear all,
Please advise how I should write and complete the following store proc.
I have 3 tables as define below:
Table A: GroupID, GroupName
Table B: NoID, GroupID, ContactNo
Table C: ContactNo, Datetime, Detination…
One to many relation between Table A and Table B.
One to many relation between table B and table C.
I created a store proc to accept one parameter that define in table A. This parameter will use to select a range of contact no in table B. This range of contact no (is variable, maybe 3 up to 16 no) will use in the WHERE condition to select all related records on Table C. The store proc will look something like below:
Create proc SelectInfo @Groupname int @Report OUTPUT
As
Begin
Select Contactno from Table B where groupID = @GroupName
…
…
Select ContactNo, DateTime,Destination from Table C WHERE Contactno = …
…
Please advise and help how should I write and complete this store proc. The WHERE condition is variable depend on the Range of contactno on selected from table B. Please advise how and any way I can use to loop thru the WHERE condition.
Thanks in Advance. Many Thanks.
October 22, 2004 at 8:26 am
I believe that you could do it with a three-way join...
SELECT a.col, b.col, c.col, .....
FROM tableA a
JOIN tableB b
ON a.GroupID = b.GroupID
AND a.groupID = @GroupName
JOIN tableC c
ON b.ContactNo = c.ContactNo
/Kenneth
October 22, 2004 at 8:52 am
Hi Kenneth,
Thanks for the reply. The problem i have is, there will be a multiple contact nos selected from the input parameter. All the contact nos will use in the WHERE clause to select records from TableC and return the result. The contact no selected from table B can be 3 and up to 16 nos.How this can be done on the join table? Is that i need a way to loop thru and compare WHERE condition in order to select all correct records from TableC?
Please correct me. I new to programming. Thanks.
October 22, 2004 at 10:08 am
Similar to Kenneth's
SELECT a.GroupID, a.GroupName, b.NoID, c.ContactNo, Datetime, Detination
FROM [TableA] a
INNER JOIN [TableB] b
ON b.GroupID = a.GroupID
INNER JOIN [TableC] c
ON c.ContactNo = b.ContactNo
WHERE a.GroupID = @GroupName
this will return all data for the GroupID that matches @GroupName !!
including the ContactNo's for the matching GroupID from TableC
You have not specified what your @Report OUTPUT parameter is for
Why do you need separate WHERE clause for tableC ?
Far away is close at hand in the images of elsewhere.
Anon.
October 26, 2004 at 6:28 pm
Dear Kenneth's and David's,
Many thanks for the advise. It's worked. thanks a lots for the help. Now i learn when i should use join statement. Again...Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply