SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need to join query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shriroshanraj
Starting Member

30 Posts

Posted - 06/26/2013 :  03:30:11  Show Profile  Reply with Quote
hi,
I need to join these two queries into one.

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



select assignmentname,makeavailable,pointspossible,duedatetime from assignment
where assignmentid=(select assignmentid from assignmentsubmission
where coursesectionrosterid=(select coursesectionrosterid from coursesectionroster
where coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' and courserole=2))

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  03:35:22  Show Profile  Reply with Quote

select p.firstname,p.lastname,a.assignmentname,
a.makeavailable,a.pointspossible,a.duedatetime
from person2 p
inner join coursesectionroster cs
on cs.personid = p.personid 
inner join assignmentsubmission asn
on asn.coursesectionrosterid = cs.coursesectionrosterid
inner join assignment a
on a.assignmentid = as.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
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 06/26/2013 :  03:55:52  Show Profile  Reply with Quote
visak,

If i execute 1st query, i get 1 row and i execute 2nd query, i get only 1 row. i need to merge these 2 rows into 1 row.

After i execute your query i get 170 rows.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  04:02:36  Show Profile  Reply with Quote
quote:
Originally posted by shriroshanraj

visak,

If i execute 1st query, i get 1 row and i execute 2nd query, i get only 1 row. i need to merge these 2 rows into 1 row.

After i execute your query i get 170 rows.



that means the relationship between the tables are one to many

see how many records are there in coursesectionroster with same personid and same assignmentid

also post some sample data and explain how you want data to come otherwise we cant understand how you want tables to be related

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 06/26/2013 :  04:31:23  Show Profile  Reply with Quote
I have listed the table name,column name and their values.

PERSON2

personid firstname lastname username emailid institutionid

C1FC13B6-62F9-4A29-BEEC-043424CFEDBA Kelly Ross kross2@oakland.edu kross2@oakland.edu 426DBDB8-6237-48C0-A666-3989FC6C6133
1F5EE3C0-DA7B-42D7-A923-05FE2A50048E Adam Zztop jojo@bobos raekwee@yahoo.com NULL
7C21A717-755A-4F8B-A9B1-08556B0A13A1 Laker Junior lakerjr lakerjr@hellomavin.com E80E1E45-3E43-4043-B6BF-E74F97771765
E29265F1-6223-415A-BE3C-08FF35AF5BE2 Walter Bush Wbush wbush@mail.com C44D68D5-DC1D-41D4-9067-0C9810F16C71

ASSIGNMENT

assignmentid coursesectionid assignmentname makeavailable pointspossible duedatetime

31A3F399-7000-430D-968A-00B15F180826 4376A53D-77FF-48DD-86B3-473F9EFF2EB9 dsgfg 0 0 2013-03-28 00:00:00.0000000
A8CF430D-406E-4974-A764-014C10C45BAC 721E6C9D-E4DE-4453-BF75-1FA0B32A94F7 Create Access Database 0 25 2013-03-24 00:00:00.0000000
28423D6E-6F74-4D5F-AF10-030C5CFFC36D E70FC2A8-9E39-4B98-97E1-61E0E583C263 Create report on vendor comparisons 0 25 2013-05-15 00:00:00.0000000
ACC35517-305E-45AC-8C7A-037B6A81C9F1 98410C70-ADD6-48A2-8CB0-C1EB0947ED52 sample 0 10 2013-06-04 00:00:00.0000000

ASSIGNMENTSUBMISSION

assignmentsubmissionid assignmentid coursesectionrosterid submission comments

76BC4298-C57B-4A9B-952C-09DC5D70C3B6 4BA1745F-67BA-4F47-B08D-2BDA208EC094 2B6D38AD-643C-42D5-BF78-29462324EE3F Great Job This is a cool assignment NULL NULL
06C99390-4CD3-4ECC-9FFB-13DEDF0BF566 DEBE7D61-5ABB-44B2-9CB8-D940C506EA0F 70873776-A8F1-4194-9D4F-4C0270E6ED79 I read the book gimmie points
ABB8A329-17FA-4538-9663-23880FBE5C3C BED42B39-04D0-47A8-90DC-ECC6221FB943 NULL here is my submission for the group here are my comments

COURSESECTIONROSTER

coursesectionrosterid coursesectionid personid courserole coursegrade

00000000-0000-0000-0000-000000000000 7930A789-A70A-43E6-90CD-528B7165E211 062A9741-E2E7-4F27-803E-DB1617E38153 1 NULL
CFAFC039-7495-48DB-B0C6-02BD9DCBC22F B08C5F4E-F9D0-438C-B498-BDA989CA0D3A D80B68AA-198F-416D-838B-2BB7DBB3808C 1 NULL
4D5C26CA-28B2-4BF9-8CDB-04519D85B897 00A36A6F-1577-4B0B-801F-CE662F8143CA 1FC40C16-DA56-4C95-A636-15DA35FE91B2 2 A






















Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  04:37:10  Show Profile  Reply with Quote
the values you posted are unrelated one so i'm unable to find out relationship between tables form this. Can you please post related data from them? Also post it within code tags so that alignment is proper. Current format makes it difficult to understand which column has which value.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 06/26/2013 :  05:15:51  Show Profile  Reply with Quote
I have rearranged the values for your reference

PERSON2

personid firstname lastname emailid Institutionid

<C1FC13B6-62F9-4A29-BEEC-043424CFEDBA> <Kelly> <Ross> <kross2@oakland.edu> <426DBDB8-6237-48C0-A666-3989FC6C6133>
<1F5EE3C0-DA7B-42D7-A923-05FE2A50048E> <Adam> <Zztop> <raekwee@yahoo.com> <null>


ASSIGNMENT

assignmentid coursesectionid assignmentname makeavailable pointspossible duedatetime
<31A3F399-7000-430D-968A-00B15F180826> <4376A53D-77FF-48DD-86B3-473F9EFF2EB9> <dsgfg> <0> <0> <2013-03-28>
<A8CF430D-406E-4974-A764-014C10C45BAC> <721E6C9D-E4DE-4453-BF75-1FA0B32A94F7> <Create Access Database> <12> <25> <2013-03-24>

ASSIGNMENTSUBMISSION

assignmentsubmissionid assignmentid coursesectionrosterid Submission comments
<76BC4298-C57B-4A9B-952C-09DC5D70C3B6> <31A3F399-7000-430D-968A-00B15F180826> <2B6D38AD-643C-42D5-BF78-29462324EE3F> <sdssf> <sfafsaf>
<06C99390-4CD3-4ECC-9FFB-13DEDF0BF566> <A8CF430D-406E-4974-A764-014C10C45BAC> <4BA1745F-67BA-4F47-B08D-2BDA208EC094> <Cncncn> <cxbxbx>



COURSESECTIONROSTER

coursesectionrosterid coursesectionid personid courserole coursegrade
<2B6D38AD-643C-42D5-BF78-29462324EE3F> <4376A53D-77FF-48DD-86B3-473F9EFF2EB9> <C1FC13B6-62F9-4A29-BEEC-043424CFEDBA> <2> <A>
<4BA1745F-67BA-4F47-B08D-2BDA208EC094> <721E6C9D-E4DE-4453-BF75-1FA0B32A94F7> <1F5EE3C0-DA7B-42D7-A923-05FE2A50048E> <1> <B>



Edited by - shriroshanraj on 06/26/2013 05:32:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  05:34:28  Show Profile  Reply with Quote
there's nothing shown here to indicate a one to many relationship. If that is case there's no chance you'll get 170 rows by the join i suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 06/26/2013 :  05:42:08  Show Profile  Reply with Quote
It displays all the assignments. I have listed only 2 for your reference. but all tables contains nearly 500 records.
.
Among 500 records i got only 1 record for the 2nd query which i posted. only 1 assignment detail i had as output.

when i execute your query i can able to see many assignments.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  05:54:12  Show Profile  Reply with Quote
that may be because you were doing select separately. when you do join it will get multiplied based on one to many relationship.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000