CODEWITHSUNDEEP
mysqldata-structures
Artog
Artog

Reputation: 1142

SQL query as (dynamic) column value

Consider the following MySQL scenario:

Table1:
id, date, value

Table2:
id, month, count

Table one exists of many rows spanning over 1 year (the dates) and different values

Is there any way to have the values of Table 2 columns 'count' as SQL queries?

Example: row 1 in Table2

ID | Month | Count
------------------------------------------------------------------------------
1  | 07    | SELECT COUNT(*) FROM Table1 WHERE MONTH(DATE) = Table2.month

So if i do a select * from Table2 where month = 07

Then I would get the number of rows from Table one with the corresponding month.

Some kind of a dynamic column value for each rows, is there a way?

Note: I mean if there is any built in way, not using a subquery like

SELECT *, 
(SELECT COUNT(*) FROM Table1 WHERE MONTH(date) = Table2.month) as count
FROM Table2 where id = 2352

as my real queries will use multiple and thus get really messy :(

Upvotes: 0

Views: 250

Answers (2)

nobody
nobody

Reputation: 10645

I would use a TRIGGER to update the Count column in related table2 rows whenever there is a change in table1.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29639

Have you looked at creating a view? http://dev.mysql.com/doc/refman/5.0/en/create-view.html.

It won't stop the messiness of your query, but it does allow you to concentrate it all in one place.

Upvotes: 1

Related Questions