MYSQL :: Subselect In 4.0.12-max With -- New Option

Getting Rid Of Subselect
I have a table, which -- simplified -- looks like this:

create table access_logs (
session_id varchar(32),
request_uri varchar(32)
);
Each pageview logs the users session-id + the request-uri. Now, to determine how many visitors followed a specific path, I need to select the number of sessions, which have a row including specific request_uri.

This is my own feeble attempt, but I have a feeling that this could be rewritten to get rid of the subselects:

select session_id
from access_logs
where session_id in (select session_id
from access_logs
where request_uri = "landing-page.php")
and session_id in (select session_id
from access_logs
where request_uri = "exit-page.php")
group by session_id;

Getting Around Subselect
My knowledge of SQL is basic so I need some help developing a query. Suppose we have a table called BID where each row is a bid on an item up for auction. The relevant columns are BID_ID which is the primary key, ITEM_ID which identifies the item, and a BID_DATE which records the datetime of the bid.

I would like to find the most recent bid for each distinct ITEM_ID in the table. I've worked out the query below which seems to do the job. However, I need to find a query that will work on a pre-4.1 server which does not support subqueries. Is there a way to re-state this query without using a subselect? Perhaps using some kind of join?

SELECT *
FROM BID, (SELECT ITEM_ID AS IID, max(BID_DATE) as MAXDATE
FROM BID
GROUP BY ITEM_ID) as MAXDATES
WHERE
(ITEM_ID=IID) and (BID_DATE=MAXDATE);

Subselect
I had some SQL calls which worked fine on a v4.1 server and now I've moved to another one which is 4.0.24 and certain subselects no longer work. Is there any basic way to convert statements such as this:

SELECT a.name, (SELECT COUNT(*) FROM table2 AS b WHERE b.id=a.id) as count FROM table1 AS a So that it conforms to the 4.0.x standard?

Subselect
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:

locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list. Code:

Subselect
Let's say i got this query:
select user.id, (select count(*) as posts_number from posts where posts.user_id = user.id), and some other fields, and a lot of joins here.Is it any way to *say* to mysql that the current user.id selected, is the one in the subselect ?
(the one from select "user.id" and the one in where ... = "user.id")

Subselect
Unless I'm wrong, here's a way to do a subquery (inner join two tables, then inner join the resulting table with a third table). It takes advantage of the two different ways of expressing an inner join ("INNER JOIN", and "t1, t2 WHERE...") to express two separate inner joins within a single statement.
SELECT p.p_id, v2.v_name FROM t_project p, t_volunteer v INNER JOIN t_volunteer v2 ON p.p_id=v2.p_id WHERE p.p_id=v.p_id AND v.v_name LIKE "%mike%";
Is this a technique that people use often? I couldn't see it documented in my SQL book ("MySQL", by Paul DuBois), even though it seems like a useful technique for what is effectively a subselect.

Subselect
I have been held up long enough on the query time to ask for help. Its basically a subselect that never returns.

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id IN ( SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC ) ORDER BY it.org_id ASC

If I break it up into 2 seperate

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id = 09238323 ORDER BY it.org_id ASC

SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC

They both return expected values.

Subselect / AS
SELECT
a.id
(SELECT width, height, filename FROM photos WHERE user_id = a.id LIMIT 0,1) AS (width, height, filename)
FROM users a

ORDER BY a.datestamp DESC

you can see my example, using with AS (example).
How can i extract values from subselects?

Slow Subselect
I've got two tables:
lo_users: nickname|id|...
lo_friends: from|to|...

The following query takes < 0.01 sec:
SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1'

...but if I use it in a subselect, the whole thing takes about 0.54 sec:
SELECT u.nickname FROM (SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1') f LEFT JOIN lo_users u ON u.id = f.userid

What can I do to make the query faster? "from" and "to" are indexed and lo_users.id is the primary key.

Delete Subselect
I know that MySQL 3.23.nnn did not support a delete subselect, just wondering
what the best/most efficient way to do the following is:

delete from table_a where table_a.column_1 in ( select column_1 from table_b);

Assuming that column_1 is the same data type and size in both table_a and table_b.

Update Self Subselect
I have a log table that creates a row for every page view. I have a field called "flagged" which defaults to 0. I'm trying to update the flagged field to 1 when the ip count is greater then 30... This is what I have but I get the error "You can't specify target table 'ip_log' for update in FROM clause".

UPDATE `ip_log` SET `flagged` = 1
WHERE `ip` IN(
SELECT `ip`
FROM `ip_log`
GROUP BY `ip` HAVING COUNT(*) > 30
)

Subselect Wierdness
I am trying to get 3 active article IDs from the table ARTICLES for a random active feed from table FEEDS. Here is the query I have:

CODEselect AID, a.fid as FID from ARTICLES a where active='Y' and a.fid = (SELECT f.fid FROM FEEDS f where active='Y' ORDER BY RAND() desc limit 1) limit 3;

Subselect With NULL
why don't i get some results for the second query?

mysql> select * from a;
+---+
| b |
+---+
| a |
| b |
+---+

mysql> select * from a where b not in (select NULL from dual);
Empty set (0.00 sec).

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list.

Get Last Records Details With A Subselect?
I have a ticketsystem where each ticket belongs to an user and each user can insert a couple of messages to one ticket. Therefore I have implemented a date field (used as primary key). Now I want to get details from the last entry belongs to a ticketid.

kdn_message:
updated (date)
ticketid (int)
kdnr (int)
detail
state

select * from kdn_message t
where updated in (select max(updated) as updated from kdn_message group by ticketid where ticketid=t.ticketid order by updated desc)

what is wrong i this statement?

Convert A Subselect To Inner Join
I was developing a php/postuke app for a client and I wrote two of my SQL queries with subselects. I found out after I was done that they were pretty much stuck with MySQL 4.0.x for awhile, so I need to revamp my queries to avoid subselects. The query uses three tables:

nuke_gwbt_guild_halls
nuke_gwbt_guild_halls_notes
nuke_gwbt_matches

I am getting all of the fields in the first table, matching the notes id from the second table to a notes id in the first table, and then counting some metrics from the third table to return as fields in the resulting recordset, used for ORDER BY sorting. Here is the working subselect query: Code:

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table d=
oesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo=
cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER B=
Y l.location ASC

so when shotlistID is selected all the keys from the locations_join joined =
to the shotlistID would be remove from the locations list please help, i'm =
trying to do this in one query saving from getting all the keys into an arr=
ay then checking if the values arent in the array when generating the list.

Subselect/left Join
I have a table like this

| ID | THING | NUMBER |
---------------------------------------------------------------
| 1 | white | 1 |
| 2 | white | 2 |
| 3 | green | 1 |
| 4 | green | 3 |
| 5 | brown | 1 |
| 6 | brown | 4 |

and I want to get just white back if I know two numbers are 1 and 2 or green back if I know the nubmers are 1 and 3.

Its mysql 4.1 so I am allowed subselects or left joins. I am drawing a blank!?

Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table:

CREATE TABLE b5_assignment_lookup (
as_id int(11) NOT NULL auto_increment,
as_blog int(11) NOT NULL default Ɔ',
as_blogger int(11) NOT NULL default Ɔ',
as_milestone enum('start','finish') NOT NULL default 'start',
as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`as_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ;
By running this query, I get the following resultset:

mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= &#55614;&#57158;-09-30'
+--------+----------+-------------+---------------+---------------------+
| as_id | as_blog | as_blogger | as_milestone | as_timestamp |
+--------+----------+-------------+---------------+---------------------+
| 87 | 89 | 41 | start | 2006-05-01 00:00:00 |
|208 | 89 | 41 | finish| 2006-09-02 11:55:27 |
|209 | 89 | 103 | start | 2006-09-02 11:55:27 |
+--------+----------+-------------+---------------+---------------------+
3 rows in set (0.00 sec)
What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41.

What I really need to grok is all bloggers who blogged all or a part of a range of dates.

For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02.

I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25.

The flags are the start/finish, obviously.

Transform SubSelect In OUTER JOIN
maybe I'm simply to dump but I could not transform this SQL-Statment
which uses a Sub-select and create on that uses an OUTER JOIN ....

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

OPTION Value
Here's the connection string I use commonly with my VFP9 apps connecting to a MySQL5 backend:

DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;UID=mike;PWD=foxrocks;DATABASE=mydb;option=131609

I was wondering what other values for the OPTION parameter folks use? Sometimes, I get some flakiness, so I was searching for a consensus on "best option value to use."

Binary Log Off Option ?
I am using mysql version 4.0.22.
Which file to access and modify If i want to disable binary logs which are created in mysqlvar ?

Sql Option ALLOW_INVALID_DATES
how to implelent 'ALLOW_INVALID_DATES' option for sql mode?
I was able to run

SET GLOBAL sql_mode='ANSI';

but can't get the syntax of ALLOW_INVALID_DATES ...

Can't Set The Table Option
I've use the command CREATE TABLE as follow:

CREATE TABLE test(myid varchar(20)) DEFAULT CHARSET=latin1

and

CREATE TABLE test(myid varchar(20)) DEFAULT CHARACTER SET latin1

I've got an error message with the 2 commands above saying that I've used the wrong syntax near the "DEFAULT CHARSET=latin1" clause. I've read the MySQL Document and figured out those commands have the right syntax, not wrong.

Could anyone please tell me how to create a table with DEFAULT CHARSET=latin1.
( I have use MySQl ver 4.0 ).

Unknown Option -- Log-bin
Trying to use the mysql command-line client with a MySQL 5.0.21 database on Windows 2003 Server, and am getting the error message:

mysql: unknown option '--log-bin'

What does this mean and how can I get my command-line client back?

--force Option
I'm using a batch file to insert data into my database.
I read in the manual that using the --force option makes mysql to continue processing the batch file, although there was an error like "table does not exist" or something like that.

However I noticed that mysql still quits if it tries to insert data in a table that not exists.

I'm using mysql 5.0.19 under a windows xp system

the command i'm executing is as follows:

mysql -u root --force databasename < "path/to/batch.sql"

MySQL Replacement Option
I want to replace a character "/" with &frasl; in my database, is there any option to do that.

Local-infile Option
I´m using mysql 3.23.58 and I want to enable the local-infile option.
The manual on dev.mysql.com says the following to that:

.....
If you use LOAD DATA LOCAL in Perl scripts or other programs that read
the [client] group from option files, you can add the local-infile=1
option to that group. However, to keep this from causing problems for
programs that do not understand local-infile, specify it using the
loose- prefix:

[client]
loose-local-infile=1
......

But when I do that the I can restart the mysql-Server but starting of
mysqladmin says the following:

Unrecognized option local-infile

The same message with loose-local-infile=1.

When I start the server with --local-infile=1 and then starting the
mysql-Client with --local-infile=1 on the shell everything is OK and I
can use the LOAD DATA LOCAL Syntax.

What is wrong? Why it is an unregognized command? I need the LOAD DATA
LOCAL because I want to use mysql with Perl.

Disable -- User Option
I would like to use unix user names to login to mysql. If I create a mysql user xxadmin and leave the password empty other unix users can use the xxadmin with the --user option . How can I make sure that only the unix user xxadmin can login as mysql user xxadmin ?

Error =SET OPTION SQL_BIG_SELECTS=1
I add a few more test records to my db.... the result the following error message...

Vbscript Runtime error 'ASP 0185 : 3219'

General error: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok

Well to the best of my knowledge my WHERE could not be more simple and all the tables concerned don't have that many records.

I've scanned the net but cannot find an obvious answer. Any clues... if I need to use "SET OPTION... how do I do this within ASP. Code:

Browse Option Not Shown?
However, some fields won't let me browse what are in the fields. I have full admin access tot the website. If any1 doesn't know what im talking about, I could post a screenshot, if nessessary.

Mysql --force Option
I'm using a batch file to insert data into my database.
I read in the manual that using the --force option makes mysql to continue processing the batch file, although there was an error like "table does not exist" or something like that.

However I noticed that mysql still quits if it tries to insert data in a table that not exists.

I'm using mysql 5.0.19 under a windows xp system

the command i'm executing is as follows:

mysql -u root --force databasename < "path/to/batch.sql"
Xema

Joining User Option Lists
I have a niggly problem that I can't quite work out which I'm sure has a simple answer (have to be missing something).

I have a table of 7 categories, each with a unique ID and name.
A seperate table is a category/user mapping table which maps users to a category.

For example, if a user is a a member of 4/7 categories, they have 4 rows in the mapping table, each one with their userID/catID in it.

What I'm attempting to do is select a list of ALL categories and join (where applicable) the category map so I can present all options regardless of whether the user is a member of a particular category or not (but I do need to know if they are or not).

PHP

SELECT *
FROM categories
LEFT JOIN category_map ON categories.id = category_map.c_id
WHERE category_map.u_id=10

I had some success with adding OR category_map.u_id IS NULL but returns odd results when there are 0 rows present for a user in the category_map table.

Printing A Result After An Option Has Been Submitted
My goal is this.- To print another field value 'scriptcode', of the same row after an option 'scriptnaam' is submitted.

The first part isn't the problem. I do get all the data 'scriptnaam' in a select field.
But to distillate, a result 'scriptcode' out of it ???

If someone could help me a bit further with that, that would be great.
I like to mention, that I've just started with PHP/MYSQL, so my knowledge about it is limited. Code:

Mysql: Unknown Option '--ndbcluster'
Running into some issues while configuring my cluster....

I'm setting up a cluster with two storage nodes and one management node...
The problem I'm having is that when I try to open MySQL on one of the storage nodes I get this error...

mysql: unknown option '--ndbcluster'

but i do not get this error on the other storage node and i am able to access MySQL on it.

The my.cnf file looks like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysql_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#Options for MySQLD process
[MYSQL]
ndbcluster #run ndb engine
ndb-connectstring=x.x.x.x #location of MGM node

#Options for ndbd process
[MYSQL_CLUSTER]
ndb-connectstring=x.x.x.x #location of MGM node

If anyone can let me know what the problem is it would be greatly appreciated...
I'm running Fedora Core 4 and MySQL 5.0.19 MAX

MySQL Performance - Option Setting
I set OPTION=3 in my connection string as recommended by MySQL
(http://dev.mysql.com/doc/refman/5.0...parameters.html)
for a VB application.

OPTION=3 is Don't Optimised Column Width (1) and Return Matching Rows (2).

I found this to be very slow on a database with just 27,000 records. It took
approx. 12 seconds to read 27,000 records.

When I change the OPTION to 8 (Allow Big Results), it took just 3 seconds to
read 27,000 records. BUT now my application cannot update records anymore
and getting random errors on transactions.

I try setting OPTION=11 (all 3 options added together) and the speed went
back to being slow.

Slave-load-tmpdir Option
I have just installed mysql server 5.0. However, when I was trying to start the server by invoking mysqld, it complained that a directory/file is not found.

I then viewed the variable names and found that the slave-load-tmpdir variable was pointing to a non-existing path. I have tried

mysqld --slave-load-tmpdir=<my new path> --standalone

but whenever I restart the mysqld, the variable is still defaulted to that old path....what should I do to change the default setting for this variable?

Check Table Command - When To Use Which Checking Option
I'm trying to decide which check table option to use for a DB-health-check script for my product. This script is to be run periodically to find and repair damaged tables. My product uses only MySQL, i.e. it does not interact with another DB. I need to find the check table option that would find damaged tables with the highest possible performance.

The 13.5.2.3 CHECK TABLE Syntax documentation states the following:
MEDIUM Scan rows to verify that deleted links are okay. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys.

What are they referring to with the word "links"? Are they talking about links to MS Access tables? How do I know if my tables have links? Does the MEDIUM option scan rows only to check deleted links? If so, if I don' t have "deleted links", should I not use this option? By running in MEDIUM mode, will the tables be locked while "check table" is working?

For the other options - CHANGED, FAST, QUICK - will "check table" lock the tables? What access do I have to the tables while check table is running?

Symbolic Links With Windows XP Or Option Datadir
How do you make symbolic links work ?

Drop Down Menu: Option Equal To All Others In The List
I would like to create a drop down menu in a form that selects distinct arrays from a columname, inserts them into a drop down menu, but then I also want one of the options to be equal to all of the rest. I would end up with a drop down structured like this:

All colors
Red
Blue
Green
Purple

Root Login Does Not Work Correctly Without -h Option
For example, I would expect to be able to sign in by using.

mysql -u root -p

The above gives me accessed denied.

Now, I need to do.

mysql -h <hostname> -u root -p

It seems like a simple problem that I have not been able to solve by searching here and google.

Solaris 8 Daemon Fails Because Unknown Option?
I guess people did not see my post from the install thread... I am really really stuck now! Does anyone work on Solaris???

Whatever option I use to start the daemon I get this:

050310 15:27:35 mysqld started
050310 15:27:35 [ERROR] /opt/mysql/bin/mysqld: unknown option '--~'

Please Help

Note that I am using the binaries from this site.

Show All Entries, Then Give An Edit Or Delete Option
I want to show all the entries that are in a database. below each of them I want a radio button that when checked will either allow them to edit or delete the entry. How might I do this?

Enable The Option Flag Return Matching Rows
I would like to know if it's possible to enable the "Return matching rows" flag with a connection string. With a dsn connection i already know how can i do this.

Binding Option Button Controls On Unbound Forms
I am using an MS Access 2002 as a Frontend to a MYSQL 5.0.18 database server backend. I am attempting to set up unbound forms using a dsn-less ADO connection. I have been able to bind the textbox and combobox controls to the fields within my form however I am having difficulties binding boolean field types to my option button controls.

1) Has anyone had any success in binding option button controls to MySQL boolean fields?

2) On another point, in the unbound forms do you set up your own navigation controls or are you able to utilize the standard form navigation controls provided by Access.Again it seems the option button controls do not stay synchronized to the current record.

3) Also after creating a recordset and binding it to my form and binding the controls, I close the recordset and the connection object.After making changes to a current record, if I navigate to the next record in the recordset any changes I made in the previous record are automatcially reflected in the MYSQL tables. The user is not prompted to save changes; it does it automatically.I'm new to using ADO and was surprised to see that this was possible since the connection object had been closed.How do you go about preventing the direct writing to MySQL without informing the user that they are doing so?Where is the code for the navigation buttons events?Is it possible to 'hook' into them?

Best Way To Deal With Linking Tables And Foreign Keys When InnoDB Is Not An Option.
Title covers most of it.

On database insertion I suffer from a lack of transactions, which means I can enter the data into the primary database, then have the database fail with exactingly bad timing and fail when I am making my entries in other tables or the linking table.

On data deletion I suffer from a lack of foreign key constraints so again the database can fail after I delete my entry from my main table, but before I enter the data for my linking tables.

My biggest conern is that I end up with data in the linking tables but not in the main table. Then if I tried to re-enter that main entry with those linking table entries I would get an error due to duplication of the primary key (concacted id of the two foreign keys) in my linking table.

I am considering elaborate if loops in my php to try and catch errors and delete the whole entry if the errors occur. The problem being that the errors will occur when the database is misbehaving so my odds that the delete will work might be somewhat low.

Has anyone else had to work withou InnoDB much in scenarios like this?