MYSQL :: Get Last Records Details With A Subselect?

Getting All Details For Minimum Value
I'm sure this can't be that hard, but suppose I have tables for category and product, and a link table, and I want to get the minimum priced product for each category:

select c.categoryID,c.categoryName, MIN(price) as min_price
FROM cat c
JOIN category_product cp ON c.categoryID=cp.categoryID
JOIN product p ON cp.productID=p.productID
GROUP BY c.categoryID
That's fine, but how about if I wanted to get further information about that minimum priced product? I've been doing what looks like awkward things with subqueries, like this:

SELECT cat.*,(SELECT productName FROM product WHERE product.price=cat.min_price LIMIT 1) as prodname
FROM(
select c.categoryID,c.categoryName, MIN(price) as min_price
FROM cat c
JOIN category_product cp ON c.categoryID=cp.categoryID
JOIN product p ON cp.productID=p.productID
GROUP BY c.categoryID
) as cat
or this :

SELECT cat.*,p.productID, p.productName
FROM(
select c.categoryID,c.categoryName, MIN(price) as min_price
FROM cat c
JOIN category_product cp ON c.categoryID=cp.categoryID
JOIN product p ON cp.productID=p.productID
GROUP BY c.categoryID
) as cat
LEFT JOIN product p ON cat.min_price=p.price
GROUP BY cat.categoryID

Product Details
I am currently developing an e-commerce website. Each product comes in many colours and sizes. I have therefore created the following tables:

1. product (contains basic information about each product)
2. colour (contains a list of all possible colours)
3. size (contains a list of all possible sizes)
4. product_colour (contains matching product and colours ids)
5. product_size (contains matiching product and size ids)

I now need to create a query which returns the basic information, the available colours, and the available sizes for a particular product id.

How To Add Several Details To One Field In Table?
I have been adding my info into my database so far like this:

INSERT INTO mytable SET
Name = "Harry Bucket",
Products = "chair",
Suburb = "sydney",
ResLink = "harrys_page.php";

BUT, harry has more then just a chair under his product listing... how would i add several products that relate to the 1 field?

Entering Session Details
I want to get the session details (username) of a person loged in. And want that details to be entered to a separate table in he same data base. how can i do that. A coding for that would e perfect.

Concrete Data & Details
I'm reading available documentations and whitepapers, but can't reach specific answers to my questions:
1. What is the minimum requirements (cpu,memeory..) for mySql.
2. What is the advatanges in using the embedded version over the regular? And I'm looking for specific data, not 'global statements', like - how small is the embedded version's footprint?

Sharing Login Details
There is no decent image gallery that can be put with Joomla or Mambo CMS so I am wanting to add a separate pop up page to use a 4images (http://4homepages.de) image gallery.

HOWEVER,

I don't want my users to log in twice to two different databases!

I'm sure there is a way of doing this. (both the CMS and the Gallery are on the same server, and they can both use the same Database.

Sharing Login Details
There is no decent image gallery that can be put with Joomla or Mambo CMS so I am wanting to add a separate pop up page to use a 4images (http://4homepages.de) image gallery.
I don't want my users to log in twice to two different databases!
I'm sure there is a way of doing this. (both the CMS and the Gallery are on the same server, and they can both use the same Database. )

Deciphering Connection Details From PhpMyAdmin
I have no details as to how to connect to my database although I am able to create one through phpmyadmin. While I twiddle my thumbs waiting for my host to send me some connection details is it possible to maybe work out what they are? I'd guess my username and password are ones I chose and are the same for the whole kaboodle so it's the address of the db I'm looking for, any clues?

How To Save People's Details Using MySQL?
Basically i want people to enter in there name and contact details and an answer to a simple question. I then want that information to be stored somewhere, so i can retrieve it once a month. Do i use Microsoft access?

Finding User Details Inside UDF
i heared that its possible to get user details inside
the UDf by adding the mysql_priv.h header file.

But where will this file be available.
i am using MySql 3.23.52 on linux8.0

when i try to compile it says :"mysql_priv" file not
found.

If i can include this file, i can get the thread
constructor called...

Can MySQL Return Details On What Specifically Trigged A Match?
Can MySQL return details on what specifically matched in each row for a FULLTEXT search or WHERE clause?

Say the query "SELECT id FROM users WHERE (name LIKE '%Sarah%') AND (age BETWEEN 19 AND 29)" returns 12 rows. Is there a way for mysql to tell me whether name or age triggered the match?

Getting Rid Of Subselect
I have a table, which -- simplified -- looks like this:

create table access_logs (
session_id varchar(32),
request_uri varchar(32)
);
Each pageview logs the users session-id + the request-uri. Now, to determine how many visitors followed a specific path, I need to select the number of sessions, which have a row including specific request_uri.

This is my own feeble attempt, but I have a feeling that this could be rewritten to get rid of the subselects:

select session_id
from access_logs
where session_id in (select session_id
from access_logs
where request_uri = "landing-page.php")
and session_id in (select session_id
from access_logs
where request_uri = "exit-page.php")
group by session_id;

Getting Around Subselect
My knowledge of SQL is basic so I need some help developing a query. Suppose we have a table called BID where each row is a bid on an item up for auction. The relevant columns are BID_ID which is the primary key, ITEM_ID which identifies the item, and a BID_DATE which records the datetime of the bid.

I would like to find the most recent bid for each distinct ITEM_ID in the table. I've worked out the query below which seems to do the job. However, I need to find a query that will work on a pre-4.1 server which does not support subqueries. Is there a way to re-state this query without using a subselect? Perhaps using some kind of join?

SELECT *
FROM BID, (SELECT ITEM_ID AS IID, max(BID_DATE) as MAXDATE
FROM BID
GROUP BY ITEM_ID) as MAXDATES
WHERE
(ITEM_ID=IID) and (BID_DATE=MAXDATE);

Subselect
I had some SQL calls which worked fine on a v4.1 server and now I've moved to another one which is 4.0.24 and certain subselects no longer work. Is there any basic way to convert statements such as this:

SELECT a.name, (SELECT COUNT(*) FROM table2 AS b WHERE b.id=a.id) as count FROM table1 AS a So that it conforms to the 4.0.x standard?

Subselect
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:

locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list. Code:

Subselect
Let's say i got this query:
select user.id, (select count(*) as posts_number from posts where posts.user_id = user.id), and some other fields, and a lot of joins here.Is it any way to *say* to mysql that the current user.id selected, is the one in the subselect ?
(the one from select "user.id" and the one in where ... = "user.id")

Subselect
Unless I'm wrong, here's a way to do a subquery (inner join two tables, then inner join the resulting table with a third table). It takes advantage of the two different ways of expressing an inner join ("INNER JOIN", and "t1, t2 WHERE...") to express two separate inner joins within a single statement.
SELECT p.p_id, v2.v_name FROM t_project p, t_volunteer v INNER JOIN t_volunteer v2 ON p.p_id=v2.p_id WHERE p.p_id=v.p_id AND v.v_name LIKE "%mike%";
Is this a technique that people use often? I couldn't see it documented in my SQL book ("MySQL", by Paul DuBois), even though it seems like a useful technique for what is effectively a subselect.

Subselect
I have been held up long enough on the query time to ask for help. Its basically a subselect that never returns.

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id IN ( SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC ) ORDER BY it.org_id ASC

If I break it up into 2 seperate

SELECT id, it.org_id FROM import_temp3 AS it WHERE it.org_id = 09238323 ORDER BY it.org_id ASC

SELECT p.org_id FROM join_to_person AS j, person AS p WHERE p.id = j.person_id AND j.value = '15' ORDER BY p.org_id ASC

They both return expected values.

Subselect / AS
SELECT
a.id
(SELECT width, height, filename FROM photos WHERE user_id = a.id LIMIT 0,1) AS (width, height, filename)
FROM users a

ORDER BY a.datestamp DESC

you can see my example, using with AS (example).
How can i extract values from subselects?

Slow Subselect
I've got two tables:
lo_users: nickname|id|...
lo_friends: from|to|...

The following query takes < 0.01 sec:
SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1'

...but if I use it in a subselect, the whole thing takes about 0.54 sec:
SELECT u.nickname FROM (SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1') f LEFT JOIN lo_users u ON u.id = f.userid

What can I do to make the query faster? "from" and "to" are indexed and lo_users.id is the primary key.

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

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

ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near =85.

Any ideas? I need subselect working.

Delete Subselect
I know that MySQL 3.23.nnn did not support a delete subselect, just wondering
what the best/most efficient way to do the following is:

delete from table_a where table_a.column_1 in ( select column_1 from table_b);

Assuming that column_1 is the same data type and size in both table_a and table_b.

Update Self Subselect
I have a log table that creates a row for every page view. I have a field called "flagged" which defaults to 0. I'm trying to update the flagged field to 1 when the ip count is greater then 30... This is what I have but I get the error "You can't specify target table 'ip_log' for update in FROM clause".

UPDATE `ip_log` SET `flagged` = 1
WHERE `ip` IN(
SELECT `ip`
FROM `ip_log`
GROUP BY `ip` HAVING COUNT(*) > 30
)

Subselect Wierdness
I am trying to get 3 active article IDs from the table ARTICLES for a random active feed from table FEEDS. Here is the query I have:

CODEselect AID, a.fid as FID from ARTICLES a where active='Y' and a.fid = (SELECT f.fid FROM FEEDS f where active='Y' ORDER BY RAND() desc limit 1) limit 3;

Subselect With NULL
why don't i get some results for the second query?

mysql> select * from a;
+---+
| b |
+---+
| a |
| b |
+---+

mysql> select * from a where b not in (select NULL from dual);
Empty set (0.00 sec).

Subselect Doesnt Work
i am trying to remove values from a list menu if the join table
doesnt have keys when a key is selected for instance:
locations
locationID

locations_join
locationID shotlistID

SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN
locations_join lj ON l.locationID = lj.locationID WHERE l.locationID NOT IN
(select locationID FROM locations_join WHERE shotlistID IN (5069)) ORDER BY
l.location ASC

so when shotlistID is selected all the keys from the locations_join joined
to the shotlistID would be remove from the locations list please help, i'm
trying to do this in one query saving from getting all the keys into an
array then checking if the values arent in the array when generating the
list.

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

Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table:

CREATE TABLE b5_assignment_lookup (
as_id int(11) NOT NULL auto_increment,
as_blog int(11) NOT NULL default Ɔ',
as_blogger int(11) NOT NULL default Ɔ',
as_milestone enum('start','finish') NOT NULL default 'start',
as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`as_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ;
By running this query, I get the following resultset:

mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= &#55614;&#57158;-09-30'
+--------+----------+-------------+---------------+---------------------+
| as_id | as_blog | as_blogger | as_milestone | as_timestamp |
+--------+----------+-------------+---------------+---------------------+
| 87 | 89 | 41 | start | 2006-05-01 00:00:00 |
|208 | 89 | 41 | finish| 2006-09-02 11:55:27 |
|209 | 89 | 103 | start | 2006-09-02 11:55:27 |
+--------+----------+-------------+---------------+---------------------+
3 rows in set (0.00 sec)
What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41.

What I really need to grok is all bloggers who blogged all or a part of a range of dates.

For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02.

I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25.

The flags are the start/finish, obviously.

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

Ranking Student Grade? With Subquery/subselect?
I am a mySQL newbie here and have some problem defining the mySQL 4.0.14
or 3.23 SQL to get student grade ranking where tied grade have the same
rank.

I used to set it through MS Access 2002 and use this kind of query:

SELECT nilai.studentNIS, nilai.studenttestmark,
(SELECT COUNT(*) FROM tblStudentGrades
WHERE [studenttestmark]>[Nilai].[studenttestmark];)+1 AS NomorUrut FROM
tblStudentGrades AS nilai ORDER BY nilai.studenttestmark DESC;

I've been looking around mySQL documentation and read that subquery can
be redefined as INNER JOIN or using two SQL statement via variable? I
have no idea on the basics of how to set it out though.

Could one of you please help give a me a sample on how this kind of
query should be done on mySQL? Is it possible to do it in single line?
And without having to use PHP/Perl scripts?

Or maybe I should have approach it differently?

Count Related Records, But Show Records With NO Related Records Also
cl_items
========
it_id (pk)
it_ownerid
it_name

cl_offers
========
off_id (pk)
off_itid (fk) -> to it_id
off_whoid

My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers)

I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also.

Is there any way to do this with mysql only?

All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I
can usually figure out a select statement on my own. I have one today
though that really has me stumped. I am working in MySQlL 5.

In My first select statement I get all my records from Table B
SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`
Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`

In my second statement I get my records that match in this case phase
401 in Table B and all my Table A records for phase 401.

SELECT
`table_A`.`ITEM`,
`table_A`.`DECSCRIPTION`,
`table_A`.`UM`,
`table_A`.`PHASE`,
`table_B`.`Qty`,
`table_B`.`Calc` as calculated
FROM
`table_A`

Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID`
Where
table_A.PHASE In ('401' )

Now I need to combine the Data of both recordsets. I need EVERYTHING in
Table B, but I also need All Table A records that match the phase
selection....

Can I write this one query or do I need to use a Temp table?

Delete Records Without Matching Records
I have a one to many relationship in my database. I would like to delete records in one table if there are no matching records in another table.

Can't See Old Records
I really don't know much about mysql.
We have an hardware device performing bandwidht management which
we didn't even know it is using mysql until it was broken down.

After we solved the problem, we saw that the operationg system running in the device is linux (and old version) and the database is mysql.

The problem is that we can't see the old data eventhough when I
view the binary ISD file I can see the whole data including the olds.
But the management program doesn't show the old data. When I reenter new definitions from the management program, I can see that they are written into the ISD files. ISD files contains both old and new records but I can see the only new files.

Does that mean the index files are curropted?
If so, How can I recreate indexes or recover them?

Records That Are Only From 3 Pm To 6 Pm
I have a field in my table called time which is a datetime field.

The format of the data is like these below:
2006-07-12 17:55:19
2006-07-11 16:31:34
......

I would like to get every records that are only from 3 pm to 6 pm. Is there any query where I can check for the hour of a datetime field?

Top 5 Records - ASP
I have an ASP application talking to a MySQL database. All works fine but:

1) I can't figure out how to select just the latest 5 records in a table.

2) SELECT LAST_INSERT_ID() doesn't work, it just returns 0.

3) I need to select the 'recordid' (held in the first field and is the PK) of the latest record.

Getting Last 12 Records
I am trying to get just the last 12 records from a table. I have tried this but get no results.

SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' ORDER BY DESC LIMIT 12

Add Records (bug)
when a record is added, it adds 2 records instead of one. Code:

Get Records From The End
my question is how can I get the last 20 rows from a table without changing the order the table is.

Certain Records
Code:

insert into users (username, password, email) select user , ID, email from selex.empleados_selex

but i want to copy only the records where its status is "working"

something like:

insert into users (username, password, email) select user , ID, email from selex.empleados_selex WHERE status =´working`

Pf Records
Is it possible to specify the limit of records that can be entered in the table.

No Records In DB
I connected to the DB like this:
Set rsP1000 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT [G2P] FROM tblClients WHERE ClientID = '" & strID & "' AND GTime2 ='10:00a'"
rsP1000.Open strSQL, adoCon

I was wondering if the connection does not return any results, how do I display nothing. It keeps failing out because there is no record.

Records
I have a mysql database running on Mysql version 3.23.53 on a Red Hat
Linux 7.2. In the database there is a single table with over 150,000
records. Unfortunately, the performance of that table is very poor,
comparing to other tables in the same database.

Get Records
I have one table (members) and a linked table (payments) and I trying to retrieve all the records in the members table that don't have a record in the payments table.The payments table is linked to the members table by memberId. Does anyone know how I would compare the two tables and bring back only the members that don't have a record in the payments table?

To Many Records
This query is getting me rows which dont = $cid can anyone tell me why? =x
Code:

"SELECT event.eventName, event.eventMonth, event.eventYear, exhibitor.standNum
FROM event, exhibitor
WHERE exhibitor.companyID = event.companyID AND exhibitor.companyID = '$cid'"

Records
I want to extract those values whose modificatio dates are few days older using the following sql
[SQL]
select status from note where mdate<=date_add(date('Y-m-d'),interval -2 day) and status='Selected' and id=$r[0];
[/SQL]
but the comparison 'mdate<=date_add(date('Y-m-d'),interval -2 day)' doing nothing. I have checked it in db. There is no error too. How to correct that?