MYSQL :: Syntax For Multiple Table Sql Select Statement

Select Statement With Multiple Counts/multiple Joins Trouble
SQL
SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count
FROM duo_groups as g
LEFT JOIN duo_group_members as m on m.group_id=g.group_id
LEFT JOIN duo_users as u on u.user_id=m.user_id
LEFT JOIN duo_videos as v ON g.group_id=v.group_id
LEFT JOIN duo_video_comments as c on v.video_id=c.video_id
GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username
Will return

My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.

Syntax For Using Variables In A Select Statement
I haven't figured out how to use the Query Browser yet and all docs I've found so far only talk about stored procedures so I'm at a loss.

I'm running MySQL 5.0 using DRIVER={MySQL ODBC 3.51 Driver} in my vb application.

I have 2 tables, Users and Transactions.

In the Users I have a User Pin Number and in Transactions I have a Transcation Pin Number.

I do this...
rsUsr.Open "SELECT * FROM users", conn, adOpenDynamic, adLockOptimistic ... ok.

Then I want to do this...

strUPN = rsUsr("usr_pin") ... ok

rsTran.Open "SELECT * FROM transactions WHERE trans_pin = strUPN, conn, adOpenDynamic, adLockOptimistic" ... not ok

How do I get the variable strUPN into the SELECT statement.

Syntax Error In Select Statement
I use MySQL 4.0.18 in Linux environment, and I'm new to MySQL. I can get the returned records if I run the following SQL statement in Oracle. But I got syntax error when I run the following SQL statement in MySQL. Can someone tell me what the wrong is with the following SQL statement? Thanks a lot!

select hostname, vendor from serverinfo where vendor IN (select v_name from vendor);.

Syntax For Select Statement Returning Output?
I have an application that I am converting from MS SQL to MySql.

I need to return a value from MySql using a parameter.

In MS SQL my select statement is:
"select @id = id from database"

That doesn't seem to be working with MySql... does anyone have any
suggestions?

Multiple COUNT() In SELECT Statement
SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,5) = "Small"
) OR (
LEFT(s01_Options.prompt,2) IN (28,30)
)
)

SELECT
COUNT(s01_Products.id)
FROM
s01_Products
LEFT JOIN
s01_Attributes
ON
s01_Attributes.product_id = s01_Products.id
LEFT JOIN
s01_Options
ON
s01_Options.attr_id = s01_Attributes.id
WHERE
(
s01_Products.active = 1
) AND (
(
LEFT(s01_Options.prompt,6) = "Medium"
) OR (
LEFT(s01_Options.prompt,2) IN (32)
)
)
And one for Large and Extra Large...

I would obviously like to combine all 4 queries in to 1 so that I can get my 4 counts in a single,

Select Statement From Multiple Tables
how can I do this type of join:

table1: id, value1, value2
table2: id, value1, value2
table3: id, value1, value2

the "id" is a product code
the values are new & used pricing

I need a select that can join all this data, and fill 0's if an id is
not listed in a gven table, then output to a file like so:

id, table1.value1, table1,value2, table2.value1, table2.value2,
table3.value1, table3.value2

obviously, if I join on all the ids, it only will output the values
that have the same id in every table. how do I get that plus all the
values that are unique to each table?

Accessing Multiple Tables With A Select Statement
I am writing a select statement, that says something to this effect. Select * from texas, california, oklahoma; Everything in each table is going to be the exact same. as far as city, name, and zip...they are the titles of my fields. But when I go and look at this file, it just shows everyone from oklahoma. If take out out oklahoma, then it shows everyone from california but not texas. What is the proper way to do this?

Multiple Items In An ALTER TABLE Statement
Was in the midst of doing something today and I attempted to drop a
number of columns in a table with the following:

ALTER TABLE tmp DROP COLUMN col_1, col_2, col_3, col_4;

Unfortunately MySQL gave me an error reading:

ERROR 1064: 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 'col_2, col_3, col_4' at line 1

Can you not have multiple columns names in an alter statement?

Multi Table SELECT Syntax.
Here's what I'm trying to do without all the extra columns for the project. I have 2 tables. A user table with columns (int) id, (varchar) username and a another table called related with columns (int) user1, (int) user2 which are id values from the user table.

I need a select query which returns rows with the id and username for both people. I know i could store the username in both tables and make it easy but that cant be a good practice.

Select Statement FROM A TABLE That Has Two INDEXES?
I have the folowing table:
-----------
| content |
-----------
|id |
|parentId |
|title |
-----------

I want to select all the table records but I want in the ParentColumn to
return the title instead of the ID.

If it was in two different tables I would doit using SELECT * FROM t1,t2
WHERE t1.id = t2.parentId

BUT they are in one table.

SHOW TABLE STATUS & The Nested SELECT Statement
The "SHOW TABLE STATUS" command seems to return a result set, just
like "SELECT * FROM SomeTable".

Therefore, since the following is valid:

SELECT * FROM (SELECT * FROM SomeTable)

I inferred that this functionality would extend to the SHOW statement:

SELECT * FROM (SHOW TABLE STATUS FROM SomeDataBase)

But such a command gives an invalid syntax error.

I want to query the data_length from a few tables, place those values
into temp variables and then run a second SQL command using those
values. Is what I'm trying to do possible?

Multiple Table Joins In A Select
How do I write a select query to join more than two tables? =20

Table A relates to table B and table B relates to Table C.

I need to return fields from Table A and C that are related..

Statement Syntax
Other than INSERT, UPDATE, and DELETE, are there any other 'words' which being a SQL statement to alter data in a MySQL database?

I know this may seem like a strange question, but it's kind of important for a project I am working on.

Sql Statement Syntax
Could someone help me on this! What is the syntax for selecting only the year part of a field with date type. For MSSQL server its 'datepart(yyyy,approveDate)'. What's the keyword for MySQL?

Statement Syntax Question
Using an if statement about a mysql_fetch_assoc buried in a while statement regarding a different mysql_fetch_assoc:

$rowd = mysql_fetch_assoc(resultd);
while ($row = mysql_fetch_assoc($result)) {
if ($rowd['Property Type'] == 'commercial') {

If I can't, or shouldn't...why?

EDIT: Also, if I have an apostrophe in a mysql cell, and want to call it on a query
(WHERE ('Location' = 'It's Apostrophes')), how do I do that?

SQL Statement Syntax Error
"select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id in(select categories_id from categories where parent_id = '22') and p.products_status = '1' order by p.products_date_added desc limit 9"

The error message I recieve is:
"1064 - 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 categories_id from categories where parent_id = '22') an" .

INSERT Statement Syntax Error
I'm trying to make a control panel for my website that lets me insert data into a table. I'm using PHP's MySQL functions to insert this data.

$sql = "INSERT INTO Challenges(status,desc,pointvalue,winnerid,title,starttime,endtime) VALUES('$status','$desc',$pointvalue,NULL,'$title','$STimeStamp',NULL)";

$result = mysql_query($sql) or die(....

I get this error message when the mysql_query() function is run.

Query: INSERT INTO Challenges(status,desc,pointvalue,winnerid,title,starttime,endtime) VALUES('open','This is a test. This is a test. This is a test.',9,NULL,'Test1','2005-03-15-10-19',NULL)
mySQL error: 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 'desc,pointvalue,winnerid,title,starttime,endtime) VALUES('open'
Error code: 1064

I've wrote other insert staments similiar to this without any problems. I've compared the code for this insert with the others many times.

SQL Statement In Access Syntax Error
I am trying to use an SQL SELECT statement to retrieve query names from the MSysObjects table.

I used the following code:
SELECT [Name] FROM MSysObjects WHERE [Type]=5 And Left([Name],1)<>"~" ORDER BY [Name];

When I try to run it, I get an error message that reads: Syntax Error (Comma) in query expression '[Type]=5 And Left([Name],1),."~".

Does anyone know what I am doing wrong?

Syntax Error In Find And Replace Statement
Is there something wrong with the following? It gives syntax error. The sql tries to find <"a> and replace with <" a>. I think MySQL confuses with "s.

UPDATE `SOME_TABLE` SET `FIELD` = replace( `FIELD` , ""a ","" a" ) ;

Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful!

Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better.

I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns.

For example, I'd want this:
1-1,1-2,1-3,1-4,1-5
2-1,2-2,2-3,2-4,2-5

To become:
1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5

The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like:

SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345

This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).

Update Syntax For Multiple Columns
I'm looking for a feature I'm accustomed to with other SQL products, whereby multiple columns can be updated with a single SET clause, e.g.:

UPDATE tab1 SET (col1, col2, col3) = (SELECT col4, col5, col6 FROM tab2 WHERE tab2.col1 = tab1.col1);

I don't see this feature documented for MySQL. Is there a way to accomplish it?

Syntax To Pass Multiple Variables In A Url
What does the url have to look like when passing multiple variables to another page?

UPDATE Multiple Values - Proper Syntax?
I use powerbuilder with mysql database.I want to get server's datetime.

Combining SELECT Statements Into One SELECT Statement.
I want to take the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN authuser
ON name.empnumber = authuser.uname AND authuser.team = 'PHQ'
ORDER BY name.lastname, name.firstname;

and the results from:

SELECT name.empnumber, name.firstname, name.lastname
FROM name INNER JOIN crew_attendance_6QJ
ON name.empnumber = crew_attendance_6QJ.empno
ORDER BY name.lastname, name.firstname;

And combine them into one query that outputs all of the results both queries would output. Then order those results.

So far I have come up with:

Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.

Multiple Insert Statement
Just wanted to know whether its possible to insert multiple feilds in different tables inj one nested query
There are nested queries for SELECT statement like join etc
Is it possible in case of INSERT statements as well

How To Set Multiple Statement In MyODBC Connectionstring
http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-parameters.html
If I want to set multiple command lines in "stmt" parameter, how to do that?

My current connection string
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; CharSet=utf8; Port=3306; Option=0; Socket=; Stmt=SET names 'utf8' Database=yyy; Uid=xxx; Pwd=zzz;"

LAST_INSERT_ID With Multiple INSERT Statement
i have the following question:

When inserting many entries into a table with 1 auto_increment key-attibute
(say attribute user_id) and one data attribute (say attribute name)

CREATE users(user_id int key auto_increment, name text)

like

INSERT INTO users(name) VALUES ('pete'),('josh'),('carl')

When the inserting is done in a concurrent way (say 5 scripts do inserting operations like the one above) how do I get the autoincremented ids for all the inserted rows ? I know that LAST_INSERT_ID() yields the autoinc id of the first inserted row (of the multi-insert statement) say 1002 for 'pete' in this case and is also concurrent-safe. But is the insert statement atomic that i can assume that the subsequent generated ids for 'josh' and 'carl' are consecutive i.e. 'josh' gets 1003 and 'carl' gets 1004 when many scripts insert into this table ?

I dont want to use a subsequent SELECT statement to fetch the auto_incremented ids.

How Do I Write Multiple Conditions In An MySQL IF Statement?
I am using version 3.23 and want to write something like this:

Multiple Prepare Statement Inside The Procedure
I'm trying with the multiple dynamic queries inside the stored procedure means, inside my stored procedure i want to execute Multiple queries (DDL and DML). for that i'm storing query in variable and then executting it by prepare stmt.

It works fine with singal prepare stmt and failes for the multiple prepare stmt.

Is there any solution to execute multiple queries inside the Stored procedures or Functions?

SELECT Syntax
I am trying to check a variable against a specific column in my database. I know that i have sucessfully connected to the database and chosen the correct table, and as far as I can see, the syntax for this code is correct:

$usernamesQuery="SELECT 'username' FROM 'login' WHERE 'username' = '".$username."';";
$currentUsernames=mysql_query($usernamesQuery) or die ('ERROR 7: unable to connect to database: '.mysql_error());
if($username==$currentUsernames) {
echo "<script language='javascript'>alert('ERROR 8: Please choose another username')</script>";
exit();

However the when trying to carry out the script, the server returns: "ERROR 7: unable to connect to database: 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 ''login' WHERE 'username' = 'yonni'' at line 1"

SUM In SELECT Syntax
Hi I currently have this query..

CODESELECT sum( burst ) AS sum, sum( worth ) AS sum2
FROM 3279655_spends
WHERE donation = '0'

Select Syntax
I've a table with a column that its name is number.
in this column I've some numbers that some of them are repeated.
How can I select all numbers with no repeat.
I wana select all numbers but if a number repeated I don't wana have it twice or more.

CASE WHEN In SELECT Syntax
I have a table (2 columns):

Col1(string), Col2(decimal)
ABC1,10
ABC2,20
ABC3,50
ABC1,5
ABC4,10
XYZ2,5
XYZ4,10
... and so on

I want to get result to sum all ABC* and subtract all XYZ*. Example (ABC*-XYZ*) and get result 80. So:(10+20+50+5+10)-(5+10)

SELECT Syntax With EXISTS NOT()
2 tables are concerned: user_games and games_details. i want all games in my result which do not yet exist for the specific user (respectively have no games_id in the user_games table) games_details.id is autoincrement and user_games.games_id is set to this correctly.

But i still get no result and no error with the following code:

$sql = "SELECT id, name
FROM games_details
WHERE NOT EXISTS(
SELECTgames_id
FROMuser_games
WHEREuser_games.user_id = '".$_SESSION['uid']."');";

Select Count Syntax
im just a begginer in mysql. I have a problem on how can i place a result of a select statement query in a variable.
Here my select statement:
Select count(employee) from employee where empmonth =
1 and empyear = 2005;
This syntax is ok, it return a result of 123894 records. But what I want is to place the result in one variable that i can access inside the program so that i will run this select statement only once. I have tried this one syntax:
Select count(employee) into ll_count from employee
where empmonth = 1 and empyear = 2005;
but it return a result of zero record.

Mysql Select Top Row Syntax
This is the MSSQL code that i want to get into mysql. mysql is complaining about the top 1
Code:
select top 1 ta.approvedOn from trackingApproval ta where ta.trackingid =<cfqueryparam value="#getItem.trackingID#" cfsqltype="CF_SQL_INTEGER">
and ta.approvedOn is not null order by approvedOn Desc

Performance Between Multiple INSERT Statements Vs Single Statement With Lots Of Data
$sql1 = "INSERT INTO mytable VALUES ("zzz","xxx")";
$result = mysql_query ($sql1);
...
$sql1000 = "INSERT INTO mytable VALUES ("zzz1000","xxx1000")";
$result = mysql_query ($sql1000);
vs

$sql = "INSERT INTO mytable VALUES
("zzz","xxx"),
...
("zzz1000","xxx1000")";
$result = mysql_query ($sql);

is there any performance difference between the 2?

btw, there could 1000-5000 row inserts.

SELECT COUNT Syntax Error
Have a syntax error with the following query ...
SELECT number COUNT(*) AS vessels FROM process_fleet WHERE uid=1
No idea what is wrong with the sytax... can only one see what I'm missing?

Syntax Error With Nested SELECT
Working with MySQL 4.0.27-standard via phpMyAdmin (and Perl)

I've been getting error #1064 for certain SQL statments, and have traced the problem down to nested SELECTs.

This works fine:

SELECT `CITY` FROM `pa_geotags` WHERE `PLACE_ID` = 1303 ;

Whereas this (just a trivial example to show the point):

SELECT `CITY` FROM `pa_geotags` WHERE `PLACE_ID` = (SELECT 1303) ;

gives rise to:

#1064 - 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 1303 ) LIMIT 0, 30' at line 1

I've tried several other (more complex) nested SELECTs, all give exactly the same type of error. phpMyAdmin seems to be non-optionally adding 'LIMIT 0, 30' to the end of my SQL before submitting it, but I don't think that's the problem; I sent the same SQL to the same database via a PERL script and also got a syntax error reported.

Select Syntax Using Variables / Functions
I'm in the process of converting various stored procs in SQLServer over to MySQL. In doing this, I ran into the following problem:

(using the getdate() and CURRENT_TIMESTAMP functions for illustration purposes)

In SQLServer, I can do the following:

select getdate() where getdate() <> getdate()

In MySQL, the following statement is not valid. why?

SELECT CURRENT_TIMESTAMP WHERE CURRENT_TIMESTAMP <> CURRENT_TIMESTAMP;

Help Me With This Select Statement :)
I have two tables
1- articles table which contains the articleid and article_description
2- articles_translation table which contains translation_id, article_id(foreign key from the articles table) and translation_description

I would like to select all the articles which aren't translated yet... How can i do this in MySQL?

Is There Something Like A SELECT ALL BUT Statement?
Usually the tables I have to handel have a high number of columns. Additionally, columns are deleted and added dynamically. Most calls made on these tables are select statements:

Consider two tables t1 and t2. A natural join results in the columns a,b,c,d,e,f,g,h,z. A usual select statement is the following:

SELECT a,b,c,d,e,f,g,SUM(z) FROM t1 NATURAL JOIN t2 GROUP BY a,b,c,d,e,f,g

This way the column h could be "eliminated". As you can see the select and the group by clause can become fairly long. What I am looking for is a clever way to say "Group by everything but h and z". Is that possible?

Need Help With SELECT Statement
I have 2 simple tables: emps and rprts.
Table emps has: "ID", "name" (simple string), and some other irrelevant fields.
Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean).

I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest.

For example:
emps:
id name
------------------
1 jeff
2 wanda
3 brad

rprts:
id emp_id rprt type
-------------------------------------------
1 3 df 1
2 1 ie 1
3 2 ei 1
4 3 fd 0
5 2 tg 0
6 3 gf 1
For this data, the following should be selected:

id name type
----------------------------
1 jeff 1
2 wanda 0
3 brad 1

I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.

Select Statement
SELECT *
FROM Distance, LorryDetails
WHERE cavan = (Select min (cavan) From Distance where cavan>0);

here is the results i am getting

source ,Cavan, Athlone, Cork, Galway ,LorryID, LorryReg, Position
Athlone 90 0 180 80 3 02-CN-8001 Cork
Athlone 90 0 180 80 7 00-CN-1000 Cavan
Athlone 90 0 180 80 10 02-G-8001 Galway
What i want to try and do is compare the Values with the Column names and get the shortest path. for example when you compare the position cort with the column cork it is 180, cavan is 90, and galway is 80. Galway is the nearest to athlone so i want to narrow down the results to just give me back the row where the position is equal to Galway. Can anyone help me with this.

SELECT Statement
I am trying to search a table with a statement like the following;

"SELECT name FROM table WHERE state='$state' AND trigger=1 AND areaA='$area' OR areaB = '$area'";

The problem is I need it to find the 'area' value in one of two different colums while matching the 'state' and 'trigger'. My syntax seems to be incorrect and I dont know how to find matching for 2 values while the other 2 columns can be optional. but one must be found.

Select Statement
I have a transaction table, wich has multiple transactions for one customer. each transaction has type. I want to find all customers who do not have a particular type of transaction.

The problem I have is it seems to find a postive result for every customer because every customer has a least one transaction that is not of this type. and I want the ones who have never had a transaction of this type.