Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to select the name and date of birth of all living artists who have value more than 50. My tables are artist and picture and I wrote this SQL statement but it is not giving me the desired result. Please advise.

SELECT name, dateOfBirth
FROM artist 
    JOIN exhibitions.picture
        ON exhibitions.picture.name
WHERE exhibitions.picture.value = '50'
    AND artist.dayOfDeath is NULL;
share|improve this question
    
Is value a character type? Does it contain non-numbers? –  dezso Oct 26 '12 at 13:12
add comment

3 Answers

Assuming that value is a numeric data type:

SELECT 
    A.name
    , A.dateOfBirth

FROM
    artist A
    JOIN exhibitions.picture P
        ON A.name = P.name

WHERE
    P.Value > 50
    AND A.dayOfDeath is NULL;
share|improve this answer
add comment

You have to use >50

SELECT artist.name, artist.dateOfBirth 
FROM artist JOIN exhibitions.picture 
              ON artist.name=exhibitions.picture.name 
WHERE exhibitions.picture.value>='50'
  AND artist.dayOfDeath is NULL;
share|improve this answer
    
Error Code: 1052. Column 'name' in field list is ambiguous –  kenny Oct 25 '12 at 16:13
    
Keep alias name like table name in front of column name like exhibitions.picture.name from which table name u want. Give full table structure fields we will give you query –  kumar_2002 Oct 25 '12 at 16:18
    
@kenny its because whenever you join two or more tables it may contain same column name on each tables ,so whenever you specify column name always reference column names as <tablename.columnname> so it will tell the query engine to take correct column from the right table (So there is no ambiguity) –  Biju jose Oct 26 '12 at 11:25
add comment
SELECT name, dateOfBirth
FROM artist 
    JOIN exhibitions.picture
        ON exhibitions.picture.name
WHERE exhibitions.picture.value >'50'
    AND artist.dayOfDeath is NULL;

By this query, you will get only more than 50yrs living artists. Please use greater than condition for correct results.

share|improve this answer
    
If value is a CHAR column, then the comparisons will not yield wanted results because the order used is lexicographic. '9' > '50' is true for example. –  ypercube Oct 27 '12 at 12:46
    
The ON condition seems erroneous as well. –  ypercube Oct 27 '12 at 12:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.