Jump to content


Photo

Sorting by newest post and unread icons?


  • Please log in to reply
23 replies to this topic

#21 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts
  • LocationBirmingham UK

Posted 20 January 2013 - 12:16 AM

Is this cleaner rather than using *?

SELECT
c.ClientID
, j.JobNumber , j.Username , j.Description , j.ClientID , j.Status , j.AdminUnread
, MAX(p.PostDate) as postedOn
FROM Clients c
INNER JOIN Jobs j ON c.ClientID=j.ClientID
LEFT JOIN Posts p ON j.JobNumber=p.JobNumber AND c.ClientID=p.ClientID
GROUP BY
c.ClientID
, j.JobNumber
ORDER BY
postedOn DESC, c.ClientID, j.JobNumber DESC

Have also added in a few extra orders for the jobs with no posts.

Edited by Kristoff1875, 20 January 2013 - 12:17 AM.


#22 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,648 posts
  • LocationBonita, FL

Posted 20 January 2013 - 01:05 AM

Call all from TABLE A > Only call certain things from TABLE B > Check if TABLE B has anything matching TABLE A in a certain column, discard anything that doesn't > Check if TABLE C has anything that matches any of the results that TABLE A and TABLE B sorted PLUS check and see if it matches the other value, if it doesn't then discard that too, then list all the results in the order requested?


More or less, except for the case of the LEFT JOIN (Table C) nothing is discarded if there are no matches.  Instead of discarding the row it is "joined" to a single row consisting of all NULL values.  Basically any field from Table C would just have the value NULL when selected.

Does seperating the GROUP BY items with a comma mean that both values have to be there to group them? For example calling GROUP BY for just JobNumber would show only 1 JobNumber 1, and using ClientID would show only one of Client ID 12345's jobs, but by using both values it's showing all of that Client's jobs?


As I said, you can think of it as creating a bunch of little boxes to hold the different rows.  Each box is labeled by the columns listed in the GROUP BY clause.  See the diagram, might help make sense of it.  Based on my fiddle above.
Attached File  diagram.png   160.48KB   6 downloads

As for your first point about not using * is that just an issue when calling from more than one table? Or an issue all the time? 


In applies to both.  You may see it a lot in example queries on the forums and such simply because it's easier to type and we often do not know what a user's column names actually are.  When writing your actual queries though you should not use it.  I use it in only two cases:
1) On the forums sometimes when posting sample queries
2) When building/debugging queries during development in a tool like Mysql Workbench/PhpMyAdmin.

Once the query is working propery and ready to be put into your script, list out the columns you need.

Take for example a setup like these forums.  On the forum listing page all you really need is the title, ID#, and author name of each topic.  You don't need the post data or any extra author info.  If you were use * you'd get all that extra junk though which is just going to eat up unecessary memory in PHP and bandwidth between PHP and the sql server.  As an added benefit if by chance all the columns you need are indexed mysql can pull the values straight from the index and not have to hit the table's datastore at all which lets the query run entirely in RAM and not have to wait for any disk I/O which is the main cause of slowness.

Also, where the tables are called and the names seem shorter for example c.ClientID j.JobNumber where abouts is that set?


In the from and join clauses I set the alias for the table.
...
FROM Clients c
INNER JOIN Jobs j
...

The c and j are the table aliases.  That bit essentially means "...From the Clients table (which will now be known as 'c') Join with the Jobs table (which will now be known as 'j') ..."
Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#23 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 235 posts
  • LocationBirmingham UK

Posted 20 January 2013 - 08:34 AM

Ah perfect thanks. So if I needed to reference a table more than just calling from it the first time, I could say 'SELECT * FROM TableName A' and then use 'TableName A' to refer to it in future?

#24 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,648 posts
  • LocationBonita, FL

Posted 20 January 2013 - 12:52 PM

Once you've aliased a table in a query, then you need to refer to it by it's alias name (and only that).  Notice I just used c.ClientId not Clients c.ClientId

Aliasing is useful to shorten the table names and reduce the amount of typing you have to do in the query if you need/choose to prefix your fields with a table reference.

Aliasing is also necessary if you need to join the same table twice.  For example if you had a message table and a message had two user ID's, one for FromID (Who sent it) and ToID (Who receives it) and you want to display their names, you'd do something like:
SELECT
   f.Name as fromName
   , t.Name as toName
FROM messages m
INNER JOIN users f ON m.FromID=f.UserID
INNER JOIN users t ON m.ToID=t.UserID


Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com