MYSQL :: Subquery Help.

Subquery Or Correlated Subquery Help
I need to develop a sql that uses the results from the first Query to find data in the second Query. Then the results of the second query to find the final results of the third Query. I’m also wondering if I should try to just link all these tables together instead of Subqueries or Correlated Query.

First Query
select

ACCOUNT_ID,
ACCOUNT_TYPE_C,
PAT_ID

from PAT_ACCT_CVG

where ACCOUNT_TYPE_C in (120103,120104,120101)

Second Query

SELECT

PAT_CVG_FILE_ORDER.PAT_ID,
PAT_CVG_FILE_ORDER.LINE,
COVERAGE.COVERAGE_ID,
COVERAGE.CVG_EFF_DT,
COVERAGE.CVG_TERM_DT

FROMPAT_CVG_FILE_ORDER
LEFT OUTER JOIN COVERAGE
ONCOVERAGE.COVERAGE_ID = PAT_CVG_FILE_ORDER.COVERAGE_ID

Where coverage.payor_id = ?'

Third Query

select

TRAN.ORIG_SERVICE_DATE
TRAN.TRAN_TYPE,
TRAN.INSURANCE_AMOUNT

from Tran

where TRAN.TRAN_TYPE = 1
and TRAN.INSURANCE_AMOUNT > 0
and TRAN.proc_ID in 1008,1009

(now I need to compare the dates on this query to make sure that the TRAN.ORIG_SERVICE_DATE is within the COVERAGE.CVG_EFF_DT, COVERAGE.CVG_TERM_DT ( dates of the second query)

Subquery Help
I am using mysql 4.0 which does not support subquerys.How can i rewrite the below query using joins for mysql 4.0
select * from t1 where t1.eid not in(select eid from t2)

Using NOT IN In A Subquery
I am using MySQL version 4.0.27-standard

everytime i use "not in" in the subquery:

LIKE ANY + Subquery
I'm trying to get the following statement to work:

SELECT * FROM discountItems di
WHERE di.name LIKE ANY
(SELECT lsw.word
FROM ifDefinedSearchWords dsw
join ifLinkedSearchWords lsw on lsw.fIFEntityID = dsw.fIFEntityID
WHERE dsw.word like 'schoggi')

It is supposed to find some words in a subquery as one row, and then search another table for records matching any of those words. I get the following error message:
#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 .....

Subquery
Hi, wanted to use a subquery, as in:

SELECT * FROM pending WHERE username = (SELECT username FROM ratings);

But have learned subqueries are only available in MySQL 4.21 or later...

So...

I've tried to use the following query:

Code:

SELECT ratings.username,pending.username FROM
ratings,pending WHERE ratings.username="jjfjunk" OR
pending.username="jjfjunk";

But this returns an empty set, even though I know that the username exists in the ratings table.

Subquery
Can help to find any sytanx error from the following command?

select IMG_ID
FROM image
WHERE WORM_Name IN (SELECT WORM_Name FROM worm WHERE WORM_Age='adult')

I don't know where is wrong of above, but always got the sytanx error messange after I type in the above commands.

Subquery Help
I am pretty sure I have the syntax totally wrong.. but you can probably get an idea of what I want to do.

Code:

INSERT INTO library (tape_id,number) VALUES('DDFA3','433145')
WHERE DDFA3 NOT IN (select tape_id from library where used = '1')";

I'm basically wanting to make sure that no record for that tape_id exists that is set to "used" before I insert another record.

Subquery
I'm having trouble getting the results I need from an sql query. I believe I can accomplish my task with a subquery however I can't get it to work. I have a table of book titles and a one to many table of books read (that holds many people via ID and the book via ID they read). Now I need to get a list of books left to read by a particular person from my join. So the query starts with a join between the books table and the books read table to get the book titles. I use a left join to get all the book titles even if they haven't been read. However I need it to filter out the books that the particular person has read and leave me with a list of books left to be read by the particular person. Since I'm using a join I need it to ignore the records of the other people so that it will include does books in the results while excluding the books that the particular person has read.

Sum() And Subquery
SELECT domainTable.name as domain, sum(NetworkTraffic.upload) as Upload, sum(NetworkTraffic.download) as Download, sum(NetworkTraffic.upload) as Upload2 sum(NetworkTraffic.download) as Download2
FROM `NetworkTraffic`
LEFT JOIN domainTable ON domainTable.domainId = NetworkTraffic.domainId
WHERE NetworkTraffic.protId=2 AND date between "2005-10-11" AND "2005-10-18" GROUP BY domainTable.name

I am using the left-join above to merge two tables (NetworkTraffic and domaintable) on domainId to find which domain to present. By doing this i summarize the download and upload from NetworkTraffic between 2005-10-11 and 2005-10-18, however i want to be able to summarize the traffic from one day, say 2005-10-12 in column 3 and 4 (Download2, Upload2). Is it possible to make a subquery in the sum-function or should i take another approach? I am using mysql server 4.0.18-max-debug

Yet Another Subquery Question (I Think)
I have done some looking into subqueries on the boards and at mysql.org, but I can not seem to find the answer to my problem. Maybe there is no solution maybe the solution is simple.

I have tried to construct a query for this problem, but everytime I do I run into a problem where I don't know where to go next. So, I don't have a query that I have been trying to display....

Problem:

With MySQL 4.1.21-standard running

I have the following tables;

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
`USER_ID` int(11) NOT NULL auto_increment,
`REGIONAL_ID` smallint(5) NOT NULL default Ɔ',
`TEAM_LEADER_ID` smallint(5) NOT NULL default Ɔ',
`username` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`access_level` smallint(2) NOT NULL default Ɔ',
`status` tinyint(1) NOT NULL default Ƈ',
`num_logins` int(11) NOT NULL default Ɔ',
`type` smallint(2) NOT NULL default Ɔ',
`last_accessed` bigint(20) NOT NULL default Ɔ',
`last_modified` bigint(20) NOT NULL default Ɔ',
`created` bigint(20) NOT NULL default Ɔ',
PRIMARY KEY (`USER_ID`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=464 DEFAULT CHARSET=latin1 AUTO_INCREMENT=464 ;

--
-- Table structure for table `profile_core`
--

CREATE TABLE `profile_core` (
`STUDENT_ID` int(11) NOT NULL auto_increment,
`AGENT_ID` int(11) NOT NULL default Ɔ',
`SUPERVISOR_USER_ID` int(11) NOT NULL default Ɔ',
`HOST_ID` int(11) NOT NULL default Ɔ',
`SCHOOL_ID` int(11) NOT NULL default Ɔ',
`type` enum('Normal','Tuition','Hidden','Pals','Private','Cancelled') NOT NULL default 'Normal',
`student_number` varchar(6) NOT NULL default Ɔ',
`first_name` varchar(255) NOT NULL default '',
`last_name` varchar(255) NOT NULL default '',
`city` varchar(255) NOT NULL default '',
`nationality` char(2) NOT NULL default '',
`gender` char(1) NOT NULL default '',
`birthdate` bigint(20) NOT NULL default Ɔ',
`is_esl` enum('true','false') NOT NULL default 'false',
`may_pay_tuition` enum('No','Yes') NOT NULL default 'No',
`archived` char(1) NOT NULL default Ɔ',
`archived_date` bigint(20) NOT NULL default Ɔ',
`comments` text NOT NULL,
`last_modified` bigint(20) NOT NULL default Ɔ',
PRIMARY KEY (`STUDENT_ID`),
UNIQUE KEY `student_number` (`student_number`)
) ENGINE=MyISAM AUTO_INCREMENT=1442 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1442 ;
There are a couple of types of users that you can pull from the users table. Regional Managers, State Managers and Area Coordinators are the ones I am mostly trying to get at.

Every Area Coordinator has a State Manager and a Regional Manager identified by TEAM_LEADER_ID, AND REGIONAL_ID in the `users` table. Every State Manager has a Regional Manager (REGIONAL_ID).

Every Student has a SUPERVISOR_USER_ID identified in the 'profile_core' table which is a direct relation to the USER_ID of the `users` table.

What I want to do is find all of the students that are under a user. It is easy, for me, when we talk about only seeing students that are under a Area Coordinator:

SELECT
STUDENT_ID,
CONCAT(CORE.first_name, ' ', CORE.last_name) AS StudentName
FROM
`profile_core` AS CORE
LEFT
JOIN `users` AS USERS
ON CORE.SUPERVISOR_USER_ID = USERS.USER_ID
WHERE USERS.USER_ID = 1
My problem comes when I want to pull up all of the students that are under a Regional (including all of the students that are under all of the Regionals State Managers and all of the students that are under the State Managers Area Coordinators). I hope that made sense.

Is it possible in one query? Or, will it take two?

Simple Subquery
I know the basics of sql but i'm really bad at the joins and everything, so i hope someone can help me out with this.

items:

+-------+-------+
| it_id | name |
+-------+-------+
| 1 | item1 |
| 2 | item2 |
| 3 | item3 |
+-------+-------+
subitems:

+----+------+-------+
| id | name | it_id |
+----+------+-------+
| 1 | a | 1 |
| 2 | b | 1 |
| 3 | c | 1 |
| 4 | d | 1 |
+----+------+-------+
now i tried this in my php

PHP

$result = mysql_query("SELECT name FROM subitems WHERE it_id in (SELECT it_id from items WHERE name='$name'");
echo '<select name="subitems" class="list">'
while ($line = mysql_fetch_array($result , MYSQL_ASSOC)) {
   foreach ($line as $subitem) {
      echo '<option value="'.$subitem.'">'.$subitem.'</option>'
   }
}
echo '</select>'

Need Help With Subquery Syntax
First, what I want to do:
I want records from table1, but only the ones that have a "yes" in columnX in table2 for the same memberID (foreign key).

I have two tables:

ind1b (indID, memberID, date, ...) and
reports (reportID, memberID, okay, date)

To select all rows in table ind1b I have this query:

$query = "SELECT * FROM ind1b WHERE memberID = '$memberID' ORDER BY datum ASC".
But I also need to check in table reports to see if there is a "yes" in column "okay" for the variable $memberID. Multiple rows (with different dates) can be returned.

So I added this subquery:

$query = "SELECT * FROM ind1b WHERE memberID = '$memberID' IN (SELECT reports.okay FROM reports WHERE reports.okay = 'Yes')
ORDER BY ind1b.date ASC";
This query gives no error run through phpMyAdmin â€“ but also returns no records. I think that I need the "date" in there somehow but can't figure out how and where.

Subquery Error
I'm receiving this error:

Quote:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Not sure why, I'm running MySQL Version 5.0.41-community-nt with WAMP5.

Any suggestions? It's a personal project that I would like to continue developing on localhost.

Subquery And Counting
I have a table called people in my database.

it looks like this

+----+------+
| id | name |
+----+------+
| 2 | bob |
| 1 | Jill |
| 3 | Jill |
| 4 | John |
| 5 | Jill |
+----+------+
I want to display the distinct name and how many times they are in the table
So like this

Jill - 3
bob - 1
John - 1

How would I go about doing this. I thought maybe something like this but not to sure

[code]
SELECT COUNT(name)
FROM people
WHERE name =
(SELECT DISTINCT name
FROM people);
[code]

Subquery Problem(s)
I have set up a database (MySql 4.1.21) with these tables and rows:

players
p_id (= player_salary.players_id)
team_id
div_id
conf_id
fname
lname
position
status

player_salary [1 to many]
player_id (= players.p_id)
yr
amount
type

Basically, the â€œplayersâ€ table holds some information for multiple players, and the â€œplayer_salaryâ€ table holds a playerâ€™s salary for each year of their contract.

What I am trying to do is display the playersâ€™ names and their total salary across the length of their contracts. So, I decided to throw all the relevant player data from the database into a multidimensional array, and then output it with PHP (5.1.6).

Hereâ€™s my query and

PHP

$query = array();

$query[] = 'SELECT DISTINCT *  ' FROM players, player_salary, (SELECT SUM( amount ) AS salary_total FROM player_salary GROUP BY player_salary.player_id) AS table01  GROUP BY players . p_id  ORDER BY players . lname ASC'

$rowSet = array();
for ($i = 0; $i < count($query); $i++) {
    $result = mysql_query($query[$i],$link) or die(mysql_error());
    while ($row = mysql_fetch_assoc($result)) {
        $rowSet[] = $row;
    }
    mysql_free_result($result);
}

echo '<table>'

foreach ($rowSet as $outer_key=>$single_array){
echo '<tr>'
    echo '<td>'.$rowSet[$outer_key]['lname']. ', '.$rowSet[$outer_key]['fname'].'</td>'
    echo '<td>'.$rowSet[$outer_key]['salary_total'].'</td>'
echo '</tr>'
}
echo '</table>'

And hereâ€™s where the problems start. While I can calculate the total salary for Johnny Doe, for some reason that same amount appears for John Doe as well (same with the values for â€˜yearâ€™ and â€˜amountâ€™ ). In reality Johnny Doeâ€™s total salary should be (5,000,000+3,000,000+3,000,000=11,000,000) and John Doeâ€™s salary should be (2,000,000) 2,000,000.

Hereâ€™s what the array looks like:

Array
(
[0] => Array
(
[p_id] => 1
[team_id] => 14
[div_id] => 2
[conf_id] => 1
[fname] => John
[lname] => Doe
[position] => F
[status] => active
[player_id] => 0
[yr] => 2007
[amount] => 5000000
[type] =>
[salary_total] => 11000000
)

[1] => Array
(
[p_id] => 0
[team_id] => 14
[div_id] => 2
[conf_id] => 1
[fname] => Johnny
[lname] => Doe
[position] => F
[status] => active
[player_id] => 0
[yr] => 2007
[amount] => 5000000
[type] =>
[salary_total] => 11000000
)

)

So, Iâ€™m wondering if someone can tell me what Iâ€™m doing wrong?

Iâ€™m pretty sure itâ€™s the query because when I try the subquery (SELECT SUM( amount ) AS salary_total FROM player_salary GROUP BY player_salary.player_id) in phpMyAdmin, it works. When I put it all together I get the aforementioned results.

Subquery With Many Results?
So i'm looking for a way to have a query with a subquery, but the subquery returns more than one item.

ex.
SELECT * FROM menu where parent_id = (SELECT menu_id FROM menu WHERE parent_id = 0)

the subquery will return [22,30,33]
I would like the top query to execute logically as follows:

SELECT * FROM menu where parent_id = 22 OR parent_id = 30 OR parent_id = 33

I can't predict the count of the subquery.

I can do this with loops in my php but I have a feeling that it is possible to do this with a single SQL statement (multi subqueries).

Request Help For Subquery
I have a table that records targets and the time it appears on a
display. What I would like to do is to report the time difference for
each individual target from the initial appearance to the subsequent
one, and the time difference from the subsequent one to the next, and
so on.

So how do I put these these all together to produce one query:

for each "select distinct target from display"
for number of rows -1 with target
"select timeX - timeY from (subquery for distinct target) where
(X,Y = subsequent, initial times)"

Subquery Or Join ?
I've got two tables

First has
ID,Name,Descr

Second:
ID,IDFirst,Price,ChangeDate

in first i've got some products, in second i've got a prices for that products (one product can have more than one price - 'cause i'm using it in another tables)
i'd like to show products list with most the newest price. is this possible without subquery?

Subquery (not Exists)
I've got some sort of syntax problem that doesn't seem to make a lot
of sense. I'm developing a Categories Theory application and because
of that I need to make big, and by "big" I mean HORRIBLY HUGE queries.
That one has 54 lines and 3 subqueries (only the first one is shown so
that I won't scare people off :)).

The thing is, I don't seem to be getting the hang of how to do
subqueries. The syntax seems fine... But it'll still always say the
same thing:

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 'exists ( select * from objeto c, morfismo f1, morfismo f2
(It's MySQL 4.0.11a-gamma)

The only difference I see between the code below and the mysql.com
documentation is that my subqueries aren't alone in their "where"
clauses... Aside from that, they seem pretty much okay. Am I missing
something?.....

Using Union In A Subquery?
I'm trying to make a query that fetches messages that were created by one of your friends (friendships are stored in a separate table) and was thinking this query would possibly do the trick:

SELECT *
FROM public_messages
WHERE author_id IN
(
(SELECT friend_from FROM friendships WHERE friend_to=1 AND pending=0)
UNION
(SELECT from_to FROM friendships WHERE friend_from=1 AND pending=0)
)

Where the user's id is 1... Running this query gives an error of:

#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 'UNION ( SELECT from_to FROM friendships WHERE friend_from = 1 AND pending = 0 ) ' at line 1

and of course it works fine without the union... Is such a thing possible in one query?

MySQL Subquery Using NOT IN
I need a little help rewriting a subquery in a MySQL db that does not support this type of query.

I have read the manual about how to convert a NOT IN to a left join and it makes good sense, but I have on small addition to it that I cannot get to work.

The manual states that this is the correct SQL for a NOT IN subquery:

Update With Subquery?
I have two tables and I want to update one with data from the other

TableA
id description date

TableB
id counter date

I want to update counter in TableB with its current value minus a count from tableA

UPDATE TableB set counter = counter - (SELECT count(id) WHERE description = 'blabla')

But I only want to do this on the date columns are equal. For example date in TableB is 2007-03-13, and counter should be updated with its value minus the selection from TableA where date is the same. But I want to do this on all rows for all dates in one query. How do I do that? Add a where to the subquery ie "WHERE TableB.date = TableA.date"? Is that correct?

Count With Subquery
I've a problem with a count with a 3.23.58 sql version...

Here's my sql code (short version and normal version below)...

Subquery With IN Clause
I have a table full of subscriptions to a service. this is our second year, and i want to see how many people returned for a new season. I am having a hard time finding a solution to this problem. so far i have this...

SELECT
count(*)
FROM product
WHERE
DATE_FORMAT(insert_date,"%Y")='2005'
AND
payer_email IN
(SELECT DISTINCT
payer_email
FROM product
WHERE DATE_FORMAT(insert_date,'%Y')='2004')

but the page just never loads when i run that. Does anyone have any ideas as to what's wrong with my query, or a better query i might make?

How To Do A Subquery With MySQL 4.0 ?
I couldn;t upgrade to mysql 4.1 for some reason, so I am forced to use mysql 4.0.

I try to change my database from pgSQl to mySQL. But the proble is how to make a query like :

"SELECT field FROM table WHERE (select count(*) from table2)<5 "

which is very easy under pgSQL but not allowed under mysql because the subqueries seem not to be allowed...

Row Subquery In Fieldlist
I am trying to figure out how to shorten my SQL.

I know I can do this:
SELECT (SELECT some_single_field FROM somewhere) AS field_alias FROM parent_table

But I want to be able to use the subquery to return 2 values - something like this, (pseudo-code):

(SELECT some_field, some_other_field FROM somwhere) AS field_1, field_2

I know how to do comparisons in the WHERE clause using multiple columns, but I can't seem to figure it out in the beginning of the select. Does anyone know how this is done?

DELETE And Subquery
Here is my DELETE statement that should work but doesn't due to "Currently, you cannot delete from a table and select from the same table in a subquery. "

DELETE FROM history WHERE eventTime = (SELECT MIN(eventTime) from history)

I found information about selecting the max with a subquery here:

Select Subquery
I am having difficulty in the following subquery. I keep getting the syntax error at 'SELECT count(*)'.

SELECT ID, organization, (SELECT count(*) FROM Invoices WHERE paid = 'N') AS paid
FROM clients

What I am trying to do is to get a list of clients and also count any unpaid invoices.

Does anyone know what I am doing wrong.

How To Do A Subquery With MySQL 4.0 ?
I couldn;t upgrade to mysql 4.1 for some reason, so I am forced to use mysql 4.0.

I try to change my database from pgSQl to mySQL

But the proble is how to make a query like :

"SELECT field FROM table WHERE (select count(*) from table2)<5 "

which is very easy under pgSQL but not allowed under mysql because the subqueries seem not to be allowed ...

SubQuery To Add A Column
I have the following SQL which is the best I can do to illustrate what I am trying to accomplish:

select column_name as 'name',
column_default as 'default',
column_type as 'type',
(select column_name from maillists
where listname="winserver") as 'data'
from information_schema.columns
where table_name='maillists';

Essentially, I am trying to get a "description" of my table along with an additional column of the current value for each field in the table.

What I am getting instead is a repeat of the column name in the data column....

Subquery Not Working
CODESELECT parexel_staff.name, parexel_skills.skill, parexel_activity.date
FROM parexel_staff, parexel_skills, parexel_activity
WHERE parexel_staff.name = parexel_activity.name
AND parexel_activity.skill = parexel_skills.skill
AND parexel_skills.skill = 'CGI'
AND CURRENT_DATE > (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
AND parexel_staff.name NOT IN
(
SELECT parexel_staff.name
FROM parexel_staff, parexel_skills, parexel_activity
WHERE parexel_staff.name = parexel_activity.name
AND parexel_activity.skill = parexel_skills.skill
AND parexel_skills.skill = 'CGI'
AND CURRENT_DATE < (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
);

Making The Right Subquery
I've been trying to wrap my head around this one for a while, and while it would be pretty easy to do using a loop in PHP I'd like to keep it in a single SQL statement if possible.

I have a table of comments, very much like you would store comments in for a blog. It goes something like this:

idCOMMENT | FK_LIST_ID | COM_Posted | COM_Comment
autoincrement| foreign key of post | Timestamp | Text for Comment

I only want to keep the most recent 30 or so comments on each listing so I need to create a statement that will delete any excess comments.
I could loop a statement like this in PHP:
DELETE FROM COMMENT WHERE idCOMMENT IN (SELECT idCOMMENT FROM COMMENT WHERE FK_LIST_ID=(variable from PHP) ORDER BY COM_Posted Desc LIMIT 30, 10^10)

But there will be thousands of listings, (it's and that's going to be rough on the server to make thousands of calls to it each night with the maintenance script. I think there must be a way to do this in pure SQL, just send one SQL statement to the server and that's it, but can't figure it out. Can you point me in the right direction?

Subquery Count
I have this sql statement where its selecting a bunch of folders from the folder table (userfolders) and then its counting how many messages are in that folder in table (privatemessages) well I need to take this sql statement one step further and grab a column from the privatemessage folder also called pstatus, however you can't use more then 1 column in this subquery below...

Suport Subquery
which mysql version supports subquery?
mysql 4.1 supports subquery?

Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished)

select date from temps where date in (select distinct date from observations where camera like "a")

The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?

Correlated Subquery
SELECT MAX(e1.score) AS high_score
, e1.dept
, (SELECT e2.emp_no
FROM Employee_Evaluations AS e2
WHERE e2.dept = e1.dept
AND e2.score = MAX(e1.score)) AS emp_no
FROM Employee_Evaluations AS e1
GROUP
BY e1.dept

It doesn't work in MySQL (I've tried 4.1 and 5.0), but it seems like it should work and if I'm not mistaken, it's valid SQL99. Can anyone confirm this? I don't need help writing a compatible version, I've already done that - just curious if this little bugger is standards compliant.

Subquery As Join
I have the following query, which works perfectly on my developpement computer:

UPDATE writings SET num_votes=(SELECT COUNT(vote) FROM votes WHERE writing_id=id), rating=(SELECT AVG(vote) FROM votes WHERE writing_id=id);

Unfortunately, my host doesn't seem to allow subqueries. Is there a way to do this with joins? I've heard that joins are faster anyhow.

Subquery Issue
I have a query at sql.pastebin.com/d3398502e and it's a bit of a monster (sorry, it says I can't post the actual link because I'm a new user, I assume it's a spam precaution, so please copy and paste).
It's a search query for a hotel site, like expedia, but it needs the ability to get different prices for children in a room based on their age.
Each hotel can have age groups, so it needs to figure out how many children are in each age group for that hotel (which works, that's all the UNIONs), and then apply the price associated with that age group to the number of children for that hotel for that date.
That's where I'm stuck since the price data for that date is not available in the subquery.
The way I have it now it's selecting all dates that have child prices applied and adding those together, rather than just the one date.

I Need Help With A Query/subquery Tangle
I started out trying to do everything at once and really ran into problems (5 tables! my head hurts), so i broke the original query in half to get:

$query ="SELECT
members.id,
members.lname,
members.fname,
member_email.email,
member_phone.phone,
committees.id,
committees.name,
committees.description,
committees.chair_id,
committees.staff_id,
staff.phone,
staff.email,
staff.id,
staff.name
FROM committees, staff, members, member_email, member_phone
WHERE committees.name='Test'
AND members.email_id=member_email.id
AND members.phone_id=member_phone.id
AND staff.id=committees.staff_id
AND members.id=committees.chair_id";

//ORDER BY members.lname DESC
$result= mysql_query($query)
or die ("could not retrieve information"); ............

Need Help Converting Subquery Into Join
I need a bit help,

Table structure
Member
------
id

Registration
------------
id
meeting_id
member_id

Using subQuery:
Select m.* from member m where m.id NOT IN ( select r.member_id from Registration where meeting_id != XXX )

Since I'm using old version of mysql v4.0 which doesnt support subquery, so that query above wont work.
Is there anyway I can rewrite that query above using (JOIN?) .. and make it work?

I tried
Select m.* from member m left join registration r on m.id = r.member_id where
r.id is null and r.meeting_id != XXX
It doesnt work because of the meeting_id constraint.

Correlated Subquery In From Clause - Help!
I have read that correlated subqueries in from clauses are illegal as the inner query is evaluated before the outside query. Anyone know a getaround?

My query is as follows:

SELECT
lodging_master. * ,
lodging_type.lod_type_name_mil,
count(lod_id) AS allocations
FROM lodging_master
INNER JOIN index_page_allocations ON lod_id = index_page_alloc_lod_id
INNER JOIN index_pages ON index_page_alloc_index_page_id = index_page_id
INNER JOIN lodging_links_allocation ON index_page_alloc_lod_id = lod_link_alloc_lod_id
INNER JOIN lodging_links AS t2 ON lod_link_alloc_link_id = link_id
INNER JOIN sources ON link_source_id = source_id
LEFT JOIN lodging_type ON lodging_master.lod_type_id = lodging_type.lod_type_id

LEFT JOIN (

SELECT xml_link_id, xml_result_value
FROM
xml_results
WHERE
xml_room_occupancy IN(1,2) AND
xml_nights = 1 AND
xml_date != &#55612;&#57200;-00-00' AND
xml_question_id != 0 AND
xml_result_value != 'X' AND
xml_link_id = t2.link_id
ORDER BY CAST(xml_result_value AS UNSIGNED)
) as t1 ON t1.xml_link_id = link_id

WHERE
index_page_id = 14278
AND lod_link_alloc_status = 'ok' AND
link_status_mil IN('ok', 'hidden') AND
source_status_mil = 'ok'
AND lod_status_mil = 'ok'
GROUP BY lod_id
ORDER BY lod_id LIMIT 0, 15
I keep getting the error:

#1109 - Unknown table 't2' in where clause

Deleting Based On Subquery
i need to DELETE the results of this query :

SELECT bizName, COUNT( bizName ) AS name_count
FROM `datanewest` GROUP BY bizName
HAVING name_count >1
I use this to remove double entries. But when i run something lie this

DELETE FROM `datanewest` WHERE bizName IN (SELECT bizName, COUNT( bizName ) AS name_count
FROM `datanewest` GROUP BY bizName
HAVING name_count >1)
i get this error :

#1241 - Operand should contain 1 column(s)

Help With Subquery / Group Concat
I've attached some data from which I want to extract individual itinerary ids and show the ships which go to those destinations. The problem is that the destination ids are stored as comma separated values in a single field and there are rows which duplicate the same associations between the ship and destination ids. I can do:

SELECT ship_id, GROUP_CONCAT(DISTINCT destination_ids) FROM itineries GROUP BY ship_id;

and get a list of destination ids for each ship but what I actually want is a list of ships for each destination Id.

Can somebody give me some pointers on how to achieve this? I think it might be possible with a subquery but am not sure how to go about this. MYSQL version is 4.1.20.

Get Multiple Rows Using Subquery?
I have a query similar to the following, however i'd like to get another row from the subquerys - currency.

SELECT *,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 19 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_1,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 13 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_2,

(SELECT xml_result_value FROM lodging_links_allocation
INNER JOIN xml_results ON lod_link_alloc_link_id = xml_link_id
AND xml_result_value != 'X' AND xml_nights = 1 AND xml_source_id = 12 WHERE lod_link_alloc_lod_id = lod_id
ORDER BY CAST(xml_result_value AS UNSIGNED) LIMIT 0, 1) as price_from_3

FROM (

SELECT *
FROM
[..snip..]
GROUP BY lodging_master.lod_id
ORDER BY RAND(&#55614;&#57159;-12-17')

) as foo
HAVING (price_from_1 > 0) && (price_from_2 > 0) && (price_from_3 > 0)
LIMIT 0 , 30

Subquery In IN() Not Returning All Possible Values
I'm sure there is some silly little mistake in here somewhere, but I can't find it. This is my first stab at subqueries, so I'm not at all surprised that I'm not getting the correct results.....

GROUP_CONCAT In Subquery - Error
HTML
SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F._id = C._id
AND C.fieldC IN (1, 2, 3)
I want to incorporate it into another query:

HTML
SELECT A.fieldA, SUM(A.fieldB) AS total,
GROUP_CONCAT(A.fieldC) AS listing,
(SELECT GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F.i_id = C.i_id
AND C.fieldC IN (listing)
) AS items
FROM TABLEA A
GROUP BY A.fieldA
ORDER BY total
But hat query throws the error:

Quote:

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 GROUP_CONCAT(F.name)
FROM TABLEB F, TABLEC C
WHERE F.i_id at line 2

Count Using Subquery/group By/etc?
I have a database witha table of jobs and a table of advertisers. Jobs can be assigned an advertiser from which they came, via the advertiser_id.

So what I want to do is get my result list of advertisers with a count of how many jobs for each one.

My advertisers table looks like this:

CREATE TABLE `advertiser` (
`advertiser_id` mediumint(2) unsigned NOT NULL auto_increment,
`advertiser_nicename` varchar(127) collate latin1_general_ci NOT NULL,
`advertiser_name` varchar(127) collate latin1_general_ci NOT NULL,
`advertiser_publisher` varchar(31) collate latin1_general_ci default NULL,
PRIMARY KEY (`advertiser_id`)
);
(I'm not going to normalize advertiser_publisher. :P )

And I'll just say that the jobs table looks like this for simplicity:

CREATE TABLE `jobs` (
`job_id` int(10) unsigned NOT NULL auto_increment,
`advertiser_id` mediumint(2) unsigned NULL,
PRIMARY KEY (`job_id`)
);
So I have my list of advertisers generated as such:

PHP

$advertisers = $DB->get_results( "SELECT * FROM `advertiser` ORDER BY `advertiser_publisher`, `advertiser_name` ASC" );

And I could be very silly and add a count such as this:

PHP

foreach ( $advertisers as $id => $advertiser ) { $advertisers[$id]->advertiser_jobs_count = $DB->get_results( "SELECT COUNT(advertiser_id) FROM `jobs` WHERE `advertiser_id` = $advertiser->advertiser_id", 'NUM', FALSE, TRUE ); }

(Ok dont worry about all the php in there, i think its fairly self explanatory what is going on. )

So obviously I don't want to do that because its totally ridiculous and I'm sure that there would be an sql only method of doing it.

Subquery Takes Forever
I have two problems where I'm trying to retrieve data using a query.

Part 1:

I am doing a simple sub query which is on a two data sets with no more than 3000 rows in total. The query is taking 28 seconds to execute? Why I don't have a clue?

Quote:

'Select Title FROM products
WHERE Product_ID IN
(SELECT Product_ID FROM order_items WHERE Order_ID="'.$id.'")'

Part 2:

When I have solved the above I'm looking to do a join, but don't know how to approach it ? Basically I'm trying to get a complete data set for a product/order like the following:

SELECT * (which includes product_id) from items
AND SELECT Title from products where product_id =(the product_id is retrieved from items); // using a join