CODEWITHSUNDEEP
sqlmysql-workbench
Triber
Triber

Reputation: 1

My SELECT query doesn't return any values but only show an empty grid

I'm new to SQL programming and having a problem with a query that doesn't return the values but only an empty query with status and city but no values.

S has the following attributes: sNo, sName, status, city SP has the following attributes:sNo, pNo, qty,

Can anyone explain to me why the values don't come up?

SELECT S.status, S.city
FROM S, SP
WHERE S.sNo='Paris' AND SP.sNo=S.sNo;

Upvotes: 0

Views: 108

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

First, if you are learning SQL, you should be learning SQL correctly. Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

So, your query should look like this:

SELECT S.status, S.city
FROM S JOIN
     SP
     ON SP.sNo = S.sNo
WHERE S.sNo = 'Paris' ;

Why does this return no results? Here are some reasons:

  • No rows in S and SP share the same city.
  • S.sNo never has the value 'Paris'.
  • One or the other table has no rows (this is a subset of the first reason, but worth highlighting).

In this case, the most likely reason is the sNo never has the value 'Paris'. You have not provided any data, so I can only speculate that you intend:

SELECT S.status, S.city
FROM S JOIN
     SP
     ON SP.sNo = S.sNo
WHERE S.city = 'Paris' ;

But that would make sense.

One thing is suspicious. I would expect a key column called sNo to be numeric. In most databases, a comparison to a string (such as 'Paris') would return a type conversion error. MySQL does implicit conversion. So, if sNo is a number, then the comparison is interpreted as sNo = 0 -- and that could actually return rows if the condition were true.

Upvotes: 3

Rima
Rima

Reputation: 1455

in where clause s.city ='paris'

query

  SELECT S.status, S.city
    FROM S, SP
    WHERE S.city ='Paris' AND SP.sNo=S.sNo;

Upvotes: 2

Related Questions