Solved

Make a query to Select data from Two Tables

Posted on 2008-06-24
1
451 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why do I get extra rows when I do inner join? 12 43
Need help in debugging a UDF results 7 72
Database maintenance 36 142
What is GIS method of Geometry data type? 6 36
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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