Author |
Topic |
shriroshanraj
Starting Member
30 Posts |
Posted - 2013-06-26 : 03:30:11
|
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
52326 Posts |
Posted - 2013-06-26 : 03:35:22
|
[code]select p.firstname,p.lastname,a.assignmentname,a.makeavailable,a.pointspossible,a.duedatetimefrom person2 pinner join coursesectionroster cson cs.personid = p.personid inner join assignmentsubmission asnon asn.coursesectionrosterid = cs.coursesectionrosteridinner join assignment aon a.assignmentid = as.assignmentid where cs.coursesectionid='b78a6efe-ac77-4e49-806a-fc2fad71068b' and cs.courserole=2[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
shriroshanraj
Starting Member
30 Posts |
Posted - 2013-06-26 : 03:55:52
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-26 : 04:02:36
|
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 manysee how many records are there in coursesectionroster with same personid and same assignmentidalso 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
shriroshanraj
Starting Member
30 Posts |
Posted - 2013-06-26 : 04:31:23
|
I have listed the table name,column name and their values.PERSON2personid firstname lastname username emailid institutionidC1FC13B6-62F9-4A29-BEEC-043424CFEDBA Kelly Ross kross2@oakland.edu kross2@oakland.edu 426DBDB8-6237-48C0-A666-3989FC6C61331F5EE3C0-DA7B-42D7-A923-05FE2A50048E Adam Zztop jojo@bobos raekwee@yahoo.com NULL7C21A717-755A-4F8B-A9B1-08556B0A13A1 Laker Junior lakerjr lakerjr@hellomavin.com E80E1E45-3E43-4043-B6BF-E74F97771765E29265F1-6223-415A-BE3C-08FF35AF5BE2 Walter Bush Wbush wbush@mail.com C44D68D5-DC1D-41D4-9067-0C9810F16C71ASSIGNMENTassignmentid coursesectionid assignmentname makeavailable pointspossible duedatetime31A3F399-7000-430D-968A-00B15F180826 4376A53D-77FF-48DD-86B3-473F9EFF2EB9 dsgfg 0 0 2013-03-28 00:00:00.0000000A8CF430D-406E-4974-A764-014C10C45BAC 721E6C9D-E4DE-4453-BF75-1FA0B32A94F7 Create Access Database 0 25 2013-03-24 00:00:00.000000028423D6E-6F74-4D5F-AF10-030C5CFFC36D E70FC2A8-9E39-4B98-97E1-61E0E583C263 Create report on vendor comparisons 0 25 2013-05-15 00:00:00.0000000ACC35517-305E-45AC-8C7A-037B6A81C9F1 98410C70-ADD6-48A2-8CB0-C1EB0947ED52 sample 0 10 2013-06-04 00:00:00.0000000ASSIGNMENTSUBMISSIONassignmentsubmissionid assignmentid coursesectionrosterid submission comments76BC4298-C57B-4A9B-952C-09DC5D70C3B6 4BA1745F-67BA-4F47-B08D-2BDA208EC094 2B6D38AD-643C-42D5-BF78-29462324EE3F Great Job This is a cool assignment NULL NULL06C99390-4CD3-4ECC-9FFB-13DEDF0BF566 DEBE7D61-5ABB-44B2-9CB8-D940C506EA0F 70873776-A8F1-4194-9D4F-4C0270E6ED79 I read the book gimmie pointsABB8A329-17FA-4538-9663-23880FBE5C3C BED42B39-04D0-47A8-90DC-ECC6221FB943 NULL here is my submission for the group here are my commentsCOURSESECTIONROSTERcoursesectionrosterid coursesectionid personid courserole coursegrade 00000000-0000-0000-0000-000000000000 7930A789-A70A-43E6-90CD-528B7165E211 062A9741-E2E7-4F27-803E-DB1617E38153 1 NULLCFAFC039-7495-48DB-B0C6-02BD9DCBC22F B08C5F4E-F9D0-438C-B498-BDA989CA0D3A D80B68AA-198F-416D-838B-2BB7DBB3808C 1 NULL4D5C26CA-28B2-4BF9-8CDB-04519D85B897 00A36A6F-1577-4B0B-801F-CE662F8143CA 1FC40C16-DA56-4C95-A636-15DA35FE91B2 2 A |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-26 : 04:37:10
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
shriroshanraj
Starting Member
30 Posts |
Posted - 2013-06-26 : 05:15:51
|
I have rearranged the values for your referencePERSON2personid 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>ASSIGNMENTassignmentid 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>ASSIGNMENTSUBMISSIONassignmentsubmissionid 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>COURSESECTIONROSTERcoursesectionrosterid 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> |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-26 : 05:34:28
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
shriroshanraj
Starting Member
30 Posts |
Posted - 2013-06-26 : 05:42:08
|
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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-26 : 05:54:12
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|