Solved

Make a query to Select data from Two Tables

Posted on 2008-06-24
1
450 Views
Last Modified: 2010-05-18
Board                     Reply
----------------         ---------------------
BoardID (PK)         ReplyID (PK)
Subject                  BoardID (FK)
                              RegisterDate
                              StatusTypeID

Let say I have these two tables. What I need is to collect all the data based on Board Table. The problem is that on Reply Table, there are many reply data with one boardID and I need to seperate the RegisterDate based on StatusTypeID. So if StatusTypeID='12' then it will be AssignDate, and if StatusTypeID='13' then it will be Contaced Date....etc.
Bottom is what the result will be like:

BoardID   Subject   AssignDate              Contacted Date            SignedDate
------------------------------------------------------------------------------------------
1              test          03/01/2008             03/03/2008                    03/05/2008
2              hello        03/01/2008              Null                                Null
3              hi             03/01/2008              03/02/2008                     03/12/2008


Below is what I done so far and the result came out to be wrong:

Select B.BoardID,Subject,RegisterDate, ass.RegisterDate as 'Assigned Date', con.ReigsterDate as 'Contaced Date', sig,RegisterDate as 'Signed Date'
From Board B
Left Join Reply R On R.BoardID=B.BoardID
Left Join Reply ass On StatusTypeID='12'
Left Join Reply con On con.StatusTypeID='13'
Left Join Reply sig On sig.StatusTypeID='14'

Help!
0
Comment
Question by:erin027
1 Comment
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 21860978
You were close, though.  Try this modification:

Select
      B.BoardID,
      Subject,
      RegisterDate,
      ass.RegisterDate as 'Assigned Date',
      con.ReigsterDate as 'Contacted Date',
      sig,RegisterDate as 'Signed Date'
From Board B
Left Join Reply ass
      On ass.StatusTypeID='12'
      AND ass.BoardID=B.BoardID
Left Join Reply con
      On con.StatusTypeID='13'
      AND con.BoardID=B.BoardID
Left Join Reply sig
      On sig.StatusTypeID='14'
      AND sig.BoardID=B.BoardID
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question