MYSQL :: Result Of COUNT In Conditional

Count The Result Of Group_concat?
I learned group_concat from this forum, very useful command, but I am wondering if I can * COUNT the result of group_concat *

for example, the following example, I know this is a wrong clause, but I hope I can COUNT and then use the result to do some HAVING works. So is it possible to count the result of group_concat?

SELECT class, sex, COUNT(group_concat(name)) as num from school group by class, sex HAVING num > 20

Count A GROUP BY Result
I'm trying to count the result of a GROUP BY query without using the php mysql_num_rows() function. Is this even possible?

Retrive Result Of Count(*)
I am using Count(*) in my Select
Code:

String sqlStr = "Select count(*) from Bulletin where StartDate<=getDate() and getDate()<=EndDate";

ResultSet resultSet = executeSQL(sqlStr);

How do I get the the result of this select statement?

Wrong COUNT() Result With Joins?
I'm using this query:

SELECT DISTINCT COUNT(Programme.ProgrammeID) AS Count
FROM Programme
INNER JOIN ProgrammeCategoryLink ON Programme.ProgrammeID = ProgrammeCategoryLink.ProgrammeID
INNER JOIN ProgrammeCategory ON ProgrammeCategoryLink.ProgrammeCategoryID = ProgrammeCategory.ProgrammeCategoryID
WHERE Programme.Enabled = Ƈ'
Which I was expecting to return the total number of records (11) that had Enabled = 1, but it returns 21 (there aren't even that many records).

I've messed around with GROUPING etc but I just can't figure it out. (By the way, the joins need to be there even though im not using them in this instance, as sometimes filtering is on ProgrammeCategory.ProgrammeCategoryID too).

Non-Numeric Result From SELECT COUNT(*)
I'm using MySQL 4.1.11 and MyODBC 3.51.11.
I've a problem with SELECT COUNT(*) query.

I'm using mysql in ASP. I'm executing this query for example:

Select Count(*) as TotalMembers From Members; //with recordset("Totalmembers")
-- or --
Select Count(*) From Members; // with recordset(0)

but both of these queries aren't giving Numeric (Integer) results...
I'm cotrolling them with IsNumeric function by ASP. But it gives False result.

Also i cannot using mathematical operators such like +-*/ . Because result isn't numeric. And an error occurs.

If you know asp, could you try to use math operators with select count(*) result?

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Distinct List Of Parents With A Child Count Result
Parent (id, name)
Child (id, parent_id, session_name)

I'm trying to write a query that will have one output row per Parent. Each row will show:
parent.id, parent.name, count of children for that parent AS children_count

Some parents have no children. Those rows could return null or 0 for the children_count field.

I know multiple methods external to SQL to solve the overall problem such as returning the join and counting where child.ids (for the same parent) is not null.

==>> but is there a way to do it in one SQL query that returns just the info I need?

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

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

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)

Aggregate/sum With Distinct Conditional
consider the following table:

col1, col2, col3
1, 1.25, 1
1, 1.25, 2
1, 1.25, 2
2, 0.75, 1
2, 0.75, 1
2, 0.75, 1
3, 1.25, 1
3, 1.25, 1
3, 1.25, 1
3, 1.25, 2

from this "theoretical" SQL statement

SELECT col3, IF(DISTINCT(col1),SUM(col2),SUM(0)) As mySum FROM table1 GROUP BY col3

or perhaps this one (neither actually works)

SELECT col3, SUM(IF(DISTINCT(col1), col2, 0)) As mySum FROM table1 GROUP BY col3

result would be...

col3, mySum
1, 3.25
2, 2.50

Conditional Search Fields
i'm having the user input certain search fields from a typical html form .. passing them in with $_POST (<--php stuff) values .. and then i'm stuck at writing my query ...

what i have now is..
    $query = "SELECT * FROM networks WHERE name='$name'
                                     and latitude='$latitude'
                                     and longitude='$longitude'
                                     and elevation='$elevation'";

when i input the name to search for it doesn't return any answers becasue the other fields are null from the input field .
thanks much in advance :) If programming were as fun as cycling, then we'd need more bike mechanics.

What Is The Best Way To Select By Conditional Data
I have the following structure:

dater1_id
dater2_id
dater1_rating_overall
dater2_rating_overall

Now, i need to select from the table the values where dater1 or dater2_id equals a certain id and check that the dater1 or dater2 rating (depending on weather the dater1 or dater2 id was matched) is higher than certain number. Maybe I set up my table structure wrong? Making 2 entries for each rating of one user to the othe seems highly ineffecient though.

Conditional Where - 1 Of 2 Options, Order Maters
Very simliar to my Conditional Join thread, I would like to combine two queries that have different WHERE clauses. Basically when looking for an item, it could be in one of two formats: Vendor-Code-Color or Vendor-Color:

SELECT
s01_Products.id AS products_id
, s01_Products.active AS products_active

, s01_Attributes.id AS attributes_id

, s01_Options.code AS options_code
, s01_Options.prompt AS options_prompt
FROM
s01_Products

LEFT OUTER
JOIN
s01_Attributes
ON
s01_Products.id = s01_Attributes.product_id
LEFT OUTER
JOIN
s01_Options
ON
s01_Products.id = s01_Options.product_id
WHERE
s01_Products.code = 'VV-9303-BLK'
ORDER BY
s01_Options.code

SELECT
s01_Products.id AS products_id
, s01_Products.active AS products_active

, s01_Attributes.id AS attributes_id

, s01_Options.code AS options_code
, s01_Options.prompt AS options_prompt
FROM
s01_Products

LEFT OUTER
JOIN
s01_Attributes
ON
s01_Products.id = s01_Attributes.product_id
LEFT OUTER
JOIN
s01_Options
ON
s01_Products.id = s01_Options.product_id
WHERE
s01_Products.code = 'VV-9303'
ORDER BY
s01_Options.code
Is it worth re-writing this query? I only need to look for Vendor-Code if Vendor-Code-Color returns nothing. I don't think that I would gain MUCH by combining the queries because in theory the query would search the table (using an index), and if NULL, search again. I don't see any difference from using two seperate queries except maybe the resources used to send/receive the query?

Any ideas? Should I just leave it as is? If I start to add more variations in the WHERE clause would it start to make a noticeable difference in speed?

Conditional Selection Of Table Columns
I am searching for a mysql-command to make a selection on some table-columns only if a certain column exists otherwise the query should be on some alternative column.
e.g. I'd like to make a selection 'SELECT columnA, columnX FROM table' only if the column 'columnA' exists, otherwise make a selection on 'columnB' instead of 'columnA' (lets suppose column 'columnB' exists).

Is there a possibility to form such a conditional mysql-query. Please no replies suggesting a workaround with multiple queries.

SELECT Conditional... Matching NULL Rows
I am trying to use a SELECT query to retrieve records from a table that have a certain row set to NULL. However when I try a query like the one below... it is not returning any records, eventhough there are thousands of records that have NULL in the 'row1' row.

Example query:

PHP

$query = "SELECT * FROM table WHERE row1=NULL ORDER BY date DESC";

Conditional Join - 1 Of 3 Options, Order Maters
I'm confused and I'm not sure if this is even possible to do in one query -- I really want it to be, but it may be faster to throw some PHP in there (this is where I get proved wrong )

In an ideal query, if s01_MUS_DesignerLookup.id = s01_Products.designer_id IS NULL, then I'd JOIN ON s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4). If that returns no results, then hopefully s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 2) would. There is still the possibility that it would return no results, but I have a static variable that will handle that rare occasion.

The query I was hoping would work (but isn't even correct syntax) is:

SELECT
s01_Products.code AS Products_code
, s01_Products.designer_id AS Products_designer_id
, s01_MUS_DesignerLookup.name AS MUS_DesignerLookup_name
FROM
s01_Products
LEFT JOIN
s01_MUS_DesignerLookup
ON
CASE WHEN
ISNULL(s01_Products.designer_id) || s01_Products.designer_id = 0
THEN
CASEWHEN
ISNULL(SELECT s01_MUS_DesignerLookup.name
FROM s01_MUS_DesignerLookup
WHERE s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4)
)
THEN
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 2)
ELSE
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4)
END
ELSE
s01_MUS_DesignerLookup.id = s01_Products.designer_id
END
WHERE
LEFT(s01_Products.code,2) = 'UO'
I tried something like:

SELECT
s01_Products.code AS Products_code
, s01_Products.designer_id AS Products_designer_id
, s01_MUS_DesignerLookup.name AS MUS_DesignerLookup_name
FROM
s01_Products
LEFT JOIN
s01_MUS_DesignerLookup
ON
CASE WHEN
ISNULL(s01_Products.designer_id) || s01_Products.designer_id = 0
THEN
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 4)
OR
s01_MUS_DesignerLookup.code = LEFT(s01_Products.code, 2)
ELSE
s01_MUS_DesignerLookup.id = s01_Products.designer_id
END
WHERE
LEFT(s01_Products.code,2) = 'UO'
ORDER BY
LENGTH(s01_MUS_DesignerLookup.code) DESC
But it was pretty dirty and I wasn't getting true results anyways.

Can what I'm after be done? Can I check the results of a subquery inside a query?

Real-time Automatic Conditional Data Manipulation
So it happens that I have to delve into the fields I feel very uncomfortable in ... for now. I'm working off the existing MySQL DB, in which I have to:

1. Analyse in real time (or nearly there) every newly entered row in the "entry" table (data is inputed by a third party) based on the set of criteria and depending on the outcome create new entries either in "bad" or "good" tables

2. Perform the same operation but on the different set of criteria on the new entry in the "good" table and create new record in the "output" table, which is then visible to the third party (but contains very different data from originally entered)

My original plan was to dive into writing triggers, but this feature does not seem to be supported in the platform of my choice. Are there any GUI tools (or anything that would not require PhD in quantum physics) that can help me to create conditional real-time updates between tables within one MySQL database triggered by appearing new record?

#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows.

I'm using my SQL version: 4.1.19

Any clue on why this isn't working?

INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` )
VALUES (
(
SELECT Project_Code, Grant_Code, FY, SUM( Capital )
FROM Grants_Temp
WHERE Project_Code = 'OSUT'
AND FY = '2006'
AND Claim_Month = '072006'
GROUP BY Grant_Code
ORDER BY Grant_Code
)
)

Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query.
Column count doesn't match value count at row 1

I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query.

The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:

Count(*) As Count And TotalCount Of Count
data in myTable1

(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4

data in myTable4

(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.

I have the following code.

code

(select left(time,2) as hour, count(*) as count
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the code above produces the following result.

result

(day) count
(10) 3
(10) 1
(11) 1
The following would-be code doesn't work correctly, but it will show what I want.

would-be code

(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the following is my target result.

target result

(day) count totalCount
(10) 3 5
(10) 1 5
(11) 1 5

Difference Between Count(*) And Count(1)
What is the difference between count(*) and count(1)?

Difference Of Count(*) From Count(1)
Is it true that count(1) is more efficient than count(*)? They say that count(*) still goes through all the records without needing to.

How To Fix "Column Count Doesn't Match Value Count At Row 1" ?
I've been trying to install a portal to my installation of Invision Power Board, and I keep coming up with this problem. I post here for two reasons: firstly is that no one is responding over at the portal creator's thread and I need help, second is that this error looks like a mySQL error that could be solved without the need of the creator if possible.

Here is the error returned:

mySQL query error: INSERT INTO `ibf_portal_box` VALUES (1, 'lang.last_topics', '<box><menu>10,20,30,50</menu>
<topics_show>10</topics_show>
<order>last_post</order>
<exforums></exforums>
</box>', 0, 'middle', '*', 0, 4, 'last_topics', '1')

mySQL error: Column count doesn't match value count at row 1
mySQL error code:
Date: Tuesday 21st of December 2004 08:43:10 PM

I am very new to mySQL and don't really understand it. What is wrong with the above that it is trying to insert, and how is it fixed?