Post reply

Selective Query

  • November 15, 2007 at 4:59 am

    #184218

    Hi Guys,

    I have this query:

    SELECT tbStage1.Stage1Id, tbStage1.Contact1Id,tbStage1.CreateDate,tbStage1.Location,tbStage1.ContactTypeId,tbActionTypes.ActionTypeDescription As "Contact Type"

    FROM tbStage1

    INNER JOIN tbActionTypes ON tbStage1.ContactTypeId = tbActionTypes.AutoId

    WHERE tbStage1.Contact1Id = 3

    ORDER BY tbStage1.Stage1Id DESC

    But I want to select based on another tables value. I have another table called tbStage3 with 9 fields but the 2 fields I need to use in the query are Stage1Id & ClosedBy. tbStage3 can have multiple records related to tbStage1 by tbStage1.Stage1Id = tbStage3.Stage1Id . I want to select records based on the query above but only if there are records in tbStage3 where ClosedBy = 0

    I hope this makes sense.

    Thanks for any help you can give.

    Best Regards,

    Steve.

  • November 15, 2007 at 6:25 am

    #751475

    Steve, I think this will do what you want.

    SELECT

    s3.Stage1Id,

    s3.ClosedBy,

    s1.Contact1Id,

    s1.CreateDate,

    s1.Location,

    s1.ContactTypeId,

    a.ActionTypeDescription AS [Contact Type]

    FROM tbStage1 s1 INNER JOIN

    tbActionsTypes a

    ON s1.Contact1ID = a.AutoID INNER JOIN

    tbStage3 s3

    ON s1.Stage1Id = s3.Stage1Id

    WHERE s3.ClosedBy = 0

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • November 15, 2007 at 8:00 am

    #751521

    Hi Veteran,

    Thanks for the reply, Had to adjust it slightly and it is working (almost). I have changed it to this:

    SELECT

    s3.Stage1Id,

    s3.ClosedBy,

    s1.Contact1Id,

    s1.CreateDate,

    s1.Location,

    s1.ContactTypeId,

    a.ActionTypeDescription AS "Contact Type"

    FROM tbStage1 s1

    INNER JOIN tbActionTypes a ON s1.ContactTypeId = a.AutoID

    INNER JOIN tbStage3 s3 ON s1.Stage1Id = s3.Stage1Id

    WHERE s1.Contact1Id = 3 AND s3.ClosedBy = 0

    But, If there are 3 records in tbStage3 relating to the 1 record in tbStage1 I am getting 3 rows but I only want the one row. I.E.

    tbStage1.Stage1Id = 41 and in tbStage3 there are 3 records that have:

    tbStage3.Stage1Id = 41 issue = "waste Management"

    tbStage3.Stage1Id = 41 issue = "Oil Spills"

    tbStage3.Stage1Id = 41 issue = "Air Pollution"

    I just want to see id = 41, Location = s1.Location, Date = s1.CreateDate

    How do I set it to display just 1 record do I use Distinct or something like that.

    Thanks for your help, I appreciate it.

    Best Regards,

    Steve.

  • November 15, 2007 at 9:33 am

    #751573

    Ok, try changing s3.Stage1Id to s1.Stage1Id in the select statement

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • November 15, 2007 at 11:19 am

    #751603

    Greg Snidow (11/15/2007)

    Ok, try changing s3.Stage1Id to s1.Stage1Id in the select statement

    Sorry, I meant to say SELECT DISTINCT s1.Stage1ID...

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • November 16, 2007 at 4:08 pm

    #752007

    Thanks Guys,

    All working now,

    Best Regards,

    Steve.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply