join query help needed

Question

I have two tables

student

id      name
1-123   XXX
1-124   YYY

zipcode

zip  city
123  dk
124  raj
125  bog

I need to display city name beside every student. I have tried to use the following query in mysql

SELECT student.name as name, SUBSTR(student.id,3,3) as std_zip, zipcode.city
FROM student LEFT JOIN zipcode ON std_zip=zipcode.zip

but result return all rows of zipcode table but i need the output as

name std_zip city
XXX  123     dk
YYY  124     raj

Answer 1

You can't use a SELECT alias in the ON or WHERE clauses of the same query, you need to use the expression itself.

SELECT student.name as name, SUBSTR(student.id, 3, 3) as stud_zip, zipcode.city as city
FROM student
LEFT JOIN zipcode
ON SUBSTR(student.id, 3, 3) = zipcode.zip

To avoid doing the SUBSTR twice, you can use a subquery:

SELECT student.name, student.zip, zipcode.city
FROM (SELECT name, SUBSTR(student.id, 3, 4) as zip
      FROM student) student
LEFT JOIN zipcode
ON student.zip = zipcode zip

In this case, the first version is likely to perform better. See the FIDDLE

Answer 2

You can't JOIN using std_zip because it's not a real field. You need to call SUBSTR again when you JOIN.

SELECT student.name AS name,
SUBSTR(student.id,3,3) AS std_zip, zipcode.city

FROM student

LEFT JOIN zipcode ON SUBSTR(student.id,3,3)=zipcode.zip

DEMO: http://sqlfiddle.com/#!2/d0478/2

---

However, in MySQL, it's bad practice to have multiple values in one field. You should really make zip its own field in the student table. It should look something like this:

id   zip   name
1    123   XXX
2    124   YYY

Then your query would be much nicer looking:

SELECT student.name AS name, student.zip, zipcode.city

FROM student

LEFT JOIN zipcode ON student.zip=zipcode.zip

New demo: http://sqlfiddle.com/#!2/8137b/2

Answer 3

SELECT s.name, z.zipcode
FROM student AS s
LEFT JOIN zipcode AS z ON SUBSTRING(s.id, 3) = z.zip

because subtring is a function and the result is not a 'real' table field you cannot use it in the ON-clause. So using substring in the on-clause should do the trick.

Is there any specific reason why you wil have the zipcode in the ID? What happens with the 10th student in your table? What ID does he/she have?