MYSQL :: Convert A Subselect To Inner Join

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!?

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 ....

Convert Subquery To JOIN
I have a working subquery:Code:

SELECT ID, company_name, logo_file_name FROM company ORDER BY (SELECT 1 FROM company as inside WHERE TRIM(logo_file_name) != '' AND ID = company.ID) DESC, company_name ASC

It grabs all of the company rows, and puts the ones that have a logo file name at the top.This query works fine on my testing machine (mysql 4.1.7).
However, my production machine only has mysql 4.0.18 and I have no way of upgrading it to 4.1 to get subquery support. I've been looking at trying to convert it using a JOIN statement, but I'm stumped.

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.

Subselect In 4.0.12-max With -- New Option
I was reading the manual and it said that the subselect is only
available in 4.1 or using the 4.0.12 with the mysqld =96new command line
to start it.

But it doesn=92t working!! So I downloaded the 4.1 alpha version with =
the
same problem. The error is:

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 =85.

Any ideas? I need subselect working.

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?

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.

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.

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?

LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want.

The tables are:
table services
service_id
service_name

table services_provided
service_id
service_date (date field)
cust_id
service_quantity

I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time

The query:

SELECT service_date, service_name, service_quantity
FROM services
LEFT JOIN services_provided ON services_provided.service_id = services.service_id
WHERE cust_id = $cust_id
AND MONTH(service_date) = 10
AND YEAR(service_date) = 2007
GROUP BY service_id
ORDER BY service_id
(Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.)

I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table.

But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results.

Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.

Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results:

QUERY 1:
SELECT COUNT( o.orderID )
FROM order o
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 161

QUERY 2:
SELECT COUNT( o.orderID )
FROM order o
LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID
WHERE DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 175

QUERY 3:
SELECT COUNT( o.orderID )
FROM order o, credit_card cc
WHERE o.orderID = cc.orderID
AND DATE( o.orderDATE ) = &#55614;&#57159;-01-04'
AND o.orderSTATUS = 300

yields 157

Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables:

Week (with columns day and hour)
Activity (with columns day, hour, activityid and ac_text)
Person (with columns name and activityid)

I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour

I can then make a loop (I am usin asp.net) that writes the activities.
My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

How To Convert An Mdb To Sql?
How do you convert an mdb ( MS Access Database 2000 ) to a sql file ( mysql )?

How To Convert .csv To .sql?
I have the data in an .csv file and I need to import it into the MySQL database. How can I do this? I have tried various MySQL clients but they all return errors.

Convert .doc To .csv
I am trying to convert a table in a Word .doc into a .csv to import into mysql.
When I copy the contents of a table cell where there are multiple paragraphs within the cell the result in Excel is multiple rows, one for each paragraph.
Can someone give me a hint on what I should be doing so that each cell is contained within o single row?
I tried Shift | Enter in Word to separate the paragraphs but that didnt work. I want to maintain the paragraph structure in the database.

Convert .csv To .sql
I have the data in an .csv file and I need to import it into the MySQL database. How can I do this? I have tried various MySQL clients but they all return errors.

Convert MS Access To MY SQL???
I need to convert an existing MS Access db to an MySQL database.
The MySql is on a server maintained by my ISP. The Access db is on my PC.
What would be the best way to do this?

Convert A Table To Utf-8
I'm converting my PHP-MySQL website to UTF-8 and my question was how can I do that in MySQL ? Is it really enough to set another collation to my tables?

If it is of any help, the tables are filled and are latin1_swedish

Convert Query From MS SQL
We have a database in MS SQL (and ColdFusion) that we're moving to MySQL (w/ PHP). Almost all of the queries have converted perfectly, or we've been able to rewrite them fairly easily. However, there is one query I just don't know how to rewrite because it uses a MS SQL function called 'grouping' that I've never used.

Below is the query. Anyone have a clue what to replace the "grouping()" function with relative to MySQL? How would we change the query to work on MySQL 5?

SQL
SELECT DISTINCT C.ID, C.Name, S.Month, S.Year, S.PageHits, S.Applications, S.crew, S.mgmtFROM( SELECT P.ID,case when ( grouping( C.Month ) = 1 )then 13else C.Monthend AS Month,case when ( grouping( C.Year ) = 1 )then 9999else C.Yearend AS Year,sum( C.PageHits ) AS PageHits,sum( C.Applications ) AS Applications,sum( C.crew ) AS crew,sum( C.mgmt ) AS mgmtFROM RecordCounters CINNER JOIN Records RON ( R.RecordID = C.RecordID )INNER JOIN Coops PON ( R.ID = P.ID )GROUP BY P.ID, C.Year, C.Month WITH ROLLUP ) AS SRIGHT OUTER JOINCoops CON ( S.ID = C.ID )
LEFT OUTER JOIN Records RON ( R.ID = C.ID )LEFT OUTER JOIN Regions EON ( E.RegionID = R.RegionID )WHERE E.RegionID = {$_GET['regionid']}

How Can I Convert Mysql 4.1 To 4.0
i have a phpmyadmin bacup of my databse from a server which is mysql 4.1

my ex-account terminated i couldnt convert it on phpmyadmin

now new server is mysl 4.0

when i try to dump it it says

Error at the line 26: ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Query: CREATE TABLE `access` ( `userid` int(10) unsigned NOT NULL default '0', `forumid` smallint(5) unsigned NOT NULL default '0', `accessmask` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`userid`,`forumid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

MySQL: 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 'DEFAULT CHARSET=latin1' at line 6

i know this is becaue the version bcs i try on anther server i convert it via phpmyadmin but now i havent i phpmyadmin bcs ex-server terminate my account.

Convert Csv To Dbf MySQL
I'm used to using phpMyAdmin to upload csv files to a MySQL database. GoDaddy, however, has modified (I assume) their installation so that it won't allow this. One can only upload MySQL formatted dbf files. Neither do they allow remote management with software such as Navicat. My question is this...

In OS X is there an application, plug-in, etc that can easily convert an Excel spreadsheet into a properly formatted MySQL file so that I can upload using GoDaddy's mod? I've searched high and low, spent hours trying to find something. I'm sure there's a very simple answer--hopefully one other than having to manually retype the entire spreadsheet.

CAST Or CONVERT Big Int
I have a column with datatype BIGINT which is a 64 bit unsigned, numeric. The actual length of the data is only 13 integers.

I am needing to convert BIGINT to INT(13) or to a string (CHAR) so that I can load it into another datebase.

The BIGINT comes through an ODBC as exponential, which is worthless to me.

SO I want to create a view of the table and which converts or casts the bigint to another datatype like NUM.

I used this query and it seems to work:
select CAST(order_number as CHAR), date_created
from transaction_credits
where date_created > '20070103'

But I would prefer to convert the BigInt to an Integer(13).

Has anyone a solution for this, or a way to configure the mysql ODBC to automatically cast or convert the big int datatype?

Convert Access
i am trying to concvert my access database to mysql bt i am getting a error.
i just go to export in access choose odbc databases, i am sure i am doing it right
the error i get is odbc call failed no database selected.

Convert MS Access To MY SQL
I need to convert an exsting MS Access db to an MySQL database.
The MySql is on a server maintained by my ISP.

The Access db is on my PC.
What would be the best way to do this?

Convert Sql -> Mysql
i have a table 'forum' with
id - topic - user - datum - comment

What i want is a table with only the max date with the corresponding user grouped by the topics
so i can set the last message was post on ... by ...

i found the solution in SQL:
SELECT topic, user, datum
FROM forum t1
WHERE datum = (
SELECT MAX( datum )
FROM forum
WHERE topic = t1.topic
)
But this give a Syntax error in MySQL

Can someone convert it?
Or does anyone know the solution?

Convert XLS Into MySQL
Most of the tools I try are demo/failed
please help me to convert xls into mysql.

Convert Database
We use the MS access database, I want to convert the database to mysql
db , could suggest is there any tools / methods that I can export the
table and data from access , and import it to the new mysql db ?

Convert And Cast
I'm trying to convert binary strings that have been created using aes_encrypt('string','secret') to a text or character string.

I know MySql does this with convert http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

But can you give me a small example using convert and cast where a binary string is converted to characters or text. The column that stores my binary data is called org_a. What ever i do i don't get any valid output. Just a 2 small exmaples using convert and cast (not aes_decrypt).

Convert M9M 2S4 To M9M2S4
I am trying to change postal code data in my mysql table.
Current format is M9M 2S4
I would like it to be
M9M2S4
I am just a user and not really a programmer or DB guy.
I know how to run SQL queries on the table.

Convert Database.
I have a database running under MySQL 3.22.
I would like to migrate them to a new server running MySQL 4.1.14.
How I can convert the database to mysql 4.1.14.

Convert String
I have a database with records that contain a string date field in the following format:

22 jan 2005 15:02:22 GMT

Any way I can have MySQL format that to a timestamp or other date format which I can use to calculate with?

(need to flush records older then a certain date)

Convert Function
How can I convert a function (SQL SERVER) that returns a TABLE type and IS USED in queries, like, SELECT * FROM Item INNER JOIN myFunction(SomeParameter) ON Item.ItemID=myFunction.ItemID

Here's an example (SQL SERVER):
CREATE FUNCTION [DPVIEW71_0](@TransDocument nvarchar(255),@TransSerial nvarchar(255)) RETURNS TABLE AS Return (SELECT [TransDocNumber], [CreateDate], [DeferredPaymentDate], [PartyID], [PartyName], [TotalNetAmount], [TotalTaxAmount], [TotalTransactionAmount], [CurrencyID], [ContractReferenceNumber] FROM [BuyTransaction] WHERE [BuyTransaction].[TransDocument]=@TransDocument AND [BuyTransaction].[TransSerial]=@TransSerial)

Convert Quotes
I have a simple HTML Form that post text values to a mySQL insert script. sometimes I might have quotes in the title of an entry. As we all know mySQL doesn't like that how can I get around that. I see all these CONVERT()and String functions but I don't know if this applies. I don't run PHP, JAVA or any of these other technolgies so I'd appreciate a straight forward response without including these type of methods.

Convert SQL Query
I'm trying to convert the following query to MySQL:
CAST(CAST(dataMonth AS VARCHAR) + '/' + '01' + '/' + CAST(dataYear AS VARCHAR) AS SMALLDATETIME)
BETWEEN '2005/01/01' AND '2005/08/01' AND InputMethod = 1;

What this query is doing is taking two fields and stringing them together to form a date. The end result is used to check w/in the given date range.
is the SMALLDATETIME considered a column alias when using a CAST function in MySQL? If so, then I can't use the alias name in the WHERE clause of a SELECT statement. Which is what is hanging me up.
I'm just trying to take the data from two different fields and convert it to a date so I can use the result in the WHERE clause.

Convert MySQL To DBF
I would like to know if it possible to convert MySQL to DBF?
I have managed to convert MySQL to DBF by using DBF2CSV to create the CSV and then using mysqlimport to get the data into a MySQL table.

Has anybody used mysql2dbf successfully because I have recently tried running this program on Debian but I get this error message:
"Segmentation fault"
The program creates my DBF file but just doesn't fill it with any data, any ideas on this one at all?