How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask George Sibiya Your Own Question
George Sibiya
George Sibiya, Computer Hardware Engineer
Category: Programming
Satisfied Customers: 219
Experience:  PhD in Computer Science
94470076
Type Your Programming Question Here...
George Sibiya is online now
A new question is answered every 9 seconds

I'm using adventureworks2008 and trying to create a query

This answer was rated:

I'm using adventureworks2008 and trying to create a query but am lost and confused
JA: What programs and versions are you using?
Customer: adventureworks 2008 and Microsoft sql server management studio
JA: What is your ideal outcome? How can we solve this for you?
Customer: need a select statement that gives me the titles and file names of all documents produced for Karen Berge, Document Control Assistance at the company
JA: Anything else you want the programmer to know before I connect you?
Customer: no
Customer: replied 3 months ago.
Posted by JustAnswer at customer's request) Hello. I would like to request the following Expert Service(s) from you: Secure Remote Assistance.
Customer: replied 3 months ago.
Let me know if you need more information, or send me the service offer(s) so we can proceed.

Hi,

Welcome to JustAnswer and thank you for utilising our services.

Whats the name of your table and the list of its field/column names?

Regards,

G

Customer: replied 3 months ago.
4 tables: Person.Person, HumaResources.Employee, Production.Document, and Production.ProductDocument

Ok thanks. And please provide column names in those tables?

Customer: replied 3 months ago.
Here is a copy of the schema I am working from: www.defm.fmph.uniba.sk/.../odrobina/AdventureWorks2008_db_diagram.pdf

got it thanks. I'll post back

Customer: replied 3 months ago.
I've attached the tables with column headings as well.

Please test this one for me:

SELECT title, filename FROM Product
JOIN Person ON Person.rowguid = Product.rowguid
JOIN Employee ON Employee.BusinessEntityId = Person.BusinessEntityId
Where Person.FirstName = 'Karen' AND Person.LastName = 'Berge' AND Employee.JobTitle = 'Document Control Assistance'

Sorry it should be Document not Product:

SELECT title, filename FROM Document
JOIN Person ON Person.BusinessEntityID = Document.Owner
JOIN Employee ON Employee.BusinessEntityId = Person.BusinessEntityId
Where Person.FirstName = 'Karen' AND Person.LastName = 'Berge' AND Employee.JobTitle = 'Document Control Assistance

Customer: replied 3 months ago.
when I execute it, I get the following message:
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string 'Document Control Assistance
'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Document Control Assistance
'.
Customer: replied 3 months ago.
I correct syntax error and get the following message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Document'.

thanks for the update. Let me check it quickly

Please test again:

SELECT title, filename FROM Production.Document
JOIN Person.Person ON Person.Person.BusinessEntityID = Production.Document.Owner
JOIN HumanResources.Employee ON HumanResources.Employee.BusinessEntityId = Person.Person.BusinessEntityId
Where Person.Person.FirstName = 'Karen' AND Person.Person.LastName = 'Berge' AND HumanResources.Employee.JobTitle = 'Document Control Assistance'

Customer: replied 3 months ago.
get following error:
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'title'.
Customer: replied 3 months ago.
maybe use JobTitle?

Its a document title let me update it again

SELECT Production.Document.title, Production.Document.filename FROM Production.Document
JOIN Person.Person ON Person.Person.BusinessEntityID = Production.Document.Owner
JOIN HumanResources.Employee ON HumanResources.Employee.BusinessEntityId = Person.Person.BusinessEntityId
Where Person.Person.FirstName = 'Karen' AND Person.Person.LastName = 'Berge' AND HumanResources.Employee.JobTitle = 'Document Control Assistance'

Customer: replied 3 months ago.
Ok I did not get an error but nothing came forward. However, when I changed "assistance" to Assistant, I received 2 columns with 5 rows of data: Title and Filename columns
Customer: replied 3 months ago.
5 rows of data. I believe you did it
Customer: replied 3 months ago.
thank you very much.

Its a pleasure

Customer: replied 3 months ago.
have a great morning. I might reach out to you sometime over next week.

Thanks, ***** ***** I'd be delighted to offer more assistance

George Sibiya and other Programming Specialists are ready to help you