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
 New to SQL Server Programming
 query problem urgent pleaseeeeeeee

Author  Topic 

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-29 : 10:21:48
hi to all, i m beginner, please help me.

I have problem to fetch the record the data from the different type of table,
I have mention the structure and data of that table.
create table nk_user_main_info
(profile_id varchar(50),
profile_first_name varchar(50),
profile_last_name varchar(50),
Password varchar(50),
education_detail_status bit
)

create table nk_user_education_info_
(
profile_id varchar(50),
graduation_status bit,
post_graduation_status bit,
doctrate_status bit
)

create table nk_user_graduation_info_
(profile_id varchar(50),
course_name varchar(50),
specialization varchar(50),
from_institute varchar(50),
Passing_year varchar(50),
course_type varchar(50))

create table nk_user_post_graduation_info_
(
sys_user_id varchar(50),
profile_id varchar(50),
course_name varchar(50),
specialization varchar(50),
from_institute varchar(50),
Passing_year varchar(50),
course_type varchar(50)
)
Now insert the values in these tables:-
nk_user_main_info_
insert into nk_user_main_info_(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('rahulkumar', 'Rahul', 'Kumar', 'aaaaaa' ,'1')
insert into nk_user_main_info_(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('manish', 'Manish', 'Sharma', 'aaaaaa' ,'1')
insert into nk_user_main_info_(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('manisha', 'manisha', 'Khanna', 'aaaaaa' ,'1')
insert into nk_user_main_info_(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('neeraj', 'Neeraj', 'Sharma', 'aaaaaa' ,'1')
insert into nk_user_main_info_(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('ashutosh', 'Ashutosh', 'Bajpai', 'aaaaaa' ,'1')
insert into nk_user_main_info_(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('anudeep', 'Anudeep', 'Kumar', 'aaaaaa' ,'0')
insert into nk_user_main_info_(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('sanjeet', 'sanjeet', 'Bisht', 'aaaaaa' ,'1')

nk_user_education_info_
insert into nk_user_education_info_(profile_id,graduation_status,graduation_status,post_graduation_status,doctrate_status )
values('rahulkumar' ,'1', '1' ,'0' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,graduation_status,post_graduation_status,doctrate_status )
values('manish' ,'1', '1' ,'0' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,graduation_status,post_graduation_status,doctrate_status )
values('manisha' ,'1', '0' ,'0' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,graduation_status,post_graduation_status,doctrate_status )
values('ashutosh' ,'1', '1' ,'0' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,graduation_status,post_graduation_status,doctrate_status )
values('anudeep' ,'0', '0' ,'0' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,graduation_status,post_graduation_status,doctrate_status )
values('sanjeet' ,'1', '1' ,'0' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,graduation_status,post_graduation_status,doctrate_status )
values('neeraj' ,'1', '1' ,'1' ,'1')


nk_user_graduation_info_

insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('rahulkumar','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('manish','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('manisha','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('ashutosh','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('sanjeet','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('neeraj','GC104','SPL122','INST129','2002','CT101')

nk_user_post_graduation_info_


insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type )
values('rahulkumar','GC104','SPL122','INST129','2002','CT101')

insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type )
values('manish','GC104','SPL122','INST129','2002','CT101')

insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type )
values('ashutosh','GC104','SPL122','INST129','2002','CT101')

insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type )
values('neeraj','GC104','SPL122','INST129','2002','CT101')

insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type )
values('sanjeet','GC104','SPL122','INST129','2002','CT101')


? About table detial
nk_user_main_info -- this table store the user id and their detail like he has fill the education detail that is bit means whether he have mention their education detail or not

nk_user_education_info_ -- this table store the user id and their education complete detail like where he is graduate post graduate or doctrate

nk_user_graduation_info_ -- this table store the user id and detail like if he is graduate then course name,year ,institute,year,board etc.

nk_user_post_graduation_info_ -- this table store the user id and detail like if he is graduate then course name,year ,institute,year,board etc.


Now I want to make single query that will check whether the candidate are educated or not from table
“nk_user_main_info” and if he is educated then check whether he is graduate,post graduate,or doctrate from table “nk_user_education_info_”.
And if he is graduate then check from table “nk_user_graduation_info_” and if he is post graduate then check from table “nk_user_post_graduation_info_”.

If any body is not graduate then show :-
course_name = ‘No graduate’
,specialization =N/a
,from_institute = N/a,
Passing_year = N/a
,course_type= N/a

And same case in post graduate
And same case in doctrate




pankaj

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-07-29 : 10:39:12
Pankaj, Thanks for the code - but did you try to run it? It has errors.

Have you attempted any solutions to this question on your own yet? If so please post what you've got so far.

Finally, just out of curiosity, why is this so urgent? Doesn't seem to be any existing production code that is causing your business to stop - seems more like a homework assignment.

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-07-29 : 10:57:40
see if this is what you are looking for.
If not you may have enough info to solve it yourself now:

select m.profile_id
       ,course_name = 
              case 
              when e.post_graduation_status = 1 then p.course_name
              when e.graduation_status = 1 then g.course_name
              else 'no graduate'
              end

       ,specialization = 
              case 
              when e.post_graduation_status = 1 then p.specialization
              when e.graduation_status = 1 then g.specialization
              else 'n/a'
              end

       ,from_institute = 
              case 
              when e.post_graduation_status = 1 then p.from_institute
              when e.graduation_status = 1 then g.from_institute
              else 'n/a'
              end

       --etc...

from   nk_user_main_info_ m
inner join   nk_user_education_info_ e on e.profile_id = m.profile_id
left outer join nk_user_graduation_info_ g on g.profile_id = e.profile_id
left outer join nk_user_post_graduation_info_ p on p.profile_id = e.profile_id



EDIT:
Sorry Em, I tried to resist...the...URGENCY...but failed...

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-29 : 11:01:44
[code]SELECT m.profile_id,
CASE WHEN education_detail_status='1' THEN 'Educated' ELSE 'NotEducated' END,
CASE WHEN t.profile_id IS NULL THEN 'No Graduate'
ELSE t.edustatus END AS Course_Name,
COALESCE(specialization,'N/a') ,
COALESCE(from_institute,'N/a') ,
COALESCE(Passing_year,'N/a') ,
COALESCE(course_type,'N/a')
FROM nk_user_main_info_ m
LEFT JOIN nk_user_education_info_ ei
ON ei.profile_id=m.profile_id
LEFT JOIN (
SELECT profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type,'graduation' AS edustatus
FROM nk_user_graduation_info_
UNION ALL
SELECT profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type,'post graduation'
FROM nk_user_post_graduation_info_
UNION ALL
SELECT profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type,'doctrate'
FROM nk_user_doctrate_info_
)t
ON t.profile_id=m.profile_id
AND t.edustatus=CASE WHEN ei.doctrate_status='1' THEN 'doctrate'
WHEN ei.post_graduation_status='1' THEN 'post graduation'
WHEN ei.graduation_status='1' THEN 'graduation'
END[/code]
Go to Top of Page

VGuyz
Posting Yak Master

121 Posts
Posted - 2008-07-29 : 11:05:31
check this to..,

SELECT numi.profile_id,
nugi.course_name,
nugi.specialization,
nugi.from_institute,
nugi.Passing_year,
nugi.course_type
FROM nk_user_main_info_ numi left join nk_user_graduation_info_ nugi
on numi.profile_id =nugi.profile_id
where numi.education_detail_status=1
union
SELECT
numi.profile_id as profile_id,
'No Graduate' as course_name,
'N/a' as specialization,
'N/a' as from_institute,
'N/a' as Passing_year,
'N/a' as course_type
FROM nk_user_main_info_ numi left join nk_user_graduation_info_ nugi
on numi.profile_id =nugi.profile_id
where numi.education_detail_status=0
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-07-29 : 11:15:52
quote:
Originally posted by VGuyz

check this to..,

SELECT numi.profile_id,
nugi.course_name,
nugi.specialization,
nugi.from_institute,
nugi.Passing_year,
nugi.course_type
FROM nk_user_main_info_ numi left join nk_user_graduation_info_ nugi
on numi.profile_id =nugi.profile_id
where numi.education_detail_status=1
union
SELECT
numi.profile_id as profile_id,
'No Graduate' as course_name,
'N/a' as specialization,
'N/a' as from_institute,
'N/a' as Passing_year,
'N/a' as course_type
FROM nk_user_main_info_ numi left join nk_user_graduation_info_ nugi
on numi.profile_id =nugi.profile_id
where numi.education_detail_status=0

what about other tables? nk_user_post_graduation_info...
Go to Top of Page

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-29 : 12:55:08
Hi,
Actually i m beginner to sql . i m doing job. at this task is assigned to me, i just make three query and combined them with union. but i think there should something better .

and sorry for the given code.

pankaj
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2008-07-29 : 12:56:26
so what is so urgenttttt??
just for future notice:
If you mark you post as urgent fewer people will look at it and even fewer will answer it.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-29 : 13:07:14

Please check it
create table nk_user_main_info
(profile_id varchar(50),
profile_first_name varchar(50),
profile_last_name varchar(50),
Password varchar(50),
education_detail_status bit
)

create table nk_user_education_info_
(
profile_id varchar(50),
graduation_status bit,
post_graduation_status bit,
doctrate_status bit
)

create table nk_user_graduation_info_
(profile_id varchar(50),
course_name varchar(50),
specialization varchar(50),
from_institute varchar(50),
Passing_year varchar(50),
course_type varchar(50))

create table nk_user_post_graduation_info_
(
sys_user_id varchar(50),
profile_id varchar(50),
course_name varchar(50),
specialization varchar(50),
from_institute varchar(50),
Passing_year varchar(50),
course_type varchar(50)
)

insert into nk_user_main_info(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('rahulkumar', 'Rahul', 'Kumar', 'aaaaaa' ,'1')
insert into nk_user_main_info(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('manish', 'Manish', 'Sharma', 'aaaaaa' ,'1')
insert into nk_user_main_info(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('manisha', 'manisha', 'Khanna', 'aaaaaa' ,'1')
insert into nk_user_main_info(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('neeraj', 'Neeraj', 'Sharma', 'aaaaaa' ,'1')
insert into nk_user_main_info(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('ashutosh', 'Ashutosh', 'Bajpai', 'aaaaaa' ,'1')
insert into nk_user_main_info(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('anudeep', 'Anudeep', 'Kumar', 'aaaaaa' ,'0')
insert into nk_user_main_info(profile_id ,profile_first_name ,profile_last_name ,Password ,education_detail_status )
values('sanjeet', 'sanjeet', 'Bisht', 'aaaaaa' ,'1')

nk_user_education_info_

insert into nk_user_education_info_(profile_id,graduation_status,post_graduation_status,doctrate_status )
values('rahulkumar' ,'1', '1' ,'0' )
insert into nk_user_education_info_(profile_id,graduation_status,post_graduation_status,doctrate_status )
values('manish' ,'1', '1' ,'0' )
insert into nk_user_education_info_(profile_id,graduation_status,post_graduation_status,doctrate_status )
values('manisha' ,'1', '0' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,post_graduation_status,doctrate_status )
values('ashutosh' ,'1', '1' ,'0')
insert into nk_user_education_info_(profile_id,graduation_status,post_graduation_status,doctrate_status )
values('anudeep' ,'0', '0' ,'0' )
insert into nk_user_education_info_(profile_id,graduation_status,post_graduation_status,doctrate_status )
values('sanjeet' ,'1', '1' ,'0' )
insert into nk_user_education_info_(profile_id,graduation_status,post_graduation_status,doctrate_status )
values('neeraj' ,'1', '1' ,'1' )


nk_user_graduation_info_

insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('rahulkumar','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('manish','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('manisha','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('ashutosh','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('sanjeet','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('neeraj','GC104','SPL122','INST129','2002','CT101')

nk_user_graduation_info_

insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('rahulkumar','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('manish','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('manisha','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('ashutosh','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('sanjeet','GC104','SPL122','INST129','2002','CT101')
insert into nk_user_post_graduation_info_(profile_id ,course_name ,specialization ,from_institute ,Passing_year ,course_type)
values('neeraj','GC104','SPL122','INST129','2002','CT101')

pankaj
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-07-29 : 13:12:22
quote:
Originally posted by pkuchaliya


Please check it
...
pankaj

Have you tried any of the suggested solutions?
If none suit your needs please post your desired output (as it would appear based on your sample data)

Be One with the Optimizer
TG
Go to Top of Page

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-29 : 13:15:22
ok
not again.

pankaj
Go to Top of Page

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-29 : 13:26:15
actually the out put is like:
if he is graduate ,post graduate,and doctrate then it should show

profile_id,name,graduate_course_name,institute,specialization,passing_year, postgraduate_course_name,institute,specialization,passing,year,doctrate_course _name,institute,specilization,passing_year.

if any person only graduate then

profile_id,name,graduate_course_name,institute,specialization,passing_year, N/A,N/A,N/A,N/A,N/A,
N/A,N/A,N/A,N/A

if any person graduate,post graduate then
profile_id,name,graduate_course_name,institute,specialization,passing_year, postgraduate_course_name,institute,specialization,passing,year
,N/A,N/A,N/A,N/A.



pankaj
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-07-29 : 13:26:30
quote:
Originally posted by pkuchaliya

Hi,
i just make three query and combined them with union. but i think there should something better .

There is something better. A better table design. A better model would allow for a simple single query. ie: combine the education tables into a single table with an ID to specify the level of education.
quote:
Originally posted by pkuchaliya


ok
not again.
pankaj

"Not again" what? You never posted what form the output should take.

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-07-29 : 13:31:27
How many rows should be returned for neera (who as all three levels of education)? 3 or 1? If it's 3 then you need the UNION ALLs since your model has them in 3 different tables. If it is just the highest level then my solution would work.

What is your definition of "something better"?

Be One with the Optimizer
TG
Go to Top of Page

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-29 : 13:32:37
Actually my senior said me to break the table . previously i add these field to nk_user_main_info.

pankaj
Go to Top of Page

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-29 : 13:55:26
for neeraj it should return 1 row.
and i m not sure that i have make right query. i have make three query and union them then i got result.
so i want to know whether it is right way. which i have used.
basically i make three query one for graduate,second for post graduate and graduate and third for graduate,post graduate,doctrate. and then combined

pankaj
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-07-29 : 13:56:45
whatever...
try this. It is very similar to my first post. Just continue filling in the CASE statements for the additional columns from the appropriate table:

select m.profile_id

       ,name = m.profile_first_name + ' ' + m.profile_last_name 

       ,graduate_course_name = 
             case 
              when e.graduation_status = 1 then g.course_name
              else 'n/a'
              end

       ,institute = 
             case 
              when e.graduation_status = 1 then g.from_institute
              else 'n/a'
              end

       ,specialization = 
              case 
              when e.graduation_status = 1 then g.specialization
              else 'n/a'
              end
/*
       ,passing_year
       ,postgraduate_course_name
       ,institute
       ,specialization
       ,passing
       ,year
*/
       ,doctrate_course_name = 
              case 
              when e.post_graduation_status = 1 then p.course_name
              else 'n/a'
              end
/*
       ,institute
       ,specilization
       ,passing_year
*/

from   #nk_user_main_info m
inner join   #nk_user_education_info_ e on e.profile_id = m.profile_id
left outer join #nk_user_graduation_info_ g on g.profile_id = e.profile_id
left outer join #nk_user_post_graduation_info_ p on p.profile_id = e.profile_id



EDIT:
I changed you code to use #Temp tables so you'll need to remove the "#" from the table names.

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2008-07-29 : 14:16:29
quote:
Originally posted by pkuchaliya

for neeraj it should return 1 row.
and i m not sure that i have make right query. i have make three query and union them then i got result.
so i want to know whether it is right way. which i have used.
basically i make three query one for graduate,second for post graduate and graduate and third for graduate,post graduate,doctrate. and then combined

pankaj

Since you want one row per person (with data from other tables across additional columns) the UNION ALL statements is not the way to go. I think you want the solution I posted above.

Be One with the Optimizer
TG
Go to Top of Page

pkuchaliya
Starting Member

49 Posts
Posted - 2008-07-30 : 09:31:50
thanks,

pankaj
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site