MYSQL :: Transform SubSelect In OUTER JOIN

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

Outer Join With Where
I am willing to select all the distinct Products from the product table and in case the PC Name was the same as a @ passed PC name return ALSO the local path, Otherwise return null for the localpath field.... How I can do that?
here is my query:

SQL
SELECT Products.Name, Products.Code,
UserProduct.LocalPath, UserProduct.PC FROM
Products LEFT OUTER JOIN
UserProduct ON
UserProduct.ProdCode = Products.Code
this returns the same product name and product code from many users....also, I want to return distinct Product Names also.

Do I Need An Outer Join?
I have a table that stores traffic data keyed on a char (even though I call it a tstamp). The data is coalated into hourly chunks and is viewed through a web app in monthly, daily and hourly increments.

I've been charged with creating some KPI's (Key Performance Indicators) from this data and one of them is for a given month/day/year, generate an addition figure of how many times the average traffic per period (in a column called average) is less than or equal to 5.

mysql> desc coal_counts ....

Outer Join
hello, I am trying to run following query in mysql.

select a.name, a.record, b.data from A a, B b where a.name*=b.name and b.date='2006-07-28'

How can I do this in mysql? I looked up LEFT JOIN but still can't figure it out.

OUTER JOIN
I have 2 tables with the corresponding columns:

1) InventoryComputers-ComputerID, ComputerSN.
2) InventoryStations-Autonum, BranchID, StationID, ComputerID.

I want to display all rows of InventoryComputers for which the ComputerID does not have an entry in InventoryStations.
So basically, I want to display those computers that do not have a station assigned to them.

I tried this sql statement, but it also displays those rows that are present in InventoryStations table, which I don't want.

SELECT * FROM InventoryComputers
LEFT OUTER JOIN InventoryStations on InventoryStations.ComputerID=InventoryComputers.ComputerID
ORDER BY InventoryComputers.ComputerID

Any ideas what is wrong??

Outer Join
i have a main table called property, and a lookup table called ptype (property type). the propertry table has a "type" field, and a "subtype" field. both these fields look at the ptype table to get their titles.

so, i currently have the query below, but i also want to add to this the subtype. trouble is the field name is going to be the same for both the type and subtype values, so how do i get at them both?

I tried "AS subtype" after the LEFT OUTER JOIN but this didnt work.

SELECT * FROM property
LEFT OUTER JOIN ptype ON ptype.pty_id = property.pro_ptype
WHERE property.pro_id = 1

LEFT OUTER JOIN + WHERE ?
I have problem with constructing a SQL query. I have tables with data:

mysql> SELECT * FROM category;

+----+-----------+
| id | is_active |
+----+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+----+-----------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM category_i18n;

+------------------+----+---------+
| name | id | culture |
+------------------+----+---------+
| Agroturystyka | 1 | pl_PL |
| Bed&breakfast | 2 | pl_PL |
| Hotel | 3 | pl_PL |
| Obiekt zabytkowy | 4 | pl_PL |
+------------------+----+---------+

4 rows in set (0.00 sec)
Category table includes category id and is_active status. Category_i18n has category names translations for several cultures and the id field is a foreign key.

Now I'd like to get result like this:
mysql> SELECT c.id, ci.name FROM category c LEFT OUTER JOIN category_i18n ci ON c.id=ci.id WHERE ci.culture='pl_PL' OR ci.culture IS NULL;

+----+------------------+
| id | name |
+----+------------------+
| 1 | Agroturystyka |
| 2 | Bed&breakfast |
| 3 | Hotel |
| 4 | Obiekt zabytkowy |
+----+------------------+

4 rows in set (0.00 sec)
And in this case (culture='pl_PL') it works fine. But when I change culture to 'en_US' I don't get any results. How to change my query to select all ids from category table and join name field from category_i18n table but with culture condition. If there is no translation there should be a NULL value.

Result I'd like to get for culture='en_US':

+----+------------------+
| id | name |
+----+------------------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
+----+------------------+

Left Outer Join
I have noticed when I do a Left Outer Join in short form that many rows
become missing as result of null values.

i.e Left Outer Join (t1,t2,t3) ON (....)

T3 being table with null values in

It seems to be all to hard when I log a bug.

Three Table Outer Join
I have three tables:

items (id, article, etc)
deliveries (id, shipment_date, etc)
movements (item_id, delivery_id, quantity, etc)

I want to get result as following:
article | sum(quantity)

but for all articles (outer join) with condition for deliveries table(shipment_date >= '2004-08-01' for example)

It works for two table join (items and movements) without deliveries:
select i.id, sum(m.quantity) from
items i left outer join movements m on m.item_id = i.id

but how I should join "deliveries" table to add condition for shipment_date ?

Left Outer Join
I have just converted an app from MS Access to MySql. I have using Hyperion Performance to run my queries. I have 4 tables and main table and three look up tables. when I do a left outer join to one table the query runs fine, however when I do the left outer join to more then one table it error out.

with this error message: 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 {oj left outer join rc_availability Al4 Al1.rc=Al4.rc} where al1.msa=al at line 1 Code:

Left Outer Join
This is what I have come up with:

SELECT CP1.PosID, CP1.Rank, CP1.BookID, CP2.BookID AS LastWeekBook, CP1.StationID, CP1.ChartTypeID, CP1.WeekID ,
CP2.Rank AS LastWeekRank, CP2.WeekID AS LastWeek
FROM Position AS CP1
LEFT OUTER JOIN Position AS CP2 ON CP1.BookID = CP2.BookID
WHERE CP1.WeekID = {d '2007-05-08'}
AND CP2.WeekID = DATE_SUB({d '2007-05-08'}, INTERVAL 7 DAY)
AND CP1.ChartTypeID = 101
AND CP2.ChartTypeID = 101
ORDER BY Rank;

(Note: joins to other tables containing author infor and book titles etc, have been omitted for simplicity.)

Instead of returning 50 rows (one for each matching row in CP1) it returns 36 rows (only those rows where the book appears in both CP1 and CP2).

I have tried alternative join syntax as follows and all return the same result:
LEFT JOIN
INNER JOIN (36 rows would be expected)
RIGHT JOIN
RIGHT OUTER JOIN

As far as I can tell the SQL as written should be giving me the desired result but it isn't and I can't see what I might be doing wrong.

Iso FULL OUTER JOIN
I need to do a full outer join, but have not seen this available in the fine manual. Is this type of join available?

The task is to list everyone that has departed the company in a certain date range. Sometimes a record is entered on table A, sometimes on table B, sometimes on both.

SELECT a.NAME, b.NAME FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.SSN = B.SSN WHERE A.DEPART_DATE BETWEEN @BEGIN AND @END or
B.DEPART_DATE BETWEEN @BEGIN AND @END .

Outer Join And Distinct
I just started building a website and use MySQL as my backend database. I'm having a little trouble creating an SQL query that will get the correct information out.

Here's my problem:

I have 3 tables:
people: stores the name of people who can vote
votetype: stores the type of votes there are (In favor, Against, Withheld)
decisions: the dicisions that were voted on

Then I have another table that links them all together called votes. Votes has three columns: personID, typeID and decisionID. personID and decisionID are primary key. All are foreign key to obvious tables.

I want to retrieve for a given person the votes he made for all decisions. So: a list of all decisions with the vote he or she made and a NULL if there is no vote.

So far I have this query:
SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, t.name FROM decisions d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

For some reason the DISTINCT doesn't work and I get a decision that is not voted for three times (once for each type while v.typeID column is NULL).

How can I fix this so each decision only shows up once?

Jurgen

-------------------------
I've found a solution:

SELECT DISTINCT d.ID, d.name, d.date, v.typeID AS vID, IF(v.typeID IS NULL,NULL,t.name) AS stem FROM decision d, votetype t LEFT OUTER JOIN votes v ON v.decisionID = d.ID WHERE (v.personID=1 OR v.personID IS NULL) AND (v.typeID=t.ID OR v.typeID IS NULL) ORDER BY d.date;

A Simple Outer Join
Sorry to post a naive question , But i need a query for this simple condition.
There are 2 tables employee and email . both have employee id as PK. I need to list employee names and emails . If enrty does not exist in email ..then it should get NULL .
Select employee.name, email.id from employee , email where ?

Left Outer Join
^ just a quick question I can't find an answer for, with the mysql syntax is 'left outer join' the same as 'left join' ?

LEFT OUTER JOIN
am using mysql and want a recordset to be displayed from a query that links three tables.
because a user can add to their selection at a later date i would like all records from a tbl_fitopt to be displayed even when there are no foreign keys in the other tables.
sql is below but at the moment it only lists records from tbl_fitopt that have keys in tbl_fitoptlink - nb i need all records from tbl_fitopt to display so a user can add/subtract from there choices.

strsql = "SELECT tbl_fitopt.fitoptid, tbl_fitopt.fitoptcost, tbl_fitopt.fitopt, tbl_stock.stockid, tbl_fitoptlink.fitoptlinkid "_

& "FROM (tbl_fitopt INNER JOIN tbl_fitoptlink ON tbl_fitopt.fitoptid = tbl_fitoptlink.fitoptid)
INNER JOIN tbl_stock ON tbl_fitoptlink.stockid = tbl_stock.stockid "_

& "WHERE tbl_stock.stockid = " & tkey & ";"

Outer Join Syntax Error
Trying to do an outer join to retrieve info from two data bases and keep coming up with syntax errors....here's the code

$result = @mysql_query('SELECT * FROM members OUTER JOIN 2nd_location USING (members.ID = 2nd_location.ID) WHERE members.type= "RET" AND members.active="A" AND members.multi_location="Y" ');

OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition.

shoppingCart table:
+--------+-----------+-----------+-----+
| cartID | sessionID | productID | qty |
+--------+-----------+-----------+-----+
product table:
+-----------+--------------+------+--------+
| ProductID | ProductCatID | name | Active |
+-----------+--------------+------+--------+
Here's my base query, which just gets all the products in a given category.

SELECT product.*
FROM product
WHERE `ProductCatID`='{$id}'
AND `Active`=Ƈ'
Here's the query I've got so far:

SELECT product.*, shoppingCart.qty
FROM product
LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID
WHERE `ProductCatID` = Ɗ'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = Ƈ'
Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!

Multiple LEFT OUTER JOIN
What is the right way to do a LEFT OUTER JOIN on both these tables to the 'Main' table where WHERE Plan.pID IS NULL and Record.rID IS NULL (primary's)

Plan
pID | _mn (foreign main key) | pType

Record
rID | _mn (foreign main key) | rDate

Main
mID (primary) | mComp

-------------------------------------
Will need: pType, rDate, mComp values as a result of the query.

Struggling With Left Outer Join
got two tables:

T1 - User, Date, Stuff

T2 - User, Date, Other Stuff

I want to be able to select rows from T1 based on User & Date, join those rows to the data in T2, so T1.User=T2.User AND T1.Date=T2.Date. Then I want to sort the results in T1.Date order

I've no idea where to start ... anyone point me in the right direction?

Double OUTER JOIN On The Same Table?
I'm trying to make a request with an OUTER JOIN on 2 fields at the same time:

Here are the tables:

test:
- id
- user1
- user2

users:
- id
- username

And Id like to get as result each line of the 'test' table, with the corresponding username from 'user' fir the fields user1 and user2.

Do you know how to do this without using several SELECT?

Performance Left Outer Join
I changed a query from an equal join to a left outer join but then the performance is suddenly 10 times as slow ?? Is this normal or do I miss something ??

$query1="SELECT * FROM ";
    $query1=$query1 . "clienttable,contacttable AS co1 WHERE co1.coclid=clid ";
    $query1=$query1 . " AND clname like '$alfabetisch%' ";
    $query1=$query1 . " order by $o";

Left Outer Join Syntax
I am trying to join a table to three other tables

Table 1 has all records
Table 2-4 must be left outer joined to table 1, not in a chain

using "other" dbs ...

select t1.a
,t2.b
,t3.c
,t4.d

from t1
,t2
,t3
,t4

Where t1.id = t2.id (+) and t1.id = t3.id (+) and t1.id = t4.id (+)

I've tried using the left outer join syntax ...

select t1.a
,t2.b
,t3.c
,t4.d

From t1
LEFT OUTER JOIN t2 using (id)
LEFT OUTER JOIN t3 using (id)
LEFT OUTER JOIN t4 using (id)

but that, of course chains t1 to t2, t2 to t3, and t3 to t4.

How can I make this join work, without the use of subqueries?

Full Outer Join Should Work
why doesn't the following work?

mysql> select * from girls full outer join boys where girls.city = boys.city;

where :

drop table girls;
drop table boys;
create table girls (name varchar(12), city varchar(12));
create table boys (name varchar(12), city varchar(12));
insert into girls values('Mary', 'Boston');
insert into girls values('Nancy', null);
insert into girls values('Susan', 'Chicago');
insert into girls values('Betty', 'Chicago');
insert into girls values('Anne', 'Denver');
insert into boys values('John', 'Boston');
insert into boys values('Henry', 'Boston');
insert into boys values('George', null);
insert into boys values('Sam', 'Chicago');
insert into boys values('James', 'Dallas');

If needed, I can go into insert and create for you!

BTW left and right work OK!

BTW what do P1(T1,T2), P1(T1,T2) and R( mean in the following

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
WHERE P(T1,T2) AND R(T2)

Left Outer Join Produces
In order to eliminate duplicate values from the right of a query I tried the following:

LEFT OUTER JOIN
        `private_comments`
    ON
        `report_cards`.id IN (select distinct `private_comments`.student_id1 from `private_comments`)

I have unique `report_cards`.id but multiples of `private_comments`.student_id1.

Sould Someone Verify LEFT OUTER JOIN?
could someone please verify :

SELECT *
FROM Members, Submits, MNotify LEFT OUTER JOIN MemPay as mp on
(mp.MPMem_ID=Members.MemID and Members.MemID=colname and mp.MPSub_ID=MNotify.FK_SubmID)
WHERE Submits.SubmID=MNotify.FK_SubmID and Members.MemID=colname
MemPay - payment table, where
MPID | MPMem_ID (actually foreign) | MPSub_ID

Members
MemID

Submits - submitted ads
SubID

MNotify - Members become notified of individual submitted ads
MNID | FK_MemID | FK_SubmID

Members can view each MNotify.FK_SubmID (submitted ads -- the ads may be bought, and if so are found in MemPay table. MPMem_ID is the buyer primary id and MPSub_ID is the id of the submitted ad bought.)

In any case, the Member can view all submitted ads (including purchases) while the check on the LEFT OUTER JOIN values gives what he or she has bought.

While it seems to output as it should, when I check on the LEFT OUTER JOIN values with php conditional statements I've getting values oppositie of what they should (checking on NULL, <>NULL, empty, !empty)

Workaround For Outer Join Using Filtering Subquery
I'm using mysql 4.0.x, which does not allow the following query (which works
fine in 4.1). Is there any way I can issue a single query to achieve the
same results? (I want a list of all records from table al, nulled where
there is no match in table alm, which has been filtered. Without a
subquery, the filtering occurs after the outer join, and for one specific
row from alm, I only see the nonmatching records from al where NO other alm
record matches.)

SELECT al.id, alm.idmember, al.listname
FROM addresslists AS al
LEFT OUTER JOIN
(SELECT idlist, idmember FROM addresslistmembers WHERE idmember = 4)
AS alm
ON al.id = alm.idlist

Left Outer Join Doing Wired Things
i have two tables, tableA and tableB, i want all of tableA but regardless of if there is a record in tableB that references the record of tableA, so thats what an outer (or left, or left outer) join should do if tableA is the left table... right?

Ok first my Query:

SELECT *
FROM tableA A
LEFT OUTER JOIN tableB B
ON A.ID=B._IDa
WHERE ((B._IDb IS NULL) OR (B._IDb=3)); -- no record exists in tableB with _IDb=3

tableA is referenced by tableB's foreign key field _IDa
tableA has 27 rows, tableB has many rows but none that have _IDb=3

So I expect to get 27 rows with NULL for all fields of tableB, but i only get 18 rows!!
(all fields of tableB are, as expected, NULL, but WHY those 18?!! why not all or none?!)

Note though that B is a composite entity, where _IDa is only half of the primary key, _IDb is the other half referencing someother table that's not important for this problem.

Code:

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

Extract, Transform
We have an old legacy database at my company called CIMPRO, running on
a SCO UNIX box. We're trying to find a way to connect to the CIMPRO
database, grab the data, and send it to a Mysql database.
I've been able to successfully connect to the database using PHP and
ODBC to view the data in the tables.
i'd like to find a way to run a Linux tool or script to connect to the
database, do an initial copy of the table structure from the legacy
database to Mysql (on the linux box) and then copy selected data.
If that is doable I'd also like to be able to refresh the table data
periodically (e.g., cron job) either by deleting and reimporting or by
checking to see what has changed.

Transform Excel To Mysql
I have a table in excel....wich i want to transform to mysql table...there are some programs that just do that, but i`ve also had a google and read that just saving it as a .csv and then import it to mysql would do the trick...
is that true?
and my second question, after that....how do i upload the whole mysql table to my website

Transform '07,01,06' Into A Date Format With Just A Query
I've been handed just about the worst HTML/PHP/MySQL site ever scrapped together. While we're in the process of rebuilding the site from scratch for our client, they desperately need some data pulled from their DB.

Originally the data was dumped to files but due to attempted injection attacks, the table has grown so large it times out. So I'm trying to pull the data directly but I have a huge problem. I need to pull all data with a date >= July 1, 2006. The problem is the dipsh*t who built this has the date field with a datatype of varchar and the date in the format of གྷ,01,06'. Obviously I can't do a date comparision check with that so how can I transform this data into a correct date format using only a query? I do NOT want to go in and start coding in this site. It's a live site and I'm afaid anything I do will break it.

It's MySQL 3.23.57 and all I have access to is PHPMyAdmin. PLEASE help me!!! Someone work a little query magic for me.

Transform Date To Mysql Timestamp Via CSV Import
I have a CSV file (from excel) with contains personal data, like names, adress, and birth date. The birth date is in this format yyyy-mm-dd. I would like to import this CSV file into my MYsql database with the "import CSV file" function that is build in PHPmyadmin.

The problem is that I need UNIX timestamps in my database, and not the date in yyy-mm-dd format. Code:

Java Code Or Pseudo Code For "Outer Join"
I want to implement the "outer join" functionality in Java. Can
somebody explain the pseudo code for the same. OR what needs to be done
to extend the hash-join Java code of equijoin.

Transform Or Pivot(?) Or Crosstab(?) Table In MySQL. One Data Table.
I have a table in the form:

year | season | hits

2004 | Summer | 42
2004 | Autumn | 43
2005 | Spring | 51
2005 | Summer | 52
2005 | Autumn | 53
2006 | Spring | 61

I want to generate a transform or pivot or crosstab - I'm not sure what the correct term is - to return the data in form

year | Spring | Summer | Autumn
2004 | ------ | ----42 | ----43
2005 | ----51 | ----52 | ----53
2006 | ----61 | ------ | ------

Could anyone suggest a mysql query to generate the required output?

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
)

Transform SubSelect In OUTER JOIN

maybe I'm simply to dump but I could not transform this SQL-Statmentwhich uses a Sub-select and create on that uses an 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 ....