SQL Help

Gman said:
little off topic but what statemant do you use to produce the outputs of the results like so ?

Code: 
+--------------+-----------+-------------------------------------------------------+---------------------+
| article_user | user_name | article_title                                         | article_date        |
+--------------+-----------+-------------------------------------------------------+---------------------+
|            1 | pspain    | Dimmu Borgir at Academy 1                             | 2007-10-04 17:22:40 | 
|            2 | mheyes    | Evergrey Announced at Apollo                          | 2007-10-04 14:49:12 | 
|            3 | kbottoms  | Pitch Black Progress - The new album by Scar Symmetry | 2007-10-04 14:49:12 | 
+--------------+-----------+-------------------------------------------------------+---------------------+
Click to expand...

It's in Post #10 where you found the above result.

JIMA said:
This select will (is intended to anyway) retrieve details of the latest article for each user within a section. So, if three users have put a number of articles each into a section it will retrieve one row per user, that row being their latest article in the section.
Click to expand...

I only need the latest per section OR latest per user. I don't need both in the same query. The most important one is finding latest in each section. Latest per person is welcome, but not hugely important. Added bonus.

If we can find a good query for latest per section odds are it'll be a slight modification for finding latest per user. If all that makes sense :D
 
Ok. Here's one that will select the latest article in each section for a specific user (in this case the user with an id of 3):

Code: 
select a.article_date, a.article_section, a.article_title  ,
         u.user_id , u.username
from article a, users u
where a.article_user = u.user_id and u.user_id = 3
and a.article_date in (select max(a2.article_date)
                         from article a2, users u2
                         where a.article_section = a2.article_section
                         and u2.user_id = u.user_id
                         and a2.article_user = u2.user_id)
order by a.article_section, u.user_id;
commit;

If you want to get a list of all the latest articles for all users then simply omit the "u.user_id=3" part from the main select. I think the previous select I gave that you amended retrieved the latest in each section?

Apologies if I've got the wrong end of the stick.

Jim
 
Hmm, hate to say this but it appear it doesn't work...

Using u.user_id=3 produces all articles by that user - not just the latest. Omiting it makes things worse... multiple users and sections.

Just so we are all reading from the same hymn book the following statement works for selecting the LATEST article in each SECTION as well as the user_name, rank_name and status_name. [Details of the tables are above]

Code: 
SELECT DISTINCT article_section,
(SELECT user_name FROM user WHERE user_id = (SELECT article_user FROM article WHERE article_section = A.article_section ORDER BY article_date DESC LIMIT 1) LIMIT 1)  AS user_name,
(SELECT rank_name FROM rank WHERE rank_id = (SELECT user_rank FROM user WHERE user_id = (SELECT article_user FROM article WHERE article_section = A.article_section ORDER BY article_date DESC LIMIT 1) LIMIT 1) LIMIT 1) AS rank_name, 
(SELECT status_name FROM status WHERE status_id = (SELECT user_status FROM user WHERE user_id = (SELECT article_user FROM article WHERE article_section = A.article_section ORDER BY article_date DESC LIMIT 1) LIMIT 1) LIMIT 1) AS status_name,
(SELECT article_title FROM article WHERE article_section = A.article_section ORDER BY article_date DESC LIMIT 1) AS article_title,
(SELECT article_date FROM article WHERE article_section = A.article_section ORDER BY article_date DESC LIMIT 1) AS article_date
FROM user U, article A, rank R, status S
WHERE user_id = article_user AND user_rank = rank_id AND user_status = status_id
ORDER BY article_section;
Despite being awful, that SQL statement does what I need it to.

The output is:

Code: 
+-----------------+-----------+-----------+-------------+-------------------------------------------------------+---------------------+
| article_section | user_name | rank_name | status_name | article_title                                         | article_date        |
+-----------------+-----------+-----------+-------------+-------------------------------------------------------+---------------------+
|               1 | kbottoms  | Member    | Active      | Windows SMP Client                                    | 2007-10-04 22:50:27 | 
|               3 | mheyes    | Moderator | Active      | Crowden to Black Hill                                 | 2007-10-01 19:41:28 | 
|               9 | pspain    | Admin     | Active      | The new 'Age of the Five' books by Trudi Canavan      | 2007-10-01 19:46:06 | 
|              13 | kbottoms  | Member    | Active      | Pitch Black Progress - The new album by Scar Symmetry | 2007-10-04 14:49:12 | 
|              14 | pspain    | Admin     | Active      | Dimmu Borgir at Academy 1                             | 2007-10-04 17:22:40 | 
+-----------------+-----------+-----------+-------------+-------------------------------------------------------+---------------------+

For now, forget about selecting the latest article per user. Let's just concentrate on making a better query that returns the same results as above. Once we have sorted that, then we can move on to other things.

I really do appreciate everyone's help. I just think you both trying to create two queries is causing mix ups.

If it would help, I can post all the SQL required to recreate my tables exactly as I have them. That should make testing for you much easier. Just give the word.
 
Last edited:
SiriusB said:
It's in Post #10 where you found the above result.
Click to expand...

Not the results more how you've got the results formatted with all the lines? is it a certain application your using thats doing it or have you uses an sql statement to make it output like that
 
I think creating tables, including data, exactly as yours are is the only way this one's going to be cracked. My SQL definitely worked on my data so there must be some difference in the setup.

Either SQL statements to insert into the table or a number of delimited files that can be read into the DB would be great.

Jim
 
Thanks for that. I've got the tables all set up in MySQL now (bit of a newbie on MySQL - I mainly use Ingres) so it could be that the following SQL doesn't take advantage of MySQL specific stuff.

Anyway, the following selects back the latest articles for each section including the user and rank information. It's output matches the output which you previously posted:

Code: 
select a.article_section, u.user_name, r.rank_name, 
                   a.article_title,  a.article_date
from article a, user u, rank r
where a.article_id in (select a2.article_id from article a2
                             where a2.article_section = a.article_section
                             and a2.article_id = a.article_id
                             and a2.article_date in
                                   (select max(a3.article_date)
                                    from article a3
                                    where a3.article_section = a2.article_section))
and a.article_user = u.user_id
and r.rank_id = u.user_rank
order by article_section ASC;
commit;

Hope that works.....

Jim
 
Sweet! Looks like it does the job :)

Could you explain it a little for me? I'm getting lost with all the a. a2. and a3. business.
 
Glad that works. Here's an explanation of sorts...

The main select retrieves the various columns from the article, rank and user columns for the articles that the sub-select picks out.

The outermost part of the subselect picks out article_ids where the date of the article is the date found by the inner-most select. The inner-most select retrieves the max date for a section.

Each of the subselects uses a different set of data retrieved from it's own look at the article table. This is shown by each part of the select referring to it's own correlation name for the article table e.g a, a2 and a3. For example, the main select uses the article table referred to as "a" so it'll never got values that have been obtained by the look ups on the table carried out by the subselects a2 and a3. Using this kind of thing allows one select to form results from one table at the same table as another select forms results from the same table with no reliance between the two other than that stated in joins.

The correlation names are used to simplify the joins that hook the main and sub-selects together. For example, the a3 sub-select joins to the a2 sub-select on article-section so we know that the rows from the article table select known as a3 will be for the same section that the a2 select is looking at (so the max date will be for the article-section that a2 is looking at). Without this join the a3 select would pull back rows regardless of whether the section it was looking in was the one that the a2 query was looking in (the single row for the max date for all activities).

The "in" part just says that the value must be in the resultset obtained from the sub-select.

If that all sounds a bit complex it's because, in this instance, it is :) . Maybe it could be simplified further, I'm quite sure it could be.

The main difficulty with SQL I find is that you're working with sets of data rather than individual rows. So it takes a different approach to thinking of the solutions than say working with arrays in code. In the select we've got experiment by running each of the sub-selects in isolation (substituting actual values in place of the joins between the selects) and you'll see what each result set returned will be and how they join together.

Hope that helps, let me know if you need any more bits explaining and I'll do my best.

Jim
 
I think I get the idea. I will have a play around tomorrow poking it until it breaks :p

I have made this task harder on myself by pretty much jumping into the deepend. Just like me though. Most people would just have an articles table. Not me, I have 6 tables all fully normalized with 1 exception. :D I have been looking around for some good (My)SQL tutorials and guides, but so far most have very few actual examples, and they are usually really basic at that.
 
You must log in or register to reply here.
Back
Top Bottom