Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Make a query to Select data from Two Tables

Posted on 2008-06-24
1
Medium Priority
?
459 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:
Brendt Hess earned 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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 this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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