MYSQL :: Having Trouble With Subqueries With Mysql 4.0.21

Subqueries In MySQL 4.0?
I've got the following query. I just found out that subqueries are not supported by MySQL 4.0...

How would I replicate this to work with MySQL 4.0?

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblEntries)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblEntries) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblEntries)) As A ON
tblCategory.CategoryID = A.Cat;

Table tblCategory
CategoryID = index key
Category = Text entry describing the Category

Table tblEntries
Category = key link to tblCategory
Category2 = key link to tblCategory
Category3 = key link to tblCategory

Basically, I want a list of Categories that are used in tblEntries.

View Replies !

Subqueries To Mysql 3.x.x
I know that mysql 3.x.x does not support subqueries so i need your help with a query..

I need to make possible to search concurrently both fields. The user can insert only part of the word that is searching so i used the like '% keyword %'. in musql queries

Now my tables are
Table Titles:
Title (mediumtext)
AuID (integer)

Table Authors:
Author (mediumtext)
Au_ID (integer)

If i had support for subqueries then i would have done it easily

select Title from Titles where Title LIKE '%user title entry%' and AuID= (select AuID from Authors where Author LIKE '%User author entry%')

Now i don't have support for subqueries i need help for writing this query.

View Replies !

Does MySQL (4.0.20-standard) Allow Subqueries
Im moving a development app (MySQL 5.0) to a different server which
runs MySQL 4.0.20-standard. I am getting errors on queries that have
subqueries such as...

SELECT id
FROM table1
WHERE id IN (
SELECT id FROM table1
)

Seems like such a simple query but it wont run? Anybody know the
answer and what other limitations im going to experience going from
version 5.0 down to 4.0.20-standard.

View Replies !

Does MySQL (4.0.20-standard) Allow Subqueries???
Im moving a development app (MySQL 5.0) to a different server which runs MySQL 4.0.20-standard. I am getting errors on queries that have subqueries such as...

SELECT id
FROM table1
WHERE id IN (
SELECT id FROM table1
)

Seems like such a simple query but it wont run? Anybody know the answer and what other limitations im going to experience going from version 5.0 down to 4.0.20-standard.

View Replies !

Subqueries And DISTINCT From MySQL 5 To 4
Ugh... been working on a project for some time now and I'm just plain stuck and the SQL is getting over my head. I would LOVE any help you can give me. The following query works beautifully fast on my in house machine (mySQL 5) but when I brought it over to the live server (mySQL 4.1) the query takes about 30 seconds to compile... I realize I am not a SQL rock god...

SELECT DISTINCT p.pailid AS PID, p . * , (

SELECT COUNT( * ) AS c
FROM feed
WHERE pailid = PID
AND display =1
) AS numFeeds, (

SELECT COUNT( * ) AS c
FROM comments
WHERE pailid = PID
) AS numComments, c.title AS cattitle, (

SELECT COUNT( * ) AS q
FROM flags
WHERE pailid = PID
AND userid =1
) AS isFlagged
FROM pail p, feed f, category c
WHERE p.pailid = f.pailid
AND f.display =1
AND p.categoryid = c.categoryid
ORDER BY p.date DESC
LIMIT 0 , 50
Should I convert this to JOINS? If so, could someone give me a hand as I am LOST when it comes to JOINS.

View Replies !

Mysql Subqueries / Multiple Selects?
In MSSQL I can use subqueries to return a bunch of independant data with a single query. I am wondering if mysql has the same ability. I'm using > mysql 4.1, but I haven't seen any examples of this being used.

View Replies !

MySQL 4.1.7 On OS X 10.3.6 Install Trouble
I am trying to get MySQL, PHP 5, and OS Commerce installed on OS 10.3.6 but am running into a little trouble with MySQL. Here is the output that I get when trying to start MySQL after installing from the OS X PKG file.

Last login: Sun Dec 5 21:47:38 on console
Welcome to Darwin!
Rhapsody:~ jeffery$ cd /usr/local/mysql
Rhapsody:/usr/local/mysql jeffery$ sudo ./bin/mysqld_safe
Password:
Starting mysqld daemon with databases from /usr/local/mysql/data
STOPPING server from pid file /usr/local/mysql/data/Rhapsody.local.pid
041206 09:48:13 mysqld ended

I have searched the internet and all around the forums for some help, but have not found anything yet, could someone let me know whats up?. Or possibly point me in the right direction?

View Replies !

Mysql Import Trouble
I'm using the following code to import a .sql file.

mysqlimport --local -u root -pCENSORED teraspot_db /home/default/teraspot_db.sql
and I get this error

mysqlimport: Error: Table 'teraspot.teraspot_db' doesn't exist, when using table: teraspot_db
I got the .sql file from exporting the database in phpmyadmin on the old server.

What am I doing wrong?

View Replies !

Trouble Installing MySQL On Mac OS 10.4
I'm working on a Mac under OS 10.4
In the hopes of making my website interactive I've been trying to use Kevin Yank's book "Build Your Own Database Driven Website Using PHP and MySQL" to learn some MySQL and some PHP. (At the moment I know only a tiny bit about PHP and nothing about MySQL.)

Despite my determination to go about this in a calm and orderly way, I've run into a frustrating obstacle right at the beginning. To make matters worse, I'm not even sure what the problem is, which makes it difficult to ask for help. I don't know whether MySQL is actually installed and I just don't know how to use it or whether, despite my efforts, it's not really installed at all. I tried to install it according to Kevin's instructions and thought it had been installed properly (I mounted the dmg and a "startup package appeared in my Library, but when I tried to proceed farther by typing commands into a Terminal window according to Kevin's instructions, I just got a "command not found" response.) I read what Apple has to say about installing MySQL under OS 10.4 and tried again, but when, following their instructions to make sure no MySQL processes were already running before installing I entered "mysqlshutdown admin" once again I got a "command not found".

One other thing: I think the problem (or part of the problem) might be that I'm not in the right directory when I type in these commands. I get the impression MySQL stuff is supposed to be happening in the "root" directory, but when I type in "cd root" in the Terminal window I just get another "command not found." (Before I started to work on this yesterday I'd never used a Unix terminal window!)

I'm a bit embarrassed and hesitant about this post â€” my first on this forumâ€” because I'm afraid it's going to sound very ignorant and confused. But you do say beginners are welcome so....

View Replies !

Emulating Subqueries With Temp Tables In MySQL 3.23.55
I just found out that the server I'm writing some code for only has MySQL 3.23.55 which unfortunately doesn't support subqueries (and dumb me I spent so much time trying to make them work thinking it was a syntax problem).

So, I gotta find another way around.

I think temporary tables are the way to go, but I can't find enough examples to put beside the manual to full understand them. Any pointer to some resources
about this?

Specifically what I have are two tables:

View Replies !

Trouble Installing The MySQL GUI Tools With Red Hat 8.0
I just installed MySQL Community Server 5.0 onto my computer,
which is running Red Hat 8.0 and it works fine, but
I was trying to install the MySQL GUI Tools (Query Browser, MySQL
Administration and the Migration
Toolkit) and I'm having trouble with it, I have decompressed the
installer ,

I go to the installation directory (/usr/local/mysql/mysql-gui-
tools-5.0) and type in ./mysql-query-browser to run the query
browser,

but it gives me this error message:
../mysql-query-browser-bin: error while loading shared libraries:
libgtkmm-2.4.so.1: cannot open shared object file: No such file or
directory.

View Replies !

Trouble Updating Mysql Database
I am trying to update my Mysql database with a dataset. The dataset is created and filled on a handheld device running the SqlCE server. I then call the dataset from the handheld via a web service.
Running the mysql data adapter and executing the Update method does not seem to work, (I think its because the dataset does not recognise any changes made to it).
Can anybody please help me in this regard, or just send me some links with GOOD examples of where somebody is getting it to work.

View Replies !

Trouble Logging Into MySQL Administrator
I've installed MySQL and it appears to running properly because I can enter data in the command line format but I'd like to use the graphical user interface of MySQL Administrator. When I try to log in, I can not get through.

Is there someplace on the MySQL website that I can go to send me a reminder of the information I used to set-up the server and how to properly fill-out this form? When I hit contol and skip, it shows that my server is running. I did save the original information I gave on the website during the server configuration and download procedures but when I try different combinations of this information, I'm still not able to access the Administrator functions.

View Replies !

Linux (Fedora) MySQL Trouble
I'm currently trying to complete a MySQL book I purchased. I have installed the server and client applications. When I go into the terminal to run MySQL, I get the following message:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

Not sure what exactly this means. I did look in the mysql directory and the sock appeared to be there.Still fairly new to Linux, but it's all I have to work with.

View Replies !

“Rewriting Subqueries As Joins For Earlier MySQL Version
In my portal I use MySQL version 4.1.7 where i can use subquerys but now i need to move my portal to server where is MySQL version 3.23.58.

This SQL statement should convert to form where is NO subquerys and do it only ONE statement:

select * from ajanvaraus where toimenpidetunnus in(select toimenpidetunnus from toimenpide where kategorianimi = (select distinct kategorianimi from toimenpide where toimenpidetunnus = $tp))";

I managed to convert inner subquery to form:

select taulu2.toimenpidetunnus from toimenpide as taulu1, toimenpide as taulu2 where taulu1.toimenpidetunnus = $tp and taulu1.kategorianimi = taulu2.kategorianimi

now i need to compine my last two querys togehter.

View Replies !

Trouble Chanigng Mysql 5 Socket Location
How do I change the place where the mysql socket file gets created? I
thought it was in the my.cnf file by changing the "socket" property.
However, using MySQL 5.0 on my Fedora Core 5 Linux system, when I do
this and restart, attempting to connect to MySQL through PHP gives me
this error

Warning: mysql_connect() [function.mysql-connect]: Can't connect to
local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in
/usr/local/apache2/htdocs/refillingstation/db.php on line 19
Can't connect to MySQL: 'Can't connect to local MySQL server through
socket '/var/lib/mysql/mysql.sock' (2)'

Here is my "my.cnf" file:

[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql5.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

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid .

View Replies !

Help! Trouble Loading .txt Files Into MySQL Front
spent all night entering data into my 1st DB via "mySQL front" and this morning had to reinstall everything, so i lost the whole DB but i did export it as a .txt & html table file last night.. but now i can't seem to get it back :mad:

here is what the exported .txt file looks like:

id,artist,title,label,price,sound
1,Niko Bellotto ,Slam mode,La colecion,21.50,slam_mode.mp3
2,Oliver Ho,Listening to the voice inside,Meta,40.50,oliver.mp3
3,Numero Deux,The dinning rooms,Conesa,32.50,numero.mp3
4,Photek,Solaris,Omni,32.50,photek.mp3
5,Gianni Ferrio,Spring themes,Garden,32.50,spring.mp3

..... more of the same..

(its for an online cd store (fake one)
i created a table with the same fields and tried to "import .txt file" but all i get is the id field filled up? the other fields stay blank?

View Replies !

Trouble With Export Of Access Table Into Mysql
I exported some tables out of access into mysql and I cant edit and records under mysql root account through mysql's query browser. I can edit from mysql command line using same credentials while working localy on the mysql server. Wondering why Query broswers Edit button is greyed out.

View Replies !

Trouble With NATURAL JOIN Between MySQL Versions
I have this query which works perfectly well in MySQL 5, but doesn't work in 4.1.
I know there were some changes made to join syntax between the versions but for the life of me I can't figure out exactly what I need to do to fix it:

SELECT Status, UserName, FontName, FontPath
FROM AccountInfo
NATURAL JOIN AccountPrefs, Templates.Fonts
WHERE AccountPrefs.HeaderFont = Fonts.FontID
AND UserID = '$userID'
works fine in v5, but in v4.1 I get the old "Column 'UserID' in where clause is ambiguous".
I'm trying to match the UserID in AccountInfo and AccountPrefs, I just left the font thing in there as well because I'm not sure if it's contributing to the problem.

View Replies !

Trouble Connecting To MySQL On Windows Server 2003
Fatal error: Call to undefined function mysql_pconnect()

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_connMySQL = "localhost";
$database_connMySQL = "database";
$username_connMySQL = "root";
$password_connMySQL = "mypassword";
$connMySQL = mysql_pconnect($hostname_connMySQL, $username_connMySQL, $password_connMySQL) or trigger_error(mysql_error(),E_USER_ERROR);
?>

I installed the ODBC drivers as an optional solution, and that appears to be working when I test it within the program, but I don't know how to get my .php website to connect to it. .php files are working correctly on the server, but it's just not connecting to the database.

View Replies !

Subqueries On 4.0.25 Without Subqueries
i have 2 tables. knowing subqueries cant be performed on this version how would i write the following statement?

i have 2 tables
table.a, table.b

I want to select all records from table.a that match table.a.id = table.b.id WHERE table.b.varchar = 'value'

once the table.b.varchar is found, get table.b.id and match it to any records in table.a.id that match it.

Can this be done in 1 statement. I'd hate to have to run a loop here. I've tried without success JOIN statements.

here is one of my attempts:
"SELECT table.a.* FROM table.a, table.b WHERE table.a.id = table.b.id AND table.b.varchar = '$value' GROUP BY table.a.sortkey ORDER BY table.a.is_primary DESC ";

this does return 1 row, and joins the 2 tables accordingly, however i need all rows in table.a that match the table.b.id where table.b.varchar = 'value'

View Replies !

Use Of Subqueries With IN
I'm trying to do the following query:

select * from table1 where col1 in (select col1 from table2);

I keep getting a message to check my query after "in". But I've cut and pasted that query and it works. This is a very simple query so I don't understand why it doesn't work.

View Replies !

Subqueries With ALL
I got following statement:

SELECT * FROM ip WHERE id <> ALL (SELECT ipid FROM pcip);

Like I can see in the documentation, this should be correct, but SQL drops a SyntaxError near "ALL (SELECT ipid FROM pcip)"

What it dows should be ovious, select all IPs that IDs are not noticed in the PCIP table under IPID

The Tables are set up correct, means both tables exist and got values for the mentioned collums

My MySQL version is 4.0.21 if that is part of the problem...

View Replies !

Subqueries For V3.23
An upgrade is definately coming, as some some SQL practice, but I'm trying to figure out how to do a subquery and not entirely certain where to turn to.

From my limited understanding of SQL, what I want to do should look like:

SELECT
(SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='0') as a,
(SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='1') as b,
(SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='2') as c
FROM SurveyData

The SurveyData table contains 3 fields: SID (Int), Name (Varchar 100), Choice (Int)

I'd like to use it as the basis for a reusable/scalable web survey app (perl CGI), but all I'm getting is a syntax error.

View Replies !

Subqueries With NOT IN And <> ALL
I am having troubles with the following on 4.0.22:
select *
from question as q
where q.id NOT IN (select distinct question from choice );

choice.question ---> question.id

Apparently there's a syntax error, at (select distinct ....)
<> ALL yields the same result.

View Replies !

Possible Without Subqueries
I'm trying to merge the result of 2 queries but I can't use subqueries.

The first query gets all the sign-ups from a particular referer, the second query gets how many of those sign-ups are still active.
So the first query does a count, group by ref and the second one does count where active group by ref - I know I could do this easily with subqueries but without them I'm lost.

View Replies !

Subqueries?
I'm using MySQL Control Center 0.9.2-beta. Will this take subqueries? I'm trying to extract data from tables in two different databases, and I'm not all familiar with JOINS. What is the syntax to do this in a subquery? In a JOIN?

View Replies !

Max() & Subqueries
Cant seem to get what i want Missing something so fundamental. Did it with subqueries on server at home just cant seem to get my head round it without using subqueries

I have 3 tables ....

View Replies !

UNIONS In Subqueries
I have a problem using UNIONs inside subqueries. I have simplified my query to make it more readable. The question is about the right syntax.....

View Replies !

How To Rewrite This Without Subqueries?
i have a table that contains the fields order_id, canceled, and
captured. the same order_id can appear many times in the table. i
want to retrieve all the order_ids for which NO record exists that has
both canceled and captured = N.

i am not able to upgrade my version of mysql at the moment, so i would
like to write a query that does that with no subqueries.

View Replies !

SQL, Count And Subqueries
I have one table that lists a number of courses available (including
CourseID, CourseName and MaxPositions), with CourseID being auto_increment and unique

I have another table which has CourseRegistration (Containing CourseID and
CustomerID), with no unique fields. Customer information is stored
elswhere.

Without having a field in the Courses table holding the number of people
currently registered, how would I return a result set which included:

CourseID, CourseName, MaxPositions and the Number of people registered to
take that course.

Preferably this would not use features in MySQL 4.1, but anything is fair
game at this point.

View Replies !

Theory Of Subqueries For 4.1.7
While converting SQL statements for a database change, I discovered a
big performance hit in MYSQL with subqueries vices Sybase. I'm hoping
that someone might be able to help me understand why?

I have two tables USERS (2200 records) and JOB Decriptions (163
records). I wanted to retrieve all the job description not in the
USER table. (No Indexes on JOBDESC currently)

select JOBDESC from JOBS where JOBDESC not in ( select JOBDESC from
USERS where JOBDESC not NULL )

In Sybase the query returns in about .56 secs
In Mysql 4.1.7 query returns in about 8.78 secs

The funny thing is if I run the query like so

select JOBDESC from JOBS where JOBDESC not in ('President','Vice
President','Treasaur','Secretary')
returns in .03 secs.

Those four descriptions are returned by the inner select.

All select statements individually return in .03 secs.
I created indexes on both tables for JOBDESC and reduced the time to
..97 secs. I used explain command to help understand what is going on
hence the indexes, but why the difference in speed?

View Replies !

Removing Subqueries
the query i want execute is:

select email from avalia where email in (select * from avalia where nome=´img/im1.jpg´) and email in (select * from avalia where nome=´img/im2.jpg´)

but mysql 4.0 don't acept subqueries.

View Replies !

Optimization :: Subqueries
i have a little complex query that involves sub queries upto three levels. now thing is that , i think, mysql evaluates the sub queries every time that query is evaluated, whereas, i know that results for the third and 4th level queries are same for some number of queries. can we force mysql to store the result of the subqueries to be used later, instead of reexecuting the query.i studied mysql query optimization but, finally concluded is that things over there are just how mysql optimizes queries, not how can we optimize the query to be performed better. any resources for query optimization, i mean good resources ?

View Replies !

Subqueries And The IN Operator
hey all,

i am having serious problems with getting my head around why this will not work. i am trying to return a number of games played with a count of the number of people in it. the table structures are as follows:

games

gameref | gamepot | gamedate | gamelength |

..1...... | .... 50.... | 12.12.04 | ... 03.00.00 |

winnings

gref | pref | amountwon | stake |
1 . .| 1 .. | ....20 ...... | 10...|

i have used the dots to try and keep the table in some sort of form. i have only entered one row of data for each just to show the format of it, if this is not enough please ask for more.

the games table holds each game that has been played, the total pot available to be won the date played and the duration of the game.

the winnings table is teh winnings of each player in each game. the gref is the game it refers to, the pref is teh player it refers to, (there is a player table but is not used in this query at all). the amount that player won and the stake they entered into the pot.

if ya didnt gues its a poker database. my problem is as follows.

Quote: $sql = ("SELECT games.*, COUNT(winnings.gref) as playercount, winnings.gref
FROM games, winnings
WHERE gameref = gref
group by gameref
order by gamedate");

this query works a treat. it counts the amount of players in each game and outputs it all nicely.. but i wanted to edit the query to be able to show just all the games a single player had been in.

Quote: $sql = ("select games.*, COUNT(winnings.gref) as playercount, winnings.gref
from games, winnings
where gameref in (select gref from winnings where pref ='$pid')
group by gameref
order by gamedate");

$pid is passed into the page by

Quote: pt_register('POST','pid');

but this throws an error

Quote: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ghoxmfjz/public_html/database/poker/allgames.php on line 32
Query failed!

line 32 is:

Quote: $nrows = mysql_num_rows($result);

i can get it to work if i substitute the subquery for (1,2,3) being the first 3 games played. this would assume a certain player would have been in these 3 games.

the problem i get is that the COUNT function just counts every player in everygame not the individuals. if i group it by gref it counts them (sort of.. it doubles the count) but then does not return any of the other information for the query.

i am very confused any and all suggestions of things to look at or consider or a straight ur doing XXX wrong would be grand.. thx all

Jon

oh btw if you wish to look at the output if that helps the URL is:

http://www.happygolucky.me.uk/database/poker/allgames.php

with the variable $pid as the reference to try the player.

View Replies !

Subqueries Clarification
im using MySQL 4.1 the one the comes with XAMPP installer.. I have a question regarding correlated subqueries..in this version of
MySQL, are correlated subqueries supported?

as in like..

$query1= select field from table where field = 1;
$query2= select field from table4 where field= 4;

Insert into tmpTable ($query1,$query2)

View Replies !

SubQueries Prior
Does anybody know how to rewrite the following query to run on mysql 4.0.20.
Is it possible to do this with JOINS? (I don't really have to join 2 tables...
SELECT *
FROM OAK_photos
WHERE cat_id = (SELECT cat_id FROM OAK_photos WHERE id=$id)
AND order_nr >= (SELECT order_nr FROM OAK_photos WHERE id=$id)
ORDER BY order_nr ASC
LIMIT 0 , 5

or should I run the 2 subqueries first as seperate queries and pass on the outcome via variables to the third query, the above main query?

View Replies !

Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear)
FROM tblmonths, tblyears
where CONCAT(tblmonths.fldID," ", tblyears.fldYear)
NOT EXISTS
(SELECT CONCAT(fldMonth, " ", fldYear)
from tblexpensesclaims)
GROUP BY tblyears.fldYear, tblmonths.fldID

View Replies !

Subqueries In The FROM Clause
I'm using 1&1 web hosting services with MySQL as database (Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i686) using readline 5.0).

When I ran an example in Chapter 13.2.8.8 from MySQL 5.0 Reference Manual:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

I always get the following error message:

ERROR 1064 (00000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb

View Replies !

Problem With Subqueries
I am trying to find the rows in table1 that do not have matching rows in table2 in order to set the value of a field in table1. I can get a query to return the rows that do have matches with this query:

select item1 from table1 where item1 in (select item2 from table2);

However, the following queries return no data:

select item1 from table1 where not(item1 in (select item2 from table2));
select item1 from table 1 where item1 <> all(select item2 from table2);

My understanding of the first query is that the not operation would be applied after evaluating whether item was in the result of the subquery. With the second query, I thought it should return true if item is not equal to every result in the subquery. If I try to use a left join and then simply find the rows that have a null value coming from table2, I get the correct resultset but I can't use that as a subquery for my update statement. Since table1 is what is being updated MySQL tells me that table1 cannot be used in the subquery for the update statement. I have this working in a stored procedure by importing the result of the join into a temporary table, but I'd rather not have to write all that data and then delete it.

View Replies !

Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor)

This is the query :
SELECT NPL, PP FROM P_A INNER JOIN ACTIONS
ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in
(SELECT NA FROM P_A WHERE NP ='ABC')

P_A has 5 columns and 12000 rows
Actions has 5 columns and 770 rows

Any suggestions ?

View Replies !

Column Subqueries
to the best of my knowledge, a column subquery is a single column resultset with many rows, i'm assuming joined by some previous column / row value.

can anyone show me a column subquery, manybe even a multiple column subquery, with a very small discussion on what they did and what it would return?

I'm looking to return a query like username, company name, company location, application, application role, application persission where I do not get the "normal" repetitive SQL resultset.

note: column subqueries may not be my direct answer but I still wanted to see what column subqueries was all about.

View Replies !

Subqueries And Limit
I am a beginner with MySQL and I have this very large table where queries take a long time to complete. I was trying to limit the amount of time by limiting the scope of the query to only a few rows, by using LIMIT. To that effect a construct like:

SELECT * FROM table LIMIT 100;

seems to work perfectly, because it returns the 1st 100 rows from the table.

The problem is, I cannot use this in a subquery. In other words, if I try something like:

SELECT * FROM (SELECT * FROM table LIMIT 100) as temp;

I'm getting an error. I read the documentation about subqueries in the FROM clause, but I cannot figure out what's qrong with the syntax.

View Replies !

Queries And Subqueries
SELECT COUNT(t.id) AS tuttotal, (SELECT COUNT(t.id) FROM tutorials AS t WHERE t.status = 1) AS pendtotal, (SELECT COUNT(r.id) FROM reports AS r) AS reptotal FROM tutorials AS t
If I input that SQL into mysql it returns exactly what I'm after. And on my development server it works as expected.

However on my production server I am getting an error when I try it in PHP;

You have an error in your SQL syntax near 'SELECT COUNT(t.id) FROM tutorials AS t WHERE t.status = 1) AS pendtotal, (SELECT' at line 1

I think it might be that my server is running an earlier version of MySQL, 3.23.28 I believe.

View Replies !

SELECT .. WHERE .. Subqueries ?? Is It Possible?
I have a page which will send over three possible query's to the other page, but the page receiving the query's will only ever execute one..

gal/event/date
gal/venue/date
gal/event/venue/date

I need the page receiving this data to be able to handle what ever gets passed over..

I currently have:

SELECT photo_id, DATE_FORMAT(date, '%d%m%y') AS subdir, views, caption FROM photos WHERE venue_id='$venue_id' AND date='$date'"
which deals with 'gal/venue/date' from above, but how can I structure the SELECT and WHERE so I can have them all covered but in the one SELECT statement?

View Replies !

Subqueries And LIMITs...
I want to display a subset of data from another query...

Basically I've got 3 tables which are all joined together in a query...

Lets say I have 300 results rows displayed from that query. Is there a way I can then limit those results without effecting the original query (I still need to be able to pull all the results as well)

I've tried (and it didn't work)...

Quote:

SELECT * FROM products, manufacturers, brands
WHERE

(SELECT stock_code FROM products, manufacturers, brands

WHERE

(manufacturers.manufacturer_id = brands.house_id) AND
(products.brand_id = brands.brand_id)

ORDER BY

manufacturers.manufacturer_name,
brands.brand_name,
products.stock_code)

LIMIT 0 , 10

View Replies !