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

Question

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;

Obviously without seeing your data it's impossible to say for certain but I'd guess that your sNo column maybe contains numbers and not city names. Also you should learn how to use proper INNER JOIN syntax instead of the old fashioned practice of separating the table names with commas — ADyson
– ADyson, Commented Apr 13, 2019 at 7:12
On a side note: Don't use comma-separated joins. If you are being taught to do so by a book, tutorial or class, then quit it. Use proper ANSI joins instead. But why are you joining SP at all? You are neither using it in the select clause nor in the where clause. If you want to ensure that an SP record exists for an S row, then use IN or EXISTS instead of a join. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 13, 2019 at 12:17

Gordon Linoff · Accepted Answer · 2019-04-13 12:06:37Z

3

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.

answered Apr 13, 2019 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Triber Over a year ago

Very good feedback, Gordon - thanks a lot! Regarding the use of the comma in the FROM-clause, I have been taught to do it through a course book from Elmasri & Navathe but has seen tutorials where a join is used instead - that confused me. But you are sure right that sNo is numeric, I really must have been tired.

Gordon Linoff Over a year ago

@Triber . . . There are many books (including my own) that would teach you proper SQL syntax.

Rima · Accepted Answer · 2019-04-13 07:06:22Z

2

in where clause s.city ='paris'

query

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

answered Apr 13, 2019 at 7:06

Rima

1,4551 gold badge9 silver badges14 bronze badges

1 Comment

Triber Over a year ago

Well that is obvious now that you write it :D I need to pay more attention to the attributes! Thanks a lot!

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related