Click here to Skip to main content
15,936,903 members
Please Sign up or sign in to vote.
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
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

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
 
Share this answer
 
Comments
Member 10794814 19-May-14 0:06am    
@syed shanu
Sir Actually there are 3 tables, and it retrieve student info from junior or degree table.
I give only UID to the application and click on search button then the application will retrieve value from junior or degree table. IF ITS A JUNIOR STUDENT IT RETRIEVE FROM FROM JUNIOR OR VICE VERSA.
This both table has Column name as A "UID".
And application will retrieve payment info from payment table but it has column name as a "STUD_UID".
Sir help me out how can i do this ?
syed shanu 19-May-14 0:09am    
that was i did in my join query .check the above query.I did was what you want .try to create abouve test table and insert as i did and in using join query select and see the result in my join query i have used case if junior table has value then i display as junior else as degree.first check with above solution and tell me

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900