SELECT clause: Optional Columns?

Question

I want to run a single query on my MYSQL database. The table I am searching has a file_type field that will be one of three values (1,2,3). Depending what these values are I want to look for different information from the database.

So for example if the 'file_type' field is 1 or 2 then I want to SELECT fields a,b,c,d However if I notice that file_type = 3 then I want to SELECT fields a,b,c,d,e,f

Can this be done in a single SELECT statement? like this - ish

my_simple_query = SELECT file_type,a,b,c,d FROM table1
my_new_query = SELECT file_type,a,b,c,d (AND e,f IF file_type = 3) FROM table1

thanks all

---------------------------------- ADDITION -----------------------------------

And how would I do this if e,f were stored in another table?

my_multitable_query = SELECT file_type,id,a,b,c,d (AND e,f FROM table2 WHERE id=id) FROM table1

get my drift?

Answer 1

You may be happier using a non-relational database such as CouchDB or MongoDB if you need non-relational data.

In SQL, you have to declare all the columns at the time you write the query. All these columns must be present on every row of the result set; the set of columns cannot vary based on the content in each row. This is practically the definition of being a relational database (along with the requirement that each column has the same type on every row).

---

Regarding your additional question, this may be getting somewhere:

SELECT t1.file_type, t1.a, t1.b, t1.c, t1.d, t2.e, t2.f
FROM table1 t1
LEFT OUTER JOIN table2 ON t1.id=t2.id;

You'll still get columns e and f on every row of the result set, even if they're NULL because there is no match in table2 for a given row in table1.

This leads to you a pattern called Class Table Inheritance.

Answer 2

No, SQL SELECT statements do not support optional columns.

But you can specify logic to only return values based on criteria, otherwise they would be null:

SELECT a,b,c,d,
       CASE WHEN file_type = 3 THEN t2.e ELSE NULL END AS e,
       CASE WHEN file_type = 3 THEN t2.f ELSE NULL END AS f
  FROM TABLE_1 t1
  JOIN TABLE_2 t2 ON t2.id = t1.id

Answer 3

Not familiar with mysql, but why not using a UNION?

> SELECT file_type,a,b,c,d FROM table1 WHERE file_type in (1,2)
> UNION
> SELECT file_type,a,b,c,d,e,f FROM table1 WHERE file_type = 3