Ranking Student Grade? With Subquery/subselect?

Ranking Scores
I need to create a query that will sort a bunch of scores in ascending order and then add a column with their rank code from 1 to however many is in the table.
the link below shows an example image of what i need to happen.

---

A Ranking Leaderboard
I am trying to design and implement an online leaderboard in MySQL and PHP.  Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db.  It is the query you write/code which does the sorting.  How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc.  However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical.  You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking?  Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before.  I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

---

Ranking Results
Im trying to display baseball team stats, along with the ranking for that particular league.
for example, there are 5 teams in the league, Team A has 12 HR, B has 15 HR, C has 3 HR, D has 3, and E has 20.
I want to show on each individual team page their stat, with the rtanking for the league.

---

Ranking MySQL
I have a query that extracts the last 7 records from a database based on its criteria and have it ordered by its date. What i want to do is add a column in the select statement and put in the numbers from 1 to 7 into the column in this added column.

I'm using the Space(1) as Number function but i am wondering how do I put 1 to 7 in the table. Its pretty much like a ranking system.

---

Ranking Users
i'm trying to rank the users in our site.
table contains:
- userid
- userrates
and with thousands of records.
member with the highest rate goes #1, next is #2 and so on.
i want the query to be like:
Code:
<something goes here> WHERE userid = '$userid' order by userrates desc
from the code above it will show the rank of this user.
then the next user logsin and it shows again his/her ranking.
hope someone understands this
any input is appreaciated

---

Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts?
And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.

---

Ranking Leaderboard In MySQL!?
I am trying to design and implement an online leaderboard in MySQL and PHP. Maybe it is because of lack experience in this field but I am really confused on the basics on how this could be achieved.

Correct me if I'm wrong but data is not stored in any order as such in a SQL db. It is the query you write/code which does the sorting. How then is it possible to have a leaderboard with rankings on?

For example if I had a basic table with fields of name and points.

You could write a basic query to display the top 10 (or whatever limit you choose) users by points, ascending or desc, etc. However if you then have a leaderboard with thousands of users on a leaderboard, this wouldn't be practical. You could obviously pull an individuals record up by using WHERE and the name.

How would I get SQL to output the users ranking? Surely I cannot do it with an extra field called ranking as everytime the ranking changed ALL records would need to be updated...

Surely this kind of thing has been done before. I just can't imagine doing this with SQL due to the nature data is stored and then later sorted.

---

Ranking Query (self Join)
I would like to get a rank (row # of ordered results) of an article based on its average rating in relation to all other articles. I have a ratings table that holds all of the individual votes cast by users (ratingID, articleID, userID, rating). So I need to order the articles by AVG(ratings.rating) and then come up with an individual article's row # to find its ranking.

I know this involves joining the ratings table on itself with a greater than or less than operator, but i'm completely lost when it comes to self-joins. I know this is easy, but there is a small trick to it and i'm clueless.

---

Ranking For Multiple Columns
A query in my application return several columns with amounts, Now i need to add a ranking to all those columns seperatly.

So the result should be something like this:

ID     Amount1  Ranking1  Amount2 Ranking2
----   -------- --------- ------- --------
135     123.34      4       23.00     3
184     160.23      1       60.89     2
845     140.22      3      100.20     1
987     155.00      2        1.20     4

The ID isnt important here.

The only solution i can think of now is to create a (temp) table, populate it with the query, and then sort it for each ranking and fill the ranking one by one.

Is there a way to get the ranking filled in  the same pass as the amounts are calculated?

---

Sort And Give Ranking
let say i've have these following table.

tblA
name | marks
-----------------
robert | 39
johnny | 78
bruce | 23
elena | 56
halim | 23

formula, if same marks (see bruce and halim), sort them by name
how to query? expected output showing as below:

no | name | marks
----------------------------
1 | johnny | 78
2 | elena | 56
3 | robert | 39
4 | bruce | 23
5 | halim | 23

---

Sorting And Put Ranking In Field
I have 3 field in my table Ranking, Name and Score. Is it possible to sort all record to order by Score and put Ranking no. to each record by using query not much?

---

Scoreboard Ranking System
I have created a high score table that will potentially store > 10000 user scores, and need to determine ranking for items pulled out. Ther have been posts detailing how to do this when selecting ALL records, but in my case, I need to pull out specific rows, and determine their rank against the entire table.
For instance, a user gets a high score that would rank them at #1200, I need to pull out their score, as well as the 3 above and 3 below. I accomplished this by storing the insert_id, and selecting rows where the score is above and below (in seperate queries), but this makes it quite impossible to get a rank.
An alternative method is having a rank column and updating it every time a new score is added, but if it cant be done in one query, it would be a very inefficient method.
Does anyone have any solutions on how to approach this? I am using mysql 3.23, so I dont have use of sub-queries.

---

Ranking System: Retrieving Results Around Your Rank
I'm building a ranking system for a game. I'm trying to determine the best way to select a players rank, and then display the records around that players ranking.

For instance:

Username | Rank
steve | 6000
bob | 5000
jane | 4000
chris | 3000
brian | 2000
tim | 1000
molly | 0

Assuming chris is the current player, I want to select his rank, and then run a query that returns the two players ranked higher than him, and the two players ranked lower than him. So the query would return the following:


bob | 5000
jane | 4000
chris | 3000 //I'd rather leave his own rank out.. but it's not bad like this.
brian | 2000
tim | 1000

What I can't figure out how to do, is determine the row that is specific to Chris's rank. I would need to define a starting point (-2 rows from Chris), and an end point (+2 rows from Chris), order by and limit the results... what I can't figure out though, is how to get the specific row number to start from.

For instance, lets say Chris is row 583 of my database... how do I retrieve that number?

---

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)

---

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?

---

Full-Text Search: Truncation Operator And Relevance Ranking?
We would like to begin supporting the truncation operator on our website's search engine--however, we still require the results to be sorted by relevance. If a client enters "televis*" into the search engine, and we perform the search in boolean mode, pages containing "television", "televise", "televised", etc will be returned. However, because it is a boolean mode search, all rows will have a relevancy value of 1.

A user-provided "workaround" on the MySQL site said to use the same keywords but NOT in boolean mode to get a usable relevance ranking. However, a standard fulltext search for "televis" or "televis*" will return no rows.

---

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?

---

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

---

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

---

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 &#390;',
as_blogger int(11) NOT NULL default &#390;',
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 = &#3989;' 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.

---

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

---

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)

---

Subquery Help.
I am using the following but then I rembered I am getting the readyPrinted_id from the select statement!! How can I fix it so it gets the readyPrinted_id and then performs the AND with that same ID?

SELECT
`sub_name`
, `readyPrinted_name`
, `readyPrinted_id`
FROM sub s,
readyPrinted r
WHERE s.sub_id = $sub_id
AND s.readyPrinted_id = r.readyPrinted_id
AND r.readyPrinted_id = readyPrinted_id

---

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 &#390;',
`TEAM_LEADER_ID` smallint(5) NOT NULL default &#390;',
`username` varchar(255) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`access_level` smallint(2) NOT NULL default &#390;',
`status` tinyint(1) NOT NULL default &#391;',
`num_logins` int(11) NOT NULL default &#390;',
`type` smallint(2) NOT NULL default &#390;',
`last_accessed` bigint(20) NOT NULL default &#390;',
`last_modified` bigint(20) NOT NULL default &#390;',
`created` bigint(20) NOT NULL default &#390;',
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 &#390;',
`SUPERVISOR_USER_ID` int(11) NOT NULL default &#390;',
`HOST_ID` int(11) NOT NULL default &#390;',
`SCHOOL_ID` int(11) NOT NULL default &#390;',
`type` enum('Normal','Tuition','Hidden','Pals','Private','Cancelled') NOT NULL default 'Normal',
`student_number` varchar(6) NOT NULL default &#390;',
`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 &#390;',
`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 &#390;',
`archived_date` bigint(20) NOT NULL default &#390;',
`comments` text NOT NULL,
`last_modified` bigint(20) NOT NULL default &#390;',
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.

---