MYSQL :: Aggregate/sum With Distinct Conditional

Aggregate SQL
I have a table as follows, representing a series of games for a sport.

+--------+---------+---------+--------+
| gameID | team1id | team2id | winner |
+--------+---------+---------+--------+
| 2502 | 22 | 12 | 12 |
| 2503 | 21 | 13 | 21 |
| 2515 | 13 | 11 | 11 |
| 2516 | 22 | 14 | 14 |

Each game stores the game's winner as the winning team's id in the winner column.
I'd like to produce a table with each team's id, followed by the number of wins and losses, like so:

teamid wins losses
----------------------
1234 | 3 | 2
4321 | 1 | 0

Aggregate AVG
I have a MySQL call:PHP Code:

$sql = mysql_query("SELECT COUNT(id) AS count, AVG(rating_avg) AS avg, SUM(views) AS views FROM articles WHERE author= '".$this->author."' AND status = '10' GROUP BY author");

As you can see I have three aggregate functions in the statement. The problem comes in with getting the average rating -- AVG(rating_avg)The call is looking at several articles written by authors. If an article has not been rated by any visitors, it has a rating of 0.00.Is it possible to setup a mysql statement that gets all three aggregate functions but leaves out a rating of 0 in the average function?

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications.

I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct.

If I use the following select:

Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12'

I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct.

So how do I find all of the distinct product_ID but also show other columns such as region?

Do I need to do this with some kind of self-join?

Aggregate Keys
CREATE TABLE `tbl_vel_product` (
`family_id` varchar(25) NOT NULL default '',
`catagory_id` varchar(25) NOT NULL default '',
`colour_id` varchar(25) NOT NULL default '',
`price` int(11) NOT NULL,
`qty_in_stock` int(25) NOT NULL,
PRIMARY KEY (`family_id`,`catagory_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Where my primary key is several foreign keys from other tables. I have a webform that returns this product key and I wish to search for the product with that key..

How do I construct a query of the form..

SELECT * from tbl products WHERE [family_id, catagory_id, colour_id] = [URL_PARAMETER]...?

Aggregate Functions
Are aggregate functions fast when used with large sets of rows?

Aggregate Functions
I have a table PUPILS with a field Name (varchar). The problem: how to select a longest Name in the table and its length? I mean something like this:

SELECT Name, (MAX(LENGTH(Name))...

It doesn't work, of course. I tried 'grouping by' but this also fails.

Aggregate Functions
I have defined a table like this:

Wrote(Aname varchar(50), ISBN varchar(30))

In oracle I could run the following query:

select max(count(*)) from Wrote group by ISBN;

However, in mysql it says invalid use of group function when I try that.
What is the myql equivalent?
I've been using this:

select max(cnt) from(select count(*) cnt from Wrote group by ISBN) A;
Is that the simplest way to do it in mysql?

Aggregate Function
I am kind of confuse how to select this data Which city has the most airports. There are two tables airport and cities the airport has two column IATA_CODE and the name of airports where as city has the IATA_CODE and the name of the cities.
I used two diffrent ways but I dont't which one is right.

select name, count(distinct IATA_CODE) from airports group by name;

SELECT c.name,count(*) AS no_of_airports
FROM airports a
LEFT JOIN cities c ON c.IATA_CODE =
a.CTY_IATA_CODE
GROUP BY a.CTY_IATA_CODE;
both have the same result and I coudn't be able to select once city who has the most airports.

Aggregate Time Sum
I have many "track length" fields in a table, I wish to have these all added up and produced as a field... Much like "SELECT SUM(`x`) FROM `table` WHERE 1"

I see there is an ADDTIME but... This adds one value to another, I just wish to add all the fields together

Aggregate Functions
think i should get a bed here atm, you guys are such a help.

i dont think this is possible but would be GRAND if it was... i want to be able to do a calcuation on two SUM's in an sql statement.

Quote: SELECT gref, pref,COUNT(pref) as played, SUM(amountwon) as won, SUM(stake)as staked, players.*
FROM winnings, players
WHERE pref = playerref
group by pref
order by playername

i would like to work out a balance.. WON - STAKED. but i cant get it to work.. well i can with php after but this means i cant sort on it which would be what i want to produce a rankings table with the leaders sorted by the highest balance.

Aggregate Function (AVG) Query Q.
I've been looking at the AVG function, and am trying to figure out how to return a very specific, complex value using the function.

Query:
sqlTESTavg_curr = "SELECT AVG(DISTINCT TESTcomp) FROM TEST2005 WHERE (StatusCluster = 'Freshman') AND ((Class_Num = 2)"

I am trying to et the average of current freshmen applicants TEST scores. The problem is, my table is designed to capture a record for every single student every single day. I can specify to look for only records with dates of "today" - but if I try to use the sql above- it's not going to be correct because I have multiple records with identical scores which will skew the average.

We have student IDs that are the table- what I would like to do is set the query to return distinct by the Student ID's but actually return the average of the TESTcomp fields.

so - something like this:
sqlTESTavg_curr = "SELECT DISTINCT STU_ID AVG(TESTcomp) FROM TEST2005 WHERE (StatusCluster = 'Freshman') AND ((Class_Num = 2)"

Obviously that doesn't work. Is this just too complex of a query? How should I go about getting the average of a field on the values from a distinct field that is not the same?

Create Aggregate Function
i want to create my own aggregate function........

when i run the following code :

[code]DELIMITER $$

DROP FUNCTION IF EXISTS `tradedb`.`abc` $$
CREATE AGGREGATE FUNCTION `tradedb`.`abc` () RETURNS DECIMAL
BEGIN

END $$

DELIMITER ;
[code]

i get the following error in the mysql query browser.

Script line: 4AGGREGATE is not supported for stored functions

Any Way To Speed Up Queries That Aggregate?
I am developing a web-based data analysis tool that will (hopefully) include large detailed record databases. I want to make the query response times as fast as possible, though I am not sure how fast that should be. Ultimately, everything will be done through PHP, though I am now testing straight from MySQL.

My first data set includes about 250,000 unique records, with about 120 fields (mostly shortint). Most of those fields are category type fields, that can be used in a GROUP BY statement. I have a few true numerical fields on which mathematical operations (mostly average) will be applied. The problem is that I want the users to be able to select any possible combination of Grouping variables, so there is no way I can index every possible combination. As a result, the queries are somewhat slow when I aggregate using GROUP BY with two categories - around 6 seconds. (Maybe this isn't slow?)

I've tried breaking up the table into smaller tables and joining, but that only makes things worse. The question is - is there anything I can do to set up the database, make sure I'm doing in the query, or change the MySQL settings to take advantage of my hardware? (Our Linux system has 7GB of RAM).

Aggregate Functions + ORDER BY
I want to find the sum of some rows, which I can do fine. Then, I want to order by that sum. Is this possible? I know ms access does it the following way, which I've read will work with mysql also:

SELECT nav_result_id, SUM(timingpoints) AS TimingPoints
FROM leg_results
GROUP BY nav_result_id
ORDER BY SUM(timingpoints)

However, I get this error message: "#HY000Invalid Use of Group Function"

Can anyone tell me why, and possibly give me a hint how to correct my syntax to prevent this, and make it work

Mysql 5.0 & ASP/ASP.Net Aggregate SUM Problem
I'm running into a brick wall when attempting to run querys in asp/asp.net with sum() being used.

The issue I'm having is any query containing sum() will no display and breaks the asp code in either asp/asp.net(vb) I am using 3.51 odbc driver dsn-less I have even attempted to cast the sum into a decimal still encountering the issue. Anyone have any ideas as to what 5.0 changed because previously I have no issues with Sum() in my asp/asp.net sql queries.

Adding Aggregate Functions With Columns
Does anyone know how to add the values returned from aggregate functions to the values in the columns.
I have to compute the average price of a product given from a list of products which have their own prices, and
display the products information with its price, the average price including that product, and the average price not including that product.
so far it got this :----:
select
(select avg(pprice) from vendor) , (select max(pprice) from vendor),
(select min(pprice) from vendor), avg(pprice) ,
vname
from vendor a
group by vname;

which calculates the average but then i got lost.....

Show Rows With Result 0 For Aggregate Functions
have a query like this:
SELECT C.idc, C.name, count(CM.idm)
from C, CM
where C.IDC=CM.IDC and CM.idci is null and C.type='class' GROUP BY C.IDC

The result table only contains the elements of table C where the count() is >0.

How can I obtain a result table that contains all elements of table C with their count (either 0 or >0) ?

4.1 - Update A Field In Table1 With Total Aggregate From Another Table
I want to update table1.field3 with the SUM() of table2.field4 where table1.id = table2.table1_id

So I need to agrregate table2.field4, get the sum where table1.id = table2.table1_id. then take that total and put it into table1.field3

Can you do that in one statement or a series of statements - all using DML?

Where Clause Issues When Selecting Between Dates With Aggregate Functions
I am working on a reporting tool that scrapes ebay feedback scores and enteres then into a database to be used later in various reports. One report I am trying to create will display how much a user's feedback score has increased over a specific period of time (e.g. last 30 days).

There are two tables being used in this query. One simply lists the the identities being tracked (identities) and the other records their feedback score (feedback). Each day a new row is created with each identity's current feedback score.

When I try to get the total increase in feedback score since we started recording data it works just fine:

SELECT i.name
, MAX(f.feedback) - MIN(f.feedback) AS calc_feedback
FROM identities AS i
LEFT JOIN feedback AS f ON i.id = f.identities_id
GROUP BY f.identities_id
ORDER BY calc_feedback DESC
, i.name ASC
But when I try to add a WHERE clause that specifies a specific time period to pull this data from it doesn't return any rows. It doesn't give me an error, it just doesn't return any rows. Here is the query with the WHERE clause I am using (I've tried various other one similar to this one).

SELECT i.name
, MAX(f.feedback) - MIN(f.feedback) AS calc_feedback
FROM identities AS i
LEFT JOIN feedback AS f ON i.id = f.identities_id
WHERE f.feedback BETWEEN NOW() AND NOW() - INTERVAL 2 DAY
GROUP BY f.identities_id
ORDER BY calc_feedback DESC
, i.name ASC

Conditional Statement In SQL ?
Is there way to have a sort of conditional statement in MySQL ?

I have an image library (images, galleries) and an image can belong to multiple galleries. So I have also a rel_image_gallery table. When I transfer a bunch of images from one gallery to another all at once, there is the possibility that an image is already in the destination gallery.
So I need to check something like "if image already in gallery, do nothing, otherwise add it". I can do that in PHP but if I could do it directly in SQL, that would be more efficient I think.

Conditional Union
My objective is to gather all the type groups for a mailing list. I also would like to add to the list an 'Undefined' item for all the users that are ungrouped:

1. I'm trying to make a conditional union, where a union will occur only if the previous condition is true (if any null typeIDs are found)
2. I would like to make one query only [to use with a php function wich only allows single queries]

So this is a 'sketch' of my query:

SELECT typeID, name FROM pa_users_broadcast_types
UNION
IF((SELECT COUNT(*) FROM pa_users_broadcast WHERE ISNULL(typeID))>0)
SELECT (Ɔ') typeID, ('Undefined') name;

Conditional Selection
I have a table that contains some words and their translation, in various languages. I would like to select the translations for a particular language but use the English word in case their are no translations.

I know how I could do it with PHP, but ideally I would like to do it in SQL only.

I'm not sure it's clear at all, so here's an example:

ID, translation, language
'BACKGROUND', 'Background', 'eng'
'INTRODUCTION', 'Introduction', 'eng'
'BACKGROUND', 'Contexte', 'fra'

I would like to retrieve the French translations whenever they exist. In this case, only "BACKGROUND" has been translated, so I would need "INTRODUCTION" in English.

The SELECT result should thus be:
'INTRODUCTION', 'Introduction', 'eng'
'BACKGROUND', 'Contexte', 'fra'

Help With Conditional Insert
I'm not sure how to structure this conditional insert statement using 1 statement. This is my first attempt at conditional statements, so I'm not exactly sure what I'm doing

I am wanting to insert a new row into `order` based on two criteria from `slides`:
1. UNIX_TIMESTAMP( slides.end_date ) > UNIX_TIMESTAMP()
2. slides.active = 'Yes'

My tables:

CREATE TABLE slides (
slide_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
active ENUM('Yes', 'No') DEFAULT 'Yes' NOT NULL
)
AUTO_INCREMENT=100
TYPE=InnoDB;

CREATE TABLE order (
order_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
slide_id INTEGER UNSIGNED NOT NULL,
listorder VARCHAR(2) NOT NULL,
FOREIGN KEY (slide_id) REFERENCES slides(slide_id) ON DELETE CASCADE
)
AUTO_INCREMENT=1
TYPE=InnoDB;
I tried crafting two statements I thought might work, but not getting anywhere:

Statement Attempt #1
This works, but if no slide_id is returned in the subquery, I can't insert a NULL into orders.slide_id, thus, I thought I needed a conditional statement.

INSERT INTO order( slide_id, listorder )
VALUES (
(
SELECT s.slide_id
FROM tst_slides s
WHERE s.slide_id = 130
AND UNIX_TIMESTAMP( s.end_date ) > UNIX_TIMESTAMP()
AND s.active = 'Yes'
),
8
);
Statement Attempt #2
Invalid Syntax

CASE
WHEN (130 IN
(
SELECT s.slide_id
FROM tst_slides s
WHERE s.slide_id = 130
AND UNIX_TIMESTAMP( s.end_date ) > UNIX_TIMESTAMP()
AND s.active = 'Yes'
)
)
THEN
INSERT INTO tst_order(slide_id, listorder)
VALUES (130, 8)
END;

Conditional Statments?
Before inserting a new product for a supplier I'm trying to do two checks:

1. If product already exists for current supplier (product.supplier_id) then update the existing product.
2. If product already exists with NULL supplier_id (i.e. product with no supplier) then update that product using current supplier.
3. Else insert new product.

Can I do that effiecently within mysql/php without running multiple mysql_query statments?

Conditional Joins
This is similar to a previous thread of mine: http://www.sitepoint.com/forums/showthread.php?t=375169

I have two fields in my user table, 'lastentryid', and 'lastprivateentryid'. What I need to do is join the entry table using that entryid. Unfortunately, the results are skewed if their last entry is private, because lastentryid won't contain it. I am running into this problem in a few places, and I'd really like to solve it without using subqueries. I might even accept better organization on the db level.

Here is my query as it is now

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = user.lastentryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC
Here is what I want... but this doesn't work.

SELECT
user.userid, user.options, user.displaygroupid, user.usergroupid, user.username,
ugroup.opentag as opentaga, ugroup.closetag as closetaga,
dgroup.opentag as opentagb, dgroup.closetag as closetagb,
entry.title, entry.dateline as entrydateline,
if (
user.lastentryid > user.lastprivateentryid,
user.lastentryid,
user.lastprivateentryid
) as last_entryid
FROM vb_user as user
LEFT JOIN vb_usergroup as ugroup ON(user.usergroupid = ugroup.usergroupid)
LEFT JOIN vb_usergroup as dgroup ON(user.displaygroupid = dgroup.usergroupid)
LEFT JOIN vb_blog_entry as entry on (entry.entryid = last_entryid)
WHERE entry.dateline > $cutoff
ORDER BY user.username ASC

Conditional Summation
Hey guys...this may seem trivial but I've been up way too long today and I just can't wrap my head around it. I need conditional summation performed based on a given value. For instance:

CODESELECT SUM(CASE WHEN tbl_a.aID = 1
THEN SUM( crazy math goes here )
ELSE WHEN tbl_a.aID = 2
THEN SUM( crazy math goes here )
ELSE 0
END) AS 'total', MONTHNAME( tbl_a.field2 )
FROM tbl_a

Using The LIKE Conditional Twice In A Query
I can't get my head around this query and i would be greatful for any help thrown my way.

Its part of my search feature on one of my sites. I have the variable $keyword which is a word or phrase the customer enters trying to find a product on the site. I need the query to search 2 fields that could contain the keyword (ie they could enter a product code or part of the products name/title).

The following query works:

Conditional Join
I have made a basic left join statement say "Select t.*, p.usenet_id
from usenet_group_1 u left join user_table t on u.id = t.usenet_id;
but only want the recordset to return values from user_table if field
in user_table have a certain value ( if usenet_group = 5 ) - can this
be done within the above sql statement or do i have to make 2 seperate
statements?

Conditional ORDER BY
I created the following table:

CREATE TABLE `test_projects` (
`name` VARCHAR( 30 ) NOT NULL ,
`startdate` DATE NOT NULL ,
`enddate` DATE NOT NULL ,
`complete` BOOL DEFAULT 'false' NOT NULL
) TYPE = MYISAM

How can I retrieve items sorted by complete, then startdate if not complete, and enddate if complete is true?

Conditional Trigger
i have a field called "status" in a mysql table containing orders. This field records the status of an order. is it possible to create a condition (a trigger of some kind) for this field such that whenever its value changes it records this change to another field.

Inner Join Conditional
So I have two tables set up in a normalized fashion. They are users (user_id, last_name, first_name) and orgs (org_id, name, comments, assigned_to). The assigned_to field of orgs "points" to the user_id field of users.

What I am wanting to do is simple enough. When I query the organization I do an inner join to grab the org's contact info

SELECT org_id, name, comments, last_name, first_name FROM orgs, users WHERE assigned_to=user_id ORDER BY name ASC

However, not all orgs have a user assigned to them, so the assigned_to field of those orgs is left as NULL. The above query will only pull orgs that have users assigned to them, but I also want to pull orgs that do not have users. Here's what I tried

SELECT org_id, name, comments, last_name, first_name FROM orgs, users WHERE assigned_to=user_id OR assigned_to=NULL ORDER BY name ASC

The server still only returns orgs with assigned_to that are not null. I could make two queries and do a merge sort algorithm on them to organize them back into alphabetical order, but I'd rather avoid that, and I think it's cheating anyway. How can I restructure my query to accomplish this?

Conditional Delete
I have the following statement to remove entries from a number of tables matching a common key:

DELETE items, group_user_role,groups FROM items AS i, group_user_role AS gur, groups AS g WHERE g.group_id=gur.group_id AND g.group_id=i.group_id AND g.group_id='5';

My problem is, there is not always an i.group_id value and when there isn't the condition isn't currently met. How do I make the statement work whether or not i.group_id values exist?

Conditional Revoke
Did this:

revoke all on D_name_01.* from trial_user_01;

and got this response:

ERROR 1141 at line 4: There is no such grant
defined for user 'trial_user_01' on host '%'

Is is possible to issue a conditional revoke statement
so that the revoke is:

performed only if applicable
or
NOT performed if not applicable

Conditional REPLACE
suppose that there is a text like following (in datasore table)
Quote: "cookiedomain";s:11:"devshed.com"

i can change it using following query

UPDATE datastore SET data = REPLACE(data,
'"cookiedomain";s:11:"devshed.com"',
'"cookiedomain";s:0:""')

but there are somethings i do not know between quotes so i want create a query like "if there are somethings between quotes" namely "in between quotes are blank"

Conditional Actions
I need to keep a running count of actions for particular dates, so I need to say 'if this date & value aren't in the table, insert them. if they are, increment them'. Is this possible to do in 1 query? I can't figure how.

Here's what I'm doing at the minute:
CODESELECT count(*) FROM tracking WHERE date = current_date() AND source = 'somesource' AND target = 'sometarget'

Conditional Insert
How to make this in one single query (if possible):
IF (SELECT COUNT(Id) AS mycount FROM mytable) <= 4
THEN INSERT INTO mytable SET "some data"

Example (photo albums):

I have an "Albums" table and If an user has 4 albums already, he can't add a new one.

I don't want to execute 2 different SQLs using php because It will slow don the execution time I guess.

Conditional Insert
I need to do in SQL the following psuedo-code, and I'm stuck:

IF ((COUNT(*) FROM a WHERE column_a=1)>0)
THEN
INSERT INTO TABLE b
ENDIF

Conditional Selection
I'm trying to select all rows where if the field isChargable is Yes then the field charged must equal Yes to be selected. At the moment I'm trying:
PHP Code:
$query = 'SELECT * FROM calls WHERE status="closed"
AND (CASE isChargable IS "Yes" THEN charged = "Yes" END)
ORDER BY "opened" ASC';

But this doesn't work. If anyone could help me out with this I'd be very grateful, as I haven't managed to find anything relating to my problem online so far.

Conditional Insert
How to make this in one single query (if possible):

IF (SELECT COUNT(Id) AS mycount FROM mytable) <= 4
THEN INSERT INTO mytable SET "some data"

Example (photo albums):
I have an "Albums" table and If an user has 4 albums already, he can't add a new one.
I don't want to execute 2 different SQLs using php because It will slow don the execution time I guess

Conditional Update
Another baseball question: I have a table of the voting history for the Hall of Fame. This will give me a list of everyone inducted:

SELECT playerID, yearID, inducted FROM HallOfFame WHERE inducted = 'Y'

I have a Master file with records for each playerID where I have added the hofYear field. I want to update this field in "Master" with the yearID field from "HallOfFame" for the records selected above. How would I do that?

Conditional SELECT
I have a problem which I am not sure or it is even possible to fix with sql statements. So here it goes.
SITUATION
---
I have a table with 3 columns (PID, LANGUAGE, TEXT). PID+LANGUAGE is PRIMARY KEY. In this way I can have unlimited languages next to each other in one table with one correspending ID.

Depending on the language chosen, I want to retrieve the text. No so hard. SELECT TEXT FROM TABLE WHERE LANGUAGE = 'spanish'.
PROBLEM
---
NOT all languages are presented. So I want to fall back to English if Spanish (e.g.) is not presented in the table. Of course I could choose for SELECTING english AND the language I want, and determine with PHP or I have data. But I am talking about 10k+ rows, so I dont want to retrieve everything double. Just only when I really need to

Conditional Update Statement
I have a table with a field of ID numbers. I want to update this field and replace the ID numbers. For example I want every record that currently has the ID number 2,5,6,or 9 to be replaced with a 1. Every record with 1 or 4 to be replaced with 2 ...

I can't seem to form the update statement to do this. I can do them one at a time but this won't work because if I change 2,5,6, and 9 to 1 then when I run then update for 1 and 4 it will change them all. Does this make any sense?

I want some way to be able to use a condition...if id=2 or 4 or 6 set it to 1 and if id= 1 or 4 set it to 2.

My failed attempt:

mysql> update client_software set software_id=1
WHERE (software_id=2 or software_id=5 or software_id=6 or software_id=9)
AND software_id=2 WHERE (software_id=3 or software_id=7 or software_id=8)
AND software_id=3 WHERE (software_id=1 or software_id=4);

Conditional JOIN Statement
I need some help with an SQL statement. Here is the situation:

tblvendors - holds all information for clients
tbljobtickets - holds all job tickets for vendor jobs

these jobs have a status - open or closed

This statement successfully pulls a recordset of all the vendor information I need joining the jobticket table to the vendors table so I can get a count on the number of open jobs each vendor has, but I want to throw another conditional in the mix only to display jobs that are open (open status = 1) ...

Conditional Column Selections
I believe there is a way to make a conditional column selection on the SELECT statement but I am having difficultly finding succinct syntax.

Here is what I want to do:

I have 3 columns
FIRSTNAME
NICKNAME
LASTNAME

FIRSTNAME and LASTNAME are populated on every record. NICKNAME contains data only on occasion.

How can I make my SELECT statement do this:

concat(NICKNAME," ",LASTNAME) OR concat(FIRSTNAME," ",LASTNAME) ONLY WHEN NO NICKNAME OR NICKNAME IS NULL

Result Of COUNT In Conditional
How can I conditionally test for the number of rows returned from a query for, say, use in a stored procedureâ€™s conditional expression?

For example, I want an INSERT to occur when

IF (SELECT COUNT(*) FROM myTable WHERE id=5) = 1

But Iâ€™ve been getting errors, and Iâ€™m guessing it has to do with the syntax of this first part of the conditional expression - how can I test the value returned by COUNT to conditionally execute some other statement?

Conditional SELECT Statement
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?

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)