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
 need to join query

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.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
[/code]

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

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.
Go to Top of Page

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 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 - 2013-06-26 : 04:31:23
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

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shriroshanraj
Starting Member

30 Posts

Posted - 2013-06-26 : 05:15:51
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>


Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -