Conditional SELECT Statement

[cmayo]

Hi all,

I'm trying to find a way to keep a SELECT from failing if a WHERE condition is false.

This is an extreme simplification of the actual query but I think it'll illustrate the problem. In the scenario below, I need a list of all schools in the school table and, if a school has an entry in the stadium field, I need the stadium name as well.

When I run the query below, though, it fails to return school 1 since there's no stadim zero. How can I select all the schools and still get the stadium name when a valid stadium is given in the school table?

Thanks in advance,

mysql> select * from school;
+------+---------+---------+
| id   | name    | stadium |
+------+---------+---------+
|    1 | Able    |       0 |
|    2 | Baker   |       1 |
|    3 | Charlie |       2 |
+------+---------+---------+
3 rows in set (0.01 sec)

mysql> select * from stadium;
+------+---------------+
| id   | name          |
+------+---------------+
|    1 | Tiger Stadium |
|    2 | Bears Stadium |
+------+---------------+
2 rows in set (0.00 sec)

mysql> select school.id,school.name,stadium.name from school, stadium where school.stadium = stadium.id;
+------+---------+---------------+
| id   | name    | name          |
+------+---------+---------------+
|    2 | Baker   | Tiger Stadium |
|    3 | Charlie | Bears Stadium |
+------+---------+---------------+
2 rows in set (0.00 sec)

 

[JCreamerII]

Two choices remove the WHERE clause ( what's it doing anyway if you don't need to check if there is a stadium, or add a new stadium to the stadium table ( 0 = "NO STADIUM" )

Jim C

 

[cmayo]

I'm not necessarily checking if a stadium exists, I just need to pull the stadium info if it's there. I considered adding a stadium zero, but my DBA hates it when I force an 'illegal' zero-value into an auto-increment field.

Any other ideas?

 

[guelphdad]

Simply change your inner join to an outer one:

select 
school.id,
school.name,
stadium.name 
from school
left outer join stadium 
on school.stadium = stadium.id;

 

[cmayo]

Quelphdad, thank you for your suggestion. The outer join works perfectly. Is there a way to combine a JOIN with a series of WHERE statements? The actual query I'm working on is a nightmare for me... it's pulling across 7 tables, some of them multiple times, and has 18 WHERE statements linking all those tables. Using a JOIN would solve the problem I seem to be having, but I don't know how to use a JOIN and retain the other WHERE statements that I need.

If I may expand my example just a bit by adding a 'city' field to the school table,

mysql> select * from school;
+------+---------+---------+---------+
| id   | name    | city    | stadium |
+------+---------+---------+---------+
|    1 | Able    | Phoenix |       0 |
|    2 | Baker   | Phoenix |       1 |
|    3 | Charlie | Dallas  |       2 |
+------+---------+---------+---------+
3 rows in set (0.00 sec)

and I want to use your join but only return Dallas schools, i.e.

select
school.id,
school.name,
school.city,
stadium.name
from school
left outer join stadium
on school.stadium = stadium.id
where school.city='Dallas'

is there a way to do that?

 

[cmayo]

Never mind, figured it out. Thanks again for your suggestion, I think it'll work great for me.

 

[guelphdad]

Two things to note.

1) your first query above is a join, it is just written in list syntax. it is best to avoid it and use the specific ON clause.

2) make sure that when you use a LEFT JOIN that you don't include conditions that belong in your JOIN in a WHERE clause. Doing so will render your join into an INNER JOIN.

 

[cmayo]

quelphdad,

Thanks again for your help with this. I know I should spend a bit of time getting more familar with joins. They seem a lot more efficient than the WHERE statements I typically use, but I find the WHERE statements easier to read because I'm not terribly familiar with joins. A viscious circle, I expect.

Anyway, I appreciate the help. It got me where I needed to be.