Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query
Struggling With a SQL query Struggling With a SQL query
Struggling With a SQL query
Go Back  Xtreme Visual Basic Talk > > > Struggling With a SQL query


Reply
 
Thread Tools Display Modes
  #1  
Old 06-17-2013, 07:04 AM
nick_d nick_d is offline
Regular
 
Join Date: Jan 2004
Location: Derby
Posts: 60
Default Struggling With a SQL query


I'm struggling with a sql query


I want to show the names of the employee for whom the task is set for,
and the name of the employee who set the task; along with details of the task itself.

I think I may have to use column / field aliasing but I've never done that before so
I really would like some assistance with the following sql query (mdb).

I'm fine with the Where statement, I think I'm falling over where it's trying to separate the emp_id to the emp_details.name and Tasks.FromEmpID and Tasks.ForEmpID.
The result set is showing the same emp_details.name for both as the EmpID in Tasks.ForEmpID

Tables:
Tasks, key = taskID
emp_details, key = emp_id

Variables: Taskstat = string

TaskSql = "SELECT Tasks.taskID, Tasks.tasktitle, Tasks.jobID, Tasks.ForEmpID, emp_details.name, Tasks.taskdescription, Tasks.taskcomments, Tasks.taskhistory, Tasks.wakedate, Tasks.taskstatus, Tasks.FromEmpID, emp_details.name
FROM emp_details INNER JOIN Tasks ON emp_details.emp_id=Tasks.ForEmpID
WHERE ((datevalue(Tasks.wakedate)<= datevalue(""" & Date.Today & """))AND ((Tasks.taskstatus)='" & Taskstat & "'))"

Thanks for any assistance
Reply With Quote
  #2  
Old 06-17-2013, 11:42 AM
Gruff's Avatar
GruffStruggling With a SQL query Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

1)I usually code my query string so that it is easily readable.
This way some simple typos can be caught quickly.
2) I do not know if you really need to use a JOIN in this case.
3) Send the complete string to the immediate window during developement to check the any conversions from VB variables into the query.
4) Be careful of using the DateValue() function as it uses the system date format.
http://technet.microsoft.com/en-us/l.../ee634543.aspx

Public Class Form1

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

Dim s As String = ""
Dim TaskStat As String = "FAB" 'Test Status

s = "SELECT "
s &= "t.taskID,"
s &= "t.taskTitle"
s &= "t.JobID,"
s &= "t.ForEmpID,"

s &= "e.name,"

s &= "t.TaskDescription,"
s &= "t.TaskComments,"
s &= "t.TaskHistory,"

s &= "t.TaskWakeDate,"
s &= "t.TaskStatus,"
s &= "t.FromEmpID "

s &= "FROM Tasks t, Emp_Details e "

s &= "WHERE e.Emp_ID = t.Emp_ID"
s &= "AND datevalue(t.wakedate) <= datevalue('" & Date.Today & "')"
s &= "AND t.taskstatus = '" & TaskStat & "'"

Dim sCheck As String = s.Replace(",", "," & vbNewLine)
sCheck = sCheck.Replace("FROM", vbNewLine & "FROM")
sCheck = sCheck.Replace("AND", vbNewLine & "AND")
sCheck = sCheck.Replace("SELECT ", "SELECT " & vbNewLine)

Debug.Print(sCheck)

End Sub
End Class
__________________
Burn the land and boil the sea
You can't take the sky from me


~T

Last edited by Gruff; 06-18-2013 at 10:06 AM.
Reply With Quote
  #3  
Old 06-18-2013, 07:13 AM
nick_d nick_d is offline
Regular
 
Join Date: Jan 2004
Location: Derby
Posts: 60
Default

Thankyou for the prompt reply and the excellent way of creating the query string and warning about potential date issues.

My biggest problem with the query was in showing the employee name of :
a) who the task is for :ForEmpID is employee number/ID(emp_id) of this person;
and
b) who set the task: FromEmpID is employee number/ID(emp_id) of this person.

The Employee Name (emp_name) is accessed via the employee table's (emp_details) key field which is emp_id.

I appreciate that it is extremely difficult to understand nuances of a MRDB in a brief query and thank you for any assistance.
Reply With Quote
  #4  
Old 06-18-2013, 08:22 AM
DrPunk's Avatar
DrPunkStruggling With a SQL query DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

If I understand your question correctly, you need to join the employee table twice. And yes, you'll need to use aliases for that.

Code:
Select Tasks.Title, Tasks.ForEmpID, ForEmp.Name AS ForEmpName, Tasks.FromEmpID, FromEmp.Name AS FromEmpName
FROM Tasks
    INNER JOIN emp_details AS ForEmp ON Tasks.ForEmpID = ForEmp.emp_id
    INNER JOIN emp_details AS FromEmp ON Tasks.FromEmpID = FromEmp.emp_id
WHERE ...
__________________
There are no computers in heaven!
Reply With Quote
  #5  
Old 06-20-2013, 04:45 AM
nick_d nick_d is offline
Regular
 
Join Date: Jan 2004
Location: Derby
Posts: 60
Default

Hello DRPunk,
thankyou for the reply, I think I maybe getting the hang of it but am getting the following error with this query:

TaskSQL = "Select Tasks.taskID, Tasks.Title, Tasks.ForEmpID, ForEmp.Name AS ForEmpName, Tasks.FromEmpID, FromEmp.Name AS FromEmpName
FROM Tasks
INNER JOIN emp_details AS ForEmp ON Tasks.ForEmpID = ForEmp.emp_id
INNER JOIN emp_details AS FromEmp ON Tasks.FromEmpID = FromEmp.emp_id

WHERE Tasks.taskstatus='" & Taskstat & "'"

Error Message:
Sorry - there is a problem in Tasks module getting Task Details
System.Data.OleDb.OleDbException(0x80040E14):Syntax error (missing operator) in query expression
'Tasks.ForEmpID = ForEmp.emp_id INNER JOIN emp_details AS FromEmp ON Tasks.FromEmpID = FromEmp.emp_id'

at System.Data.Oledb.oledbCommand.ExecuteCommandTextErrorHandling(OleDbHR esult hr) at ...............


Just in case it's relevant:

Tasks Table
key = taskID (number), tasktitle (text), jobID (number), ForEmpID (number), FromEmpID (number)
taskdescription (text), taskcomments (text), taskhistory (text), wakedate (date), taskstatus (text)


Relevant fields from emp_details
emp_id (number), name (text)

I wonder if you could point out where I'm going wrong, or should I just scrap this and put ForEmpName and FromEmpName text fields in the Tasks table (which may solve this problem but not help my knowledge of aliasing!)

Regards,
Nick
Reply With Quote
  #6  
Old 06-20-2013, 05:29 AM
DrPunk's Avatar
DrPunkStruggling With a SQL query DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Unfortunately I don't have Access to test the query on to make sure I've got the syntax spot on. The query I wrote works on SQL Server.

Quick google on joining same table twice brings up this MSDN page (http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx) and the query they have there doesn't have AS between the table name and the alias name in the query. So maybe try that first...
Code:
Select Tasks.Title, Tasks.ForEmpID, ForEmp.Name AS ForEmpName, Tasks.FromEmpID, FromEmp.Name AS FromEmpName
FROM Tasks
    INNER JOIN emp_details ForEmp ON Tasks.ForEmpID = ForEmp.emp_id
    INNER JOIN emp_details FromEmp ON Tasks.FromEmpID = FromEmp.emp_id
WHERE ...
This SHOULD work, there's no reason to go adding fields to your tables. Sorting all the updating and creating of the records should take longer than fixing this.
__________________
There are no computers in heaven!
Reply With Quote
  #7  
Old 06-20-2013, 12:28 PM
nick_d nick_d is offline
Regular
 
Join Date: Jan 2004
Location: Derby
Posts: 60
Default

Thanks for your assistance and patience DrPunk:
This one works with Access

SELECT Tasks.taskID, Tasks.tasktitle, Tasks.taskdescription, emp_details.emp_id, Tasks.FromEmpID, emp_details.name, Tasks.ForEmpID, emp_details_1.name, Tasks.taskstatus
FROM emp_details AS emp_details_1
INNER JOIN (emp_details INNER JOIN Tasks ON emp_details.emp_id = Tasks.FromEmpID) ON emp_details_1.emp_id = Tasks.ForEmpID
WHERE Tasks.taskstatus='" & Taskstat & "'"

Last edited by nick_d; 06-20-2013 at 12:29 PM. Reason: Addition
Reply With Quote
  #8  
Old 06-21-2013, 04:32 AM
DrPunk's Avatar
DrPunkStruggling With a SQL query DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

I still have no idea why the query I wrote doesn't work in Access. I might see if I can work out why when I'm on a PC with Access.

But I must say that I don't really like that you are querying Tasks from emp_details. I appreciate that it works, but it's not right really. You should be querying Tasks from Tasks. If you wanted to know emp_details that have tasks then querying from emp_details would be OK. It doesn't make a huge amount of difference here but if you tried to start adding other stuff to the query then it might become a problem.
__________________
There are no computers in heaven!
Reply With Quote
  #9  
Old 06-23-2013, 05:30 AM
DrPunk's Avatar
DrPunkStruggling With a SQL query DrPunk is offline
Senior Contributor

* Expert *
 
Join Date: Apr 2003
Location: Never where I want to be
Posts: 1,403
Default

Looks like it's some guff with Access SQL syntax about needing brackets if a table's used twice, or something. I don't know the rules beyond this many tables used, but the following will work.
Code:
Select Tasks.Title, Tasks.ForEmpID, ForEmp.Name AS ForEmpName, Tasks.FromEmpID, FromEmp.Name AS FromEmpName
FROM (Tasks
    INNER JOIN emp_details ForEmp ON Tasks.ForEmpID = ForEmp.emp_id)
    INNER JOIN emp_details FromEmp ON Tasks.FromEmpID = FromEmp.emp_id
WHERE ...
Unbelievable.
__________________
There are no computers in heaven!
Reply With Quote
  #10  
Old 07-08-2013, 09:32 AM
nick_d nick_d is offline
Regular
 
Join Date: Jan 2004
Location: Derby
Posts: 60
Default

Sorry for the delay in replying, I've been on holiday for a few weeks.
Thankyou very much for your persistence and help Dr Punk,
Reply With Quote
Reply

Tags
sql querty


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query Struggling With a SQL query
Struggling With a SQL query
Struggling With a SQL query
 
Struggling With a SQL query
Struggling With a SQL query
 
-->