join 2 queries - SQL Server Forums

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

join 2 queries

Author

Topic

shriroshanraj
Starting Member

30 Posts

Posted - 2013-06-28 : 00:37:46

I have 2 select query i need to join these 2 query into 1 query

select assignmentname,makeavailable,pointspossible,duedatetime from assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'

select firstname,lastname from person2 where personid=(
select personid from coursesectionroster where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' and courserole=2)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-28 : 00:53:32

[code]
SELECT a.assignmentname, a.makeavailable, a.pointspossible, a.duedatetime, p.firstname, p.lastname
FROM assignment a
INNER JOIN coursesectionroster c on a.coursesectionid = c.coursesectionid
INNER JOIN person2 p on c.personid = p.personid
WHERE a.coursesectionid = 'b78a6efe-ac77-4e49-806a-fc2fad71068b'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 01:13:52

continuation of

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186413

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-06-28 : 01:30:19

I am getting error in this query. how to rectify it.

select asg.pointsawarded,asg.submissiondatetime,a.assignmentname,a.makeavailable,a.pointspossible,a.duedatetime
from assignmentsubmission asg where asg.assignmentid =(
select assignmentid from assignment a where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 01:36:55

quote:
Originally posted by shriroshanraj

I am getting error in this query. how to rectify it.

select asg.pointsawarded,asg.submissiondatetime,a.assignmentname,a.makeavailable,a.pointspossible,a.duedatetime
from assignmentsubmission asg where asg.assignmentid =(
select assignmentid from assignment a where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b')

This not what Tan suggested. what was the problem with suggestion?

your current query is fetching data from only assignmentsubmission table but you're using columns from assignment table also in select which is causing the error

the corrected query will be like


select asg.pointsawarded,asg.submissiondatetime,a.assignmentname,a.makeavailable,a.pointspossible,a.duedatetime 
from assignmentsubmission asg 
inner join assignment a
on asg.assignmentid = a.assignmentid 
where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-28 : 01:50:21

You really need to learn how to use JOIN
http://www.w3schools.com/sql/sql_join.asp

It is the way you link multiple tables together and to retrieve information from it

This may work in this case but will fail if the sub-query return more than 1 row


where personid=(
select personid from coursesectionroster where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' and courserole=2)

KH
[spoiler]Time is always against us[/spoiler]

shriroshanraj
Starting Member

30 Posts

Posted - 2013-06-28 : 01:55:32

tan suggested is correct i did wrong in the query which i posted.

according to the coursesectionid i need to get assignments. From that assignemntid i need to get the values from assignmentsubmission. this what i need now.

for example, according to the coursesectionid i get 5 assignment records. from that 5 records i need to get values from the assignmentsubmission.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 01:58:45

quote:
Originally posted by shriroshanraj

tan suggested is correct i did wrong in the query which i posted.

according to the coursesectionid i need to get assignments. From that assignemntid i need to get the values from assignmentsubmission. this what i need now.

for example, according to the coursesectionid i get 5 assignment records. from that 5 records i need to get values from the assignmentsubmission.

see your query had columns from assignment table too (a.assignmentname,a.makeavailable,a.pointspossible,a.duedatetime ) which was what causing the error

for a column to be used in select the table has to included in the same query via join or subquery. You had used assignment table just in subquery in where condition so none of its columns will be available outside to be used in select.
so if you want onlu assignmentsubmission detail remove the assignment table columns from the select. Otherwise use query as per my suggestion after including assignment table through a join.

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-06-28 : 02:21:04

I am executing this query
select assignmentid,assignmentname,makeavailable,pointspossible,duedatetime from assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'

I get 5 assignments. i need to check each assignmentid with asssignmentsubmission. if assignmentid is present in assignmentsubmission table display the corresponding value otherwise display null value for pointsawarded and submissiondatetime.

select assignmentid,submissiondatetime,pointsawarded from assignmentsubmission

for this i need a query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 02:33:46

quote:
Originally posted by shriroshanraj

I am executing this query
select assignmentid,assignmentname,makeavailable,pointspossible,duedatetime from assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'

I get 5 assignments. i need to check each assignmentid with asssignmentsubmission. if assignmentid is present in assignmentsubmission table display the corresponding value.

select assignmentid,submissiondatetime,pointsawarded from assignmentsubmission

for this i need a query.

Is this a echo bot?
Are you trying out given suggestions at all? Whats the point again repeating questions after being provided with suggestions?

As suggested by Tan learn and understand joins and try the given suggestions. We've already answered your question more than once.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 02:59:14

quote:
Originally posted by shriroshanraj

I am executing this query
select assignmentid,assignmentname,makeavailable,pointspossible,duedatetime from assignment where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'

I get 5 assignments. i need to check each assignmentid with asssignmentsubmission. if assignmentid is present in assignmentsubmission table display the corresponding value otherwise display null value for pointsawarded and submissiondatetime.

select assignmentid,submissiondatetime,pointsawarded from assignmentsubmission

for this i need a query.

This is very close to what we suggested
have a look at LEFT JOIN in posted link and try it out yourself

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

shriroshanraj
Starting Member

30 Posts

Posted - 2013-06-28 : 04:58:06

i got it. from these

select a.assignmentname,asg.pointsawarded,a.pointspossible,a.duedatetime,asg.submissiondatetime from assignment a
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where a.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'

select p.firstname,p.lastname
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid
where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'
and cs.courserole=2

i cant merge it together.i am getting error from the below query. what i did wrong.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "p.personid" could not be bound.

select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded,a.pointspossible,a.duedatetime,asg.submissiondatetime

from person2 p, assignment a

left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid

inner join coursesectionroster cs
on cs.personid = p.personid

where a.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'
and cs.courserole=2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 05:05:51

the table referred by a is missing still!


select p.firstname,p.lastname,a.assignmentname,asg.pointsawarded,a.pointspossible,a.duedatetime,asg.submissiondatetime
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid 
inner join assignment a
on a.coursesectionid = cs.coursesectionid
left join assignmentsubmission asg
on a.assignmentid=asg.assignmentid
where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b'
and cs.courserole=2

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources