Avatar of kdeutsch
kdeutschFlag for United States of America asked on

Select statement with SQL

What I am trying to do is select a list of names based of of what was picked in dropdown boxes.  So first is the taskforce dropdown wich will fill the UIC drop down box.  On the UIc drop down box is when it should query a list of names based on what was picked on the taskforce drop down then what ws picked on the UIC drop down and then wether a bit was present in another table.    SO first I am picking the user name and SSN from tbls Sidpers and SADDOTNET and then trying to match this to  what was picked in the taskforce & UIC dropdowns based on the intUICID which is in the tblUIC.  Then the final kicker is the that those pciked must have a 1 in bitpresent in the tblassignedPersonnel.  So heres what i have but it keeps giving me incorrect syntax near WHERE and tblassignedpersonnel.

select      p.sidstrNAME_IND,
      u.strSSN
from      cms.dbo.tblsidpers as p INNER JOIN
            tblAssignedPersonnel as u on u.strSSN = p.sidstrSSN_SM
where      sidstrSSN_SM in (select strssn fro tblAssignedPersonnel where intUICID =
            (select intUICId from tblUIC where intTaskforceId = '1' and strUIC = '2' and bitpresent = '1')
SQL

Avatar of undefined
Last Comment
kdeutsch

8/22/2022 - Mon
ASKER
kdeutsch

ok, i found the from statement was not completed, but now I get a incorrect syntax near ')'
ASKER CERTIFIED SOLUTION
frankytee

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
frankytee

also
fro tblAssignedPersonnel

should be
from tblAssignedPersonnel
ASKER
kdeutsch

Ok, changed all that to following, but I get no data.  I checked the database to make sure there is revalent data in there and there is but its pulling nothing.  I onder if its because the bitpresent is in the tblAssingedPersonnel and not the tbluic.

select      p.sidstrNAME_IND,
      u.strSSN
from      cms.dbo.tblsidpers as p INNER JOIN
      tblAssignedPersonnel as u on u.strSSN = p.sidstrSSN_SM
where      sidstrSSN_SM in (select strssn from tblAssignedPersonnel  where intUICID in
(select intUICId from tblUIC where intTaskforceId = '23' and strUIC = 'PJPB2' and bitpresent = '1'))
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
frankytee

try
select      p.sidstrNAME_IND,
      u.strSSN
from      cms.dbo.tblsidpers as p INNER JOIN
      tblAssignedPersonnel as u on u.strSSN = p.sidstrSSN_SM
inner join  tblUIC as UC
on u.intUICID  = UC.intUICId
where UC.intTaskforceId = '23' and UC.strUIC = 'PJPB2'
and u.bitpresent = '1'

if that doesnt work then post the schema/table relationships (PK, FK, one to many etc)
ASKER
kdeutsch

It works but does not pull data.
The first table is tblsidpers this has no PK or FK in the SADDOTNET database and we just reference on SSN to get names.  In the SADODOTNET database the tbluic has a FK of intUICID in the tblAssignedPersonnel.
What I am trying to do is pull SSN's from the tblAssignedPersonnel and match to their names in tblsidpers and then if they have a bitpresent of 1 show them.  But this has to match to the taskforceId and UICId because permissions only allow users to see UIC's that are assigned to them.
ASKER
kdeutsch

ok, I got it working with the following, but you cought my original stuff so I will award the points.
Now I received another error on putting information in the listbox.

Private Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
        Dim taskforceID As Byte = ddlTaskForce.SelectedValue
        Dim uic As String = DDLUIC.SelectedValue

        sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
                & "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC =  '" & uic & "'))"

        ddlNames.Items.Add(New ListItem("", ""))
        buildDD(sql, ddlNames)
    End Sub
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
frankytee

what error message are you getting and what is  buildDD?
what db and front end are you using?
also
Select Case u.strSSN from tblAssignedPersonnel
doesn't look right
if your db is sql server then you need to End the Case statement,
somthing like
select
   case
       when field1 = 1 then strSSN
       else ....
   end
from tblAssignedPersonnel
etc
ASKER
kdeutsch

I recevied the eror messge beacause I was trying to build my datagrid with a dropdrop buildDD function that I made.  i got rid of case statement as well, don't know how that got in there, but it works like it should now.