SQL Help

SiriusB

SiriusB

SiriusB

Soldato
Joined
16 Dec 2005
Posts
14,443
Location
Manchester
I have a database with a few tables that contains articles, users and other info for a site I am creating.

At the moment I am working out all the possible SQL statements I could need before I start the PHP.

I have done quite well so far, considering my lack of SQL knowledge, but I have hit a snag.

I have two tables user and article. I have an SQL statement that will give me the most recent article of the whole site and it appears to work properly:

Code: 
 SELECT user_name, article_title, article_date, article_section 
FROM user, article 
WHERE user_id = article_user 
AND article_date = (SELECT MAX(article_date) FROM article);
The output is:
Code: 
+-----------+----------------------------+---------------------+-----------------+
| user_name | article_title              | article_date        | article_section |
+-----------+----------------------------+---------------------+-----------------+
| pspain    | New Folding@Home V6 Client | 2007-10-04 00:52:00 |               1 | 
+-----------+----------------------------+---------------------+-----------------+
Now, I would like statements that grab the most recent article per section and per user.

So I came up with this:

Code: 
SELECT user_name, article_title, MAX(article_date), article_section 
FROM user, article 
WHERE user_id = article_user GROUP BY article_section;
The output is:
Code: 
+-----------+--------------------------------------------------+---------------------+-----------------+
| user_name | article_title                                    | MAX(article_date)   | article_section |
+-----------+--------------------------------------------------+---------------------+-----------------+
| pspain    | New Folding@Home V6 Client                       | 2007-10-04 00:52:00 |               1 | 
| pspain    | Llyn Ogwen to Pen Yr Ole Wen                     | 2007-10-01 19:41:28 |               3 | 
| pspain    | The new 'Age of the Five' books by Trudi Canavan | 2007-10-01 19:46:06 |               9 | 
+-----------+--------------------------------------------------+---------------------+-----------------+
This appeared to work at first, but when I actually checked I realised the article title was wrong for the second record returned as you can see by the contents of the article table:

Code: 
+--------------------------------------------------+---------------------+
| article_title                                    | article_date        |
+--------------------------------------------------+---------------------+
| Llyn Ogwen to Pen Yr Ole Wen                     | 2007-10-01 19:34:59 | 
| Pen-Y-Pass to Elidir Fawr                        | 2007-10-01 19:39:09 | 
| Crowden to Black Hill                            | 2007-10-01 19:41:28 | 
| The new 'Age of the Five' books by Trudi Canavan | 2007-10-01 19:46:06 | 
| New Folding@Home V6 Client                       | 2007-10-04 00:52:00 | 
+--------------------------------------------------+---------------------+
The SQL statement correctly gets the latest date per section, but seems to choose the earliest article per section [and associated user_name]. I am not sure how to fix it!

Any help would be great :)
 
Thanks guys. Will give that a go soon.

Like I said in my OP I am very much an SQL n00b so I can only write code I know [and understand]. Although I will now be looking into TOP [or LIMIT for MySQL] and DESC.

Thanks again :)
 
There is no "live" database yet. So the one I set up is my test bed until I am happy everything is working. Then I have the hard task of filling it up with real data :p

Regarding your statements I can confirm that

Code: 
SELECT user_name, article_title, article_date, article_section 
FROM user, article 
WHERE user_id = article_user 
order by article_date DESC
LIMIT 1;
Works as it should, selecting the newest article in the database.

I am however having a problem with selecting the newest article in each section..

Your code:
Code: 
SELECT user_name, article_title, article_date, article_section 
FROM user, article 
WHERE user_id = article_user
GROUP BY user_name, article_title, article_date, article_section 
ORDER BY article_date DESC;
Produces:
Code: 
+-----------+-------------------------------------------------------+---------------------+-----------------+
| user_name | article_title                                         | article_date        | article_section |
+-----------+-------------------------------------------------------+---------------------+-----------------+
| mheyes    | Evergrey Announced at Apollo                          | 2007-10-04 14:49:12 |              14 | 
| kbottoms  | Pitch Black Progress - The new album by Scar Symmetry | 2007-10-04 14:49:12 |              13 | 
| pspain    | New Folding@Home V6 Client                            | 2007-10-04 00:52:00 |               1 | 
| pspain    | The new 'Age of the Five' books by Trudi Canavan      | 2007-10-01 19:46:06 |               9 | 
| mheyes    | Crowden to Black Hill                                 | 2007-10-01 19:41:28 |               3 | 
| pspain    | Pen-Y-Pass to Elidir Fawr                             | 2007-10-01 19:39:09 |               3 | 
| pspain    | Llyn Ogwen to Pen Yr Ole Wen                          | 2007-10-01 19:34:59 |               3 | 
+-----------+-------------------------------------------------------+---------------------+-----------------+
It has got everything correct, but it has chosen all three articles in Section 3. I only want the latest in each section.

If my understanding of GROUP BY is correct, it takes all the items in the column given and basically groups all the identicle values. In your code, you are grouping several columns. There is no way those columns put together can form groups, as they will always make a different combination.

However, just grouping article_section means all the info goes out of whack, as per my original post.

I suppose I could code something into the PHP to ignore the extra articles, but that's not really ideal - especially if i have hundreds of articles to wade through.

Sorry to be a bore :p
 
Woah. That's an SQL statement with chest hairs!

I'd never have come up with that in a million years. Heh, I am still trying to get my head around it :p However, the main thing is works! So thank you very much.

Hopefully I wont run into any other problems!
 
me said:
Hopefully I wont run into any other problems!
Click to expand...

Ooof, spoke too soon!

I'd like a similar SQL statement to choose the latest article by each user. Unfortunately I just can't get my brain around your SQL statement, so I can't modify it.

A brief explanation would be a great help, if you don't mind :)

EDIT: After a small epiphany [or stroke] I think I understand that SQL statement... at least enough to come up with this:

Code: 
SELECT DISTINCT article_user,
(SELECT user_name FROM user WHERE user_id = article_user ORDER BY article_date DESC LIMIT 1) AS user_name,
(SELECT article_title FROM article WHERE article_user = A.article_user ORDER BY article_date DESC LIMIT 1) AS article_title,
(SELECT article_date FROM article WHERE article_user = A.article_user ORDER BY article_date DESC LIMIT 1) AS article_date
FROM user U, article A
WHERE user_id = article_user
ORDER BY article_user;

Which appears to work. The output looks like:

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

I get a redundant "article_user" column though. At least I am on the right track though :)
 
Last edited:
I just tried your code as is and didn't work. I then whacked on a "GROUP BY article_section" at the end, and it appears to work!

Code: 
SELECT a.article_date, a.article_title, u.user_name, a.article_section
FROM user u, article a
WHERE u.user_id = a.article_user
AND a.article_date IN (SELECT MAX(a2.article_date)
FROM article a2
WHERE a2.article_user = a.article_user
AND a2.article_section = a.article_section) GROUP BY article_section;

I cant make it work for per-user though. Tried to GROUP BY article_user [and user_name] but it didn't work. Any ideas?

Also, how would one modify the query to also grab information from rank and status tables, which are linked to the user table through user_rank and user_status?

I have figured out how to do it with nikebee's code [it looks awful!] but if yours can be easily modified - then great :D
 
All this SQL is making my brain hurt.

I have no idea what is going on with the DECLARE stuff. I only know it doesn't work [syntax errors]. I can't fix it either :(

This SQL is getting horribly complicated - I am finding it hard to keep up. I just about got around the code you gave me yesterday, and I adapted it to get most recent article per user by myself [and got rid of the redundant column].

I also managed to adapt your original code to also grab the rank_name and status_name for each user. However it looks horrendous. Before JIMA posted yesterday I was looking for a way of storing the result of a sub-query so I could use it again, as my new SQL statement from hell repeats the same sub-query a few times as you can see below.

Small children and those of a nervous disposition should look away now:

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;
The above outputs:

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 | 
+-----------------+-----------+-----------+-------------+-------------------------------------------------------+---------------------+
So it does work. It's just a shame it is a horrible monstrosity of an SQL statement! There has got to be a way to reduce the repetitive statments.

The new rank and status tables look like:

rank
rank_id
rank_name
.... [other non related info]

status
status_id
status_name
... [other info]

Both the tables link to user through user_rank and user_status

The status table might not be needed, as the data it holds could probably be put somewhere else [or be derived in the PHP]. However, i wont give up and nuke the table just yet.
 
nikebee said:
Holy crap man... that has got a bit insane :eek:
If I get a chance later I'll have a proper play at lunch or during a quiet 5 minutes and I'll see if I can write a simpler version - I think you've expanded on my original query a little too much :)
Click to expand...

I know! Insane isn't it?

At first it all seemed so logical... then I found it worked and I was like "hurrah!". Then I stepped back and realise what I had created! :eek:

Bloody SQL. It's not like other languages. The learning curve is more like a brick wall.

As usual, your help is greatly appreciated. Please don't feel the need to do this during your breaks though. Do it when you get back to work :p
 
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
 
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:
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.
 
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