Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2012
    Posts
    9

    Question Unanswered: This makes no sense to me??

    I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
    PLEASE help me understand this....

    Click image for larger version. 

Name:	Itisvalidcolumnname.JPG 
Views:	17 
Size:	45.2 KB 
ID:	13565

    Click image for larger version. 

Name:	butitwon'twork.JPG 
Views:	16 
Size:	25.3 KB 
ID:	13566

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in the second of your two screenshots, you have two SELECT statements

    the first one is okay, but the second one is missing the FROM clause, consequently any column name used in it is invalid

    also, i think you need to join your employee and job_title tables properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2012
    Posts
    9

    Question Thanks, but how??

    You say I need to properly join the Employee and Job_Title tables, but I am not sure how to do that.... Please elaborate, and thanks for the response!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    google "sql tutorial" -- there are hunnerts of them -- and look up how joins work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2012
    Posts
    30
    so whats output you're expecting?

  6. #6
    Join Date
    Aug 2012
    Posts
    9

    Question I know how JOINs work but....

    I know how to use JOINs, but when I try to write a Join into this code, it keeps complaining about syntax errors, or claiming that tables I add are not valid tables.... It is monstrously frustrating to be told that table is not valid when you are looking right at it in front of you on the screen, but when I run this query without including the Where statement, it properly lists all fields, when I add the Where, it all of a sudden does not recognize the tables entered??
    Anyway, I need to find the maximum, and the minimum salary for all exempt employees, and for all non-exempt employees, and I thought this was a way to do so, but it is not working for me....

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zlloyd1 View Post
    I know how to use JOINs, but when I try to write a Join into this code, it keeps complaining about syntax errors, or claiming that tables I add are not valid tables....
    i'll walk you through it

    show your query, and then show the error message it produces

    plain text, please (it's hard for us to copy and edit code that's in a screenshot)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, let's back up a step or three...

    Do you realize that you have two different SQL SELECT statements in your second example instead of one SQL statement? That is why you are getting the syntax error, because there Salary is a column name but you haven't listed any tables in your second SELECT statement.
    Code:
    SELECT FName, LName, Area_code, Salary, Job_Title.Status
       FROM Employee, Job_Title
       WHERE  Status = 'exempt'
    The syntax you've used was the standard in SQL-89, but it was depricated (on its way out) with the releast of SQL-92. In this form, it is known as a Cartesian Product, and produces a few correct answers interspersed between a lot of "garbage" answers.

    r937 is quite correct, when you have two tables you have to either specify that you want every row in one table joined with every row in the other table (normally a bad idea called a CROSS JOIN or a Cartesian Product), or you have to specify how you want the rows to be matched between the tables (normally an INNER JOIN). As r937 suggested, it would help for you to understand the JOIN clause of the SELECT statement.
    Code:
    SELECT Max(Salary)
    According to SQL syntax, this ought to produce an error because there are no tables listed to provide the Salary column!

    You're frustrated because you are still learning the fundamentals of SQL at this point. It takes a bit more time and effort to get past this stage, but things get dramatically better once you do!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Aug 2012
    Posts
    9

    Question Any help would be GOLDEN here

    @r937,
    You absolutely right, in fact I do not even have SQL Server on my computer and am forced to use the university's student lab version. I know next to nothing about SQL Server to be honest, but I am trying to learn, and it is annoying me to no end.
    The query I wrote initially was the one you saw in my original attachments,

    Select * FROM Employee, Job_Title
    Where Status = 'exempt'

    but, although this does join the two tables and displays all records where the status is exempt, I need to take the results of this query and find the minimum and maximum values for the field Salary, and my only notion to do this was to try and add the line Select Max(Salary) to this query, but that was a wash and came back griping at me that Salary was an invalid column name....
    If you could spell this out for me, I would be eternally in your debt!!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, about the join... you are right, it does join the two tables, but it's a cross join, and in almost all cases, and especially this one, what you really want is an inner join

    look up the difference

    as for the max and min salary, you have to put those into the SELECT list instead of that dreaded, evil "select star"
    Code:
    SELECT MAX(salary)
         , MIN(salary)
      FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2012
    Posts
    9

    Red face

    Thanks you, that seems to work, but I am having a new problem now....
    The WHERE clause is resetting all of the fields in my Employee table to exempt for some reason.
    I swear, I am getting sick to my stomach with this ignorant piece of Microsoft trash finding new and improved ways to not work.
    Anyway, this is the code I was using:
    Code:
    Select * From Employee, Job_Title
    Where Status = 'exempt'
    and what I get back is the combination of both tables, but for some reason the status has changed to exempt for all of them, instead eliminating the ones that were non-exempt.
    I have included a couple of attachments to show you what I am saying. As you can possibly see from the second attachment, for some reason the Where clause is setting every employee to be assistant manager, but they are not all assistant managers, as you can see in the first attachment,under the field Title. I cannot understand why WHERE is resetting field values, I thought it was used to weed out unwanted rows, not change them all to be included??
    Attached Thumbnails Attached Thumbnails workingright.JPG   notright.JPG  

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i understand the results you are getting

    and it's because you just aren't listening to me

    i'll say it again --

    your query does join the two tables, but it's a cross join, and in almost all cases, and especially this one, what you really want is an inner join

    until you change your join from a cross join to an inner join, you're gonna have a bad time

    please, go do some research and find out how to do an inner join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •