[2005] Sql statement Q

[aerialz666]

How do i change this statement that filters what we show on our datagrid?

 commEmployee.CommandText = "SELECT EmployeeID 'Employee ID', EmployeeFName 'First Name', EmployeeLName 'Last Name' FROM EmployeeProfile;"

what we want to do is,

SELECT only the EMPLOYEES that does not have a similar EmployeeID on another table..

ex.
Employee table has an EmployeeID field
Attendees table has an employeeID field
what we want to select are the Employees that do not have a similar employeeID on the attendees table? is this possible on an SQL statement?

[GaryMazzone]

Select EmployeeID As [Employee ID], EmployeeFName As [First Name], EmployeeLName As [Last Name] FROM EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SecondTable)

[Hack]

Try

SELECT employeeid FROM employee emp
WHERE employeeid = '123456' 
AND emp.employeeid NOT IN (SELECT employeeid FROM attendees)

[aerialz666]

"SELECT EmployeeID 'Employee ID', EmployeeFName 'First Name', EmployeeLName 'Last Name' FROM EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SeminarActivites);"

ok this is my statement
when i ran the program the datagrid didnt show anything

[aerialz666]

Hi Hack what is the emp for? sorry not really good thanks

[aerialz666]

this is my code that shows whats to be seen in the datagrid

bt cant get it to work

  Private Sub GetEmployeeRecords()

        'Construct SELECT Command
        commEmployee = New SqlCommand
        Commemployee.Connection = connseminars
        Commemployee.CommandType = CommandType.Text
        'commEmployee.CommandText = "SELECT * FROM EmployeeProfile;"
        commEmployee.CommandText = "SELECT EmployeeID 'Employee ID', EmployeeFName 'First Name', EmployeeLName 'Last Name' FROM EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SeminarActivites);"

        'Assign Commands to DataAdapter
        daEmployee = New SqlDataAdapter
        daEmployee.SelectCommand = Commemployee

        'Fill the Dataset
        dtsEmployee = New DataSet
        daEmployee.Fill(dtsEmployee, "EmployeeProfile")
        dtEmployee = New DataTable
        daEmployee.Fill(dtEmployee)
        Me.EmployeeListDataGridView.DataSource = dtEmployee

        connseminars.Close()

        dtviewattendees = dtEmployee.Clone()

    End Sub

[aerialz666]

Sir Gary can you help me identify why the grid is not showing anything with the new statement? thnx

[GaryMazzone]

Is there an error or just no data? I have a problem with the SQL statement if alaising column names I would normally us the As Keyword an place the alias in square brackest (Select EmployeeID As [Employee ID],FirstName As {First Name].....

[aerialz666]

theres an error when i start the form

daEmployee.Fill(dtsEmployee, "EmployeeProfile")

invalid object name 'SeminarActivities' is the error tnx

[Hack]

Hi Hack what is the emp for? sorry not really good thanks

It is an alias for the name of the employee table.

There is a very good tutorial on SQL in our FAQ section. It would be worth checking out.

[GaryMazzone]

That is the table name in the sub select statement for the Where clause. So what is the actual table name that you want to query there?

[aerialz666]

yes im also currenty reading sql faqs but cant get this issue thank you

[aerialz666]

The actual table name is really SeminarActivities, can you check the statement i wrote above? mb something i did was wrong

[GaryMazzone]

What is the Database?

SELECT EmployeeID AS [Employee ID], EmployeeFName As [First Name], EmployeeLName As [Last Name] FROM EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SeminarActivites)"

Can this be run in the database system query area on its own? Do it return errors there?

[aerialz666]

database is MS SQL 2005

this is the statement i ran on the query of the db

SELECT EmployeeID 'Employee ID', EmployeeFName 'First Name', EmployeeLName 'Last Name' FROM EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SeminarActivites)

i got this error there

Msg 208, Level 16, State 1, Line 1
Invalid object name 'EmployeeProfile'.

thanks

[GaryMazzone]

Look at the statement I posted, notice any difference? there are some. Try my statement and let me know the results then.

[aerialz666]

invalid object name 'SeminarActivities' sir is the error i still get (

[GaryMazzone]

Please post the exact SQL statement the is being run. The error that is being told to us means that the table SeminarActivities does not exsist or you do not have access rights to it.

[aerialz666]

sir i entered the whole code to view the employees in the grid, and i insert the statement there i tried making the statement more simple and this is it..

commEmployee.CommandText = "SELECT * FROM EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SeminarActivites);"

[GaryMazzone]

No set a variable named strSQL as a string. Set the commEmployee.CommandText to that varable and Post the strSQL contents here and also the error number and Description.

Dim oCmd As System.Data.SqlClient.SqlCommand
Dim strSQL As String = "Select * From EmployeeProfile Where EmployeeID Not In (Select Distinct(EmployeeID) From SeminarActivites)"

Try
  Dim oDA As System.Data.SqlClient.SqlDataAdapter
  Dim oDS As New DataSet
  oCmd.CommandType = CommandType.Text
  oCmd.CommandText = strSQL
  oDA = New System.Data.SqlClient.SqlDataAdapter(oCmd)

  oDA.Fill(oDS)
  oDA.Dispose()  
Catch ex As Exeception
   MessageBox.Show(ex)
End Try

[aerialz666]

sir i get this invalid object name 'SeminarActivities'

[GaryMazzone]

Once more it is telling you there is a problem with the table name SeminarActvities. Did you do what I showed and post the actuall SQL generalted text?


As this query shows the idea works.

strSQL = "Select PersonnelPK,LastName,FirstName,MiddleName From Personnel Where PersonnelPK Not In ("
        strSQL &= "Select SubordinatePK From WorksFor Where SupervisorPK = " & intSuper.ToString() & ") And PersonnelPK <> " & intSuper.ToString()
        strSQL &= "And PersonnelPK > 1 Order By LastName,FirstName,MiddleName"

I use similar statement in many places in a database application. We are still at the point of the Sub Query table not being recognised. That is an issue either with the table name or permission on the table

[aerialz666]

Yes sir i made a new form and put those on the load of the form and that was what popped out.. what i do nxt? thank youu

[aerialz666]

sir im confused ive read some articles and also the code hack posted..
that i should do somthing like this but cant convert it to the sql statement i want..

here is what i want to accomplish..

SELECT * FROM EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SeminarActivites

and

here is what im using as guide .

SELECT Ord.OrderID, Ord.OrderDate, 
MAX(OrdDet.UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details] AS OrdDet 
INNER JOIN 
Northwind.dbo.Orders AS Ord
ON Ord.OrderID = OrdDet.OrderID
GROUP BY Ord.OrderID, Ord.OrderDate

but i cant get my sql statement to work tnxxx

[GaryMazzone]

Why would you be using an INNER JOIN statement as an example? That example returns results from the Orders table And the OrdersDetail table where there is a match on Orders.OrderID and OrderDetails.OrderID. That is nothing like what you are trying to do.


All right let's start from the begining.
1) What is the database (I believe that you saild it is SQL Server)?
2) What are all table names involved?
3) What is the structure of all table involved? (Fields and FieldType Specs).

Post the code that built the tables.

[aerialz666]

hi

1. yes
2. EmployeeProfile -> EmployeeID is only important 1
Seminars -> EmployeeID and SeminarID both are FK

im trying to copy the way he called 2 different tables in 1 SQL statement basically thanks

[GaryMazzone]

"Select * From EmployeeProfile Where EmployeeID NOT IN (Select EmployeeID From Seminars Where SeminarID = " & intSeminarID.ToString() & ")"

Now where does this SeminarID come from? Where is the SemiarActivities table you kept refering to?

[aerialz666]

hi sir thanks for the help i have achieved the initial thing i want.. by having this SQLstatement

SELECT Emp.EmployeeID, Emp.EmployeeFName, Emp.EmployeeLName FROM FinalDB.dbo.[EmployeeProfile] AS Emp where Emp.EmployeeID NOT IN (Select Sem.EmployeeID From FinalDB.dbo.[SeminarActivities] as Sem

what i dont get now is how to add this to the sub selectstatement..

if SeminarID IN seminar activities is = textbox

it should be something like

(Select Sem.EmployeeID From FinalDB.dbo.[SeminarActivities] as Sem where SeminarID <> '" & txtSeminarID.Text & "' );"

for my selectstatement .. thanks

[GaryMazzone]

Again I'm counfused. What is SeminarActivities? How does this relate to the other two tables? What is in the textbox that is SeminarIDs, a bunch of number? a bunch of names? Is this a query with a subquery as the where clause with an other sub query as the Where clause of the first?

[aerialz666]

its a table containg FK of EmployeeID and SeminarID basically the next step and last step i need to do is

add this statement...

if SeminarID IN seminar activities is <> txtSeminarID.text

basically what i want to accomplish is that it will only read the rows that have a seminarID not equal to the one in the SeminarID.text

to...

SELECT Emp.EmployeeID, Emp.EmployeeFName, Emp.EmployeeLName FROM FinalDB.dbo.[EmployeeProfile] AS Emp where Emp.EmployeeID NOT IN (Select Sem.EmployeeID From FinalDB.dbo.[SeminarActivities] as Sem

[aerialz666]

ok rephrased..

what is the sql statement for tis

select EmployeeID from SeminarActivities where SeminarID <> SeminarID.text

[GaryMazzone]

"Select EmployeeID,EmployeeName From EmployeeProfile Where EmployeeID Not IN (Select EmployeeID From SeminarActivities Where SeminarID Not In (" & txtSeminarID.text & "))"

Now this assumes that you have listed a bunch of number in the txtSeminarID textbox sepperated by commas.

This return a list of Employees how have not taken the seminars listed.

[GaryMazzone]

If you want a list of Employees who have not taken a specified Seminar (only on here).

"Select EmployeeID,EmployeeName From EmployeeProfile Where EmployeeID NOT IN (Select EmployeeID From SeminarActivities Where SeminalID <> " & txtSeminarID.Text.ToString() & ")"

[aerialz666]

thanks man figured it out got another problem