Solved

Make a query to Select data from Two Tables

Posted on 2008-06-24
1
443 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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now