how to retrieve data from 3 tables and display in respective textbox

Question

0.00/5 (No votes)

See more:

Hello
I want to retrieve value from 3 tables and display in textbox.
To be more specify i want to retrieve student info from junior and degree and payment value from payment table.

Detail:
1. There is one search box,i hav to retrieve value from this means I will give student id and the application checks if the student id will junior or degree, if its a junior student it will retrieve from junior table else from degree student.

2. And payment information from payment table.

Thanks in advanced.

Posted 18-May-14 3:27am

Member 10794814

Add a Solution

Comments

[no name] 18-May-14 9:36am

Okay and what have you tried? What errors do you get with your code? Does your query return anything at all? What did you see when you ran your code through the debugger? "I want to" is not a question or a description of any kind of a problem.

Member 10794814 18-May-14 9:50am

sir i tried for one table.and it retrieve value from one table but i m not tried for multiple tables

DamithSL 18-May-14 10:22am

update the question with your code, you will get answers soon.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Answer 1 · 2014-05-18T15:05:00

Hi,
Your query seems to be not correct "select * from DEGREE_STUDENT UNION select * from JUNIOR_STUDENT where UID UNION select * from PAYMENT where STUD_UID =@UID"

You have to use the Join query to get the result of student.
here i have make sample Table and Join query for your result check with this.

SQL

-- 1 Create 3 table 
CREATE TABLE [dbo].[Students](
	[STUD_UID] [int] NULL,
	[NAME] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[DEGREE_STUDENT](
	[DEG_ID] [int] NULL,
	[STUD_UID] [int] NULL,
	[NAME] [varchar](50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[JUNIOR_STUDENT](
	[JUN_ID] [int] NULL,
	[STUD_UID] [int] NULL,
	[NAME] [varchar](50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[PAYMENT](
[PAY_ID] [int] NULL,
	[STUD_UID] [int] NULL,
	[Amount] [varchar](50) NULL	
) ON [PRIMARY]

-- Insert sample data to 3 tables
INSERT INTO [Students]
           ([STUD_UID]
           ,[NAME])
     VALUES
           (1
           ,'Test1')
INSERT INTO [Students]
           ([STUD_UID]
           ,[NAME])
     VALUES
           (2
           ,'Test2')

INSERT INTO [Students]
           ([STUD_UID]
           ,[NAME])
     VALUES
           (3
           ,'Test3')

INSERT INTO [JUNIOR_STUDENT]
           ([JUN_ID]
           ,[STUD_UID]
           ,[NAME])
     VALUES
           (1 ,1 ,'Junior')

INSERT INTO [JUNIOR_STUDENT]
           ([JUN_ID]
           ,[STUD_UID]
           ,[NAME])
     VALUES
           (2 ,2 ,'Junior')

INSERT INTO [DEGREE_STUDENT]
           ([DEG_ID]
           ,[STUD_UID]
           ,[NAME])
     VALUES
           (1
           ,3
           ,'Degree')

INSERT INTO [PAYMENT]
           ([PAY_ID]
           ,[STUD_UID]
           ,[Amount])
     VALUES
           (1  ,1 ,5000)

INSERT INTO [PAYMENT]
           ([PAY_ID]
           ,[STUD_UID]
           ,[Amount])
     VALUES
           (2  ,2 ,7000)

INSERT INTO [PAYMENT]
           ([PAY_ID]
           ,[STUD_UID]
           ,[Amount])
     VALUES
           (3  ,3 ,12000)

-- Select query for all 4 tables 
select * from Students
Select * from junior_student
select * from DEgree_student
select * from payment


-- Join Query with your result :
     Select S.STUD_UID
	,S.NAME
	,Case WHEN J.Name IS NOT NULL THEN J.Name ELSE D.NAME END as STUDENT_TYPE
	,P.Amount
        FROM
	         Students as S
	        LEFT OUTER JOIN junior_student as J ON S.STUD_UID=J.STUD_UID
                 LEFT OUTER JOIN DEgree_student as D ON S.STUD_UID=D.STUD_UID
	        LEFT OUTER JOIN payment as P ON S.STUD_UID=P.STUD_UID
       WHERE
         S.STUD_UID=1