MYSQL Sub -queries

What is a Subquery?

A subquery is, in essence, a SQL statement that is most often used as part of
another SELECT statement.

Sub queries are used in order to achieve very complex searches and complex reports.

Here is a live example:

http://38.101.195.102/time/report.php?v=1&flag=0

The above is an example of the reporting and formatting capabilities of MySQL sub queries.

The reason sub queries were used for this is because of database design, this column aAttr, holds 3 different values (1,2,3).

1 being "yes"

2 being "no"

3 being "do not know"

With this simplicity comes complexity (mo money, mo problems).

We need to take the values in one column and make them into distinct values that can be used in a real world example.

The solution I came up with is (sub queries), you know what they say, don't be afraid go towards the light.

Here my sql statement:

$sql ="SELECT table.qid,table.quest,table.aComm,";
$sql.="(select count(aAttr) from table where table.aAttr = 1 and aCounty = '" . $county . "' and table.qid = table.qid) as Yes,";
$sql.="(select count(aAttr) from table where table.aAttr = 2 and aCounty = '" . $county . "' and table.qid = table.qid) as No,";
$sql.="(select count(aAttr) from table where table.aAttr = 3 and aCounty = '" . $county . "' and table.qid = table.qid) as DKN,";
$sql.="(select count(aAttr) from table where table.aCounty = '" . $county . "' and table.qid = table.qid)as Total";
$sql.=" FROM table, table";
$sql.=" WHERE table.qid = table.qid";
$sql.=" AND table.aCounty = '" . $county ."'";

Look at the bold in the statement above, I only highlighted one, but there are 3 sub queries in the above statement.

(select count(aAttr) from table where table.aAttr = 1 and aCounty = '" . $county . "' and table.qid = SectionA.qid) as Yes

All this is, is a simple select statement that is putting the value in the column aAttr that holds the value of (1) and calling it yes.

If you take the time and look through the statement, you will see the same pattern on 2 more, one for No and another for DKN (do not know, my favorite).

When spitting this out, it will conjure up variables that you can all kinds of cool stuff with, like our report in the example above.

Now the one thing I learned from programming, is to only use what you need, don't complicate things to over come.

I used sub queries, because it interfaced great with the database design I had put together for a survey.

You have to find your way, all I am doing is providing you with a different path.

If you think about it the above example is pretty straight forward

Remember one thing: Think through what you need to accomplish and use the right tools.

The right tool for the job was subqueries

Thank you and check back for more.