How to get value from more than one table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

How to get value from more than one table

Author

Topic

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-17 : 09:15:23

Hai frieds,

This is my stored procedure

ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin
select CMBooks,Magazine from Book_Issue where Stu_Stf_Id=@id
if @ct = '1'
begin
if exists (select StuId from StudReg where StuId=@id )
begin
select name from StudReg where StuId=@id
end
end
else
begin
if exists (select StfId from staff_reg where StfId=@id )
begin
select Name from staff_reg where StfId=@id
end
end
end

In coding,

SqlDataReader reader = ss.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
MessageBox.Show(reader[0].ToString());
MessageBox.Show(reader[1].ToString());
txtname.Text = reader[2].ToString();

}

I used this coding, I want to retrieve 3 fields (2 from Book_issue table, 1 from Student or staff table) value.

But i received error message "Index out of bounds".

then i tried

txtname.text=reader[0].tostring();

it means display same value of Book_issue CMBook column value.

Kindly help for me to resolve it.

Thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 10:03:28

You are getting two record sets from the stored procedure. The first record has CMBooks and Magazine, and the second has the Name. To move from one recordset to the next use NextResult method of SqlDataReader. This page has an example: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Alternatively, and perhaps a better option, might be to rewrite your query like this:

ALTER PROCEDURE [dbo].[id_selection]
    @id VARCHAR(20) ,
    @ct VARCHAR
AS 
    IF EXISTS ( SELECT  StuId
                FROM    StudReg
                WHERE   StuId = @id ) 
               
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN StudReg s ON s.StuId = b.Stu_Stf_Id
        WHERE   Stu_Stf_Id = @id
    ELSE IF EXISTS ( SELECT  StfId
                            FROM    staff_reg
                            WHERE   StfId = @id ) 
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN staff_reg s ON s.StfId = b.Stu_Stf_Id
        WHERE   StfId = @id
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 10:09:54

you just need this


ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin
select CMBooks,Magazine,
case when @ct='1' then  StuId end as StuId,
case when @ct='1' then  StfId end as StfId 
from Book_Issue bi 
left join StudReg sr
on sr.StuId = bi.Stu_Stf_Id
left join staff_reg sfr
on sfr.StfId = bi.Stu_Stf_Id
where bi.Stu_Stf_Id=@id

now take this in datareader and use fields[0],fields[1] etc to get corresponding fields

ALso if you've planning to introduce two columns as per earlier fk post your query will change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-17 : 11:01:01

Thank you for your valuable code.
But it retrieve CMBook, Magazine, Stu_id,StfId.
I want to retrieve CMBook, Magazine, Name (Student Table or Staff Table.
Kindly tel wat i do..

quote:
Originally posted by visakh16

you just need this

ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin
select CMBooks,Magazine,
case when @ct='1' then StuId end as StuId,
case when @ct='1' then StfId end as StfId
from Book_Issue bi
left join StudReg sr
on sr.StuId = bi.Stu_Stf_Id
left join staff_reg sfr
on sfr.StfId = bi.Stu_Stf_Id
where bi.Stu_Stf_Id=@id

now take this in datareader and use fields[0],fields[1] etc to get corresponding fields

ALso if you've planning to introduce two columns as per earlier fk post your query will change

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-17 : 11:09:35

Thank you this code worked done as my wish.

quote:

Originally posted by James K

You are getting two record sets from the stored procedure. The first record has CMBooks and Magazine, and the second has the Name. To move from one recordset to the next use NextResult method of SqlDataReader. This page has an example: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Alternatively, and perhaps a better option, might be to rewrite your query like this:

ALTER PROCEDURE [dbo].[id_selection]
    @id VARCHAR(20) ,
    @ct VARCHAR
AS 
    IF EXISTS ( SELECT  StuId
                FROM    StudReg
                WHERE   StuId = @id ) 
               
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN StudReg s ON s.StuId = b.Stu_Stf_Id
        WHERE   Stu_Stf_Id = @id
    ELSE IF EXISTS ( SELECT  StfId
                            FROM    staff_reg
                            WHERE   StfId = @id ) 
        SELECT  CMBooks ,
                Magazine
        FROM    Book_Issue b
				INNER JOIN staff_reg s ON s.StfId = b.Stu_Stf_Id
        WHERE   StfId = @id
GO

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-18 : 01:28:31

Thank you for your reply. Your query only retrieve if existing records available in both table.

But i want to display Name in text box from Student table. its needed one

After that, if existing data in Book_issue table means, i wanna data or empty. Its optional one depending upon the condition

quote:
Originally posted by Arunavally

Thank you this code worked done as my wish.

quote:
Originally posted by James K

You are getting two record sets from the stored procedure. The first record has CMBooks and Magazine, and the second has the Name. To move from one recordset to the next use NextResult method of SqlDataReader. This page has an example: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Alternatively, and perhaps a better option, might be to rewrite your query like this:
ALTER PROCEDURE [dbo].[id_selection]
@id VARCHAR(20) ,
@ct VARCHAR
AS
IF EXISTS ( SELECT StuId
FROM StudReg
WHERE StuId = @id )

SELECT CMBooks ,
Magazine
FROM Book_Issue b
INNER JOIN StudReg s ON s.StuId = b.Stu_Stf_Id
WHERE Stu_Stf_Id = @id
ELSE IF EXISTS ( SELECT StfId
FROM staff_reg
WHERE StfId = @id )
SELECT CMBooks ,
Magazine
FROM Book_Issue b
INNER JOIN staff_reg s ON s.StfId = b.Stu_Stf_Id
WHERE StfId = @id
GO

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-18 : 01:34:35

Thank you visakh16.

My doubt is, When i select a student or staff id from combo box. it will automatically display Student or Staff name to text box.

This value is taken from StudReg Table.

At that same time, i select a student or staff id from combo box, want to check student or staff already got course material and magazine. Then i want to retrieve CMBook, Magazine field from Book_issue table.

I want to use 2 logic of queries in same stored procedure.

How can i resolve it? Kindly help me and tel what is that querey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 03:00:47

in that case it should be


ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin

select t.name,CMBooks,Magazine
from
(
select StuId as ID, name as studname from StudReg where StuId = @id
union 
select StfId, name from Staff_Reg where StfId = @id
)t
left join Book_Issue bi 
on bi.Stu_Stf_Id = t.ID

you'll get name and Book details in same resultset itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-18 : 04:42:57

Thank you. It will work as my wish.

quote:
Originally posted by visakh16

in that case it should be

ALTER procedure [dbo].[id_selection]
@id varchar(20),
@ct varchar
as
begin

select t.name,CMBooks,Magazine
from
(
select StuId as ID, name as studname from StudReg where StuId = @id
union
select StfId, name from Staff_Reg where StfId = @id
)t
left join Book_Issue bi
on bi.Stu_Stf_Id = t.ID

you'll get name and Book details in same resultset itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 08:07:33

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-19 : 06:51:57

Hai visakh murukes sir,

I have maintained batch table

This field have

Stu_Id(Primary Key) BatchId (Not Primary key)
STU0001 APR042013
STU0002 APR062013
STU0003 APR042013

In form_load i want to load batch id frm DB to Combo Box. But i want to only one set of Id allow in combo box. That means..

In 1st row BatchId is APR042013
In 3rd row BatchId is APR042013 (Same)

But i want to display one Id (APR042013)instead of these two. and also 2nd row Id.

Kindly help me sir.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 03:42:57

Do you mean just BatchID alone. then


SELECT DISTINCT BatchID FROM Table

would do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-21 : 00:54:24

yes. Thank you sir. It will work as my wish. Thank you

quote:
Originally posted by visakh16

Do you mean just BatchID alone. then

SELECT DISTINCT BatchID FROM Table

would do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 06:38:10

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-22 : 08:57:09

Hai visakh murukes sir,

I used reportviewer to display all test marks in particular student

Table: TestTable
StuId
BatchId
Testname
Mark
QualMark
Max Mark
Date

It displayed correct in reportviewer. But i want to display StuId, Batch Id, Course, BatchStartDate,BatchEndDate, Attendance from Batch Table.

It will display only once in report. But TestTable value displayed more records.

How can i do?
Kindly help me sir.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 09:09:43

do you mean this?


SELECT  StuId, Batch Id, Course, BatchStartDate,BatchEndDate, Attendance,
Testname,Mark,QualMark,[Max Mark],Date
FROM Batch b
JOIN TestTable t
ON t.StuId = b.StuId
AND t.BatchId = b.BatchId

the other requirement is presentation requirement
depending on how you want you may apply grouping on SSRS on Batch fields or you could set hide duplicates property true for all batch id fields.
If you can provide info on how exactly you want data to come I can suggest.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-22 : 09:27:26

ya.I wrote query this way. but in presentation view, i dont knw how i do?
Then StuId:RRR BatchId: WWWWW B.StartDate: ddd B.End date:ddd Atttn: 12

Date TestName Mark QulMark MaxMark
ddd xxxxxx 45 25 50
ddd yyyyy 32 25 50
ddd zzzzz 41 25 50

I want to display this format.
Kindly tel how i do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 10:08:55

apply grouping in SSRS including batchid fields and then in details add the testtable fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-22 : 10:33:55

Thank you. I will try

Arunavally
Yak Posting Veteran

58 Posts

Posted - 2013-10-22 : 11:08:27

This error is occurring .
"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."
I dnt knw Why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 14:32:33

hmm...This has nothing to do with earlier question.
Where did you get this? What were you trying to do?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources