complicated SELECT statement: display records from multiple table

[bickyz]

Following are the two tables i have:

 

i need to write a mysql statement that will do followings:

- list the latest updated record of each company i.e. single record per company

- it should only list todays/current date record

 

interms of sorting:

- highest stock value with latest updated should be displayed on top.

- if both company has same stock value then the one who updated first should go on top. (i.e. latest updated should be on second)

 

i did something like this but it displays multiple records for each company:

SELECT * from tblcompany, tblstock
WHERE tblcompany.comid = tblstock.comid
AND tblstock.updated >= CURRENT_DATE()
ORDER BY tblstock.stock DESC, tblstock.updated ASC

 

i will be grateful if someone can help me please.

[ebmigue]

Try this:

SELECT 
    DISTINCT
    comid, company, phone1, phone2, email, latest
FROM
    tblcompany
NATURAL JOIN (
    SELECT comid, max(updated) as 'latest' FROM tblstock GROUP BY comid
)_tblstock

 

Hope it helps.

[bickyz]

with the above code following is displayed (sorry i have changed the data a bit)

 

on the above display can i list the stock value aswell

[ebmigue]

Can you provide a script for your sample data and table definitions?

 

Also, what would be the fields in the result?

 

Another, if a company has no entry in tblstocks, would that company be included in the results? If so, what would be the values for the "last_update" and "stockid" columns?

[bickyz]

Hi ebmigue, thanks.

 

Can you provide a script for your sample data and table definitions?

i have attached the scripts for both of my tables in this post.

 

Also, what would be the fields in the result?

in the results i want

comid company email stock updated

 

Another, if a company has no entry in tblstocks, would that company be included in the results? If so, what would be the values for the "last_update" and "stockid" columns?

It should only display records for those company who has updated today, any companies who hasnt updated today should not be displayed

 

 

[attachment deleted by admin]

[ebmigue]

Ok, try this:

 

SELECT
    DISTINCT
    *
FROM
    tblcompany c
NATURAL JOIN tblstock s
WHERE
  DATE(updated) = DATE('2011-06-21') --assuming that the current date is '2011-06-21'
AND updated = (SELECT MAX(_s.updated) FROM tblstock _s WHERE _s.comid = c.comid)
ORDER BY stock DESC, updated DESC;

 

The clause

AND updated = (SELECT MAX(_s.updated) FROM tblstock _s WHERE _s.comid = c.comid)

is to allow only "those stocks of a company that were most recently updated".

 

You could remove this to display the stocks of companies updated on a given date (in your case, "today").

 

 

Hope it helps.

[bickyz]

thanks very much ebmigue.

 

 

there are two stock with same value

 

comid company email phone1 phone2 stockid stock updated

4  jkl jkl@jkl.com 98563 45987 108 489.02 2011-06-22 09:35:18

3  ghi ghi@ghi.com 45367 98309 107 440.22 2011-06-22 09:35:17

2  def def@def.com 56789 65432 110 355.26 2011-06-22 15:10:20

5  mno    mno@mno.com 78965 69574 109 355.26 2011-06-22 14:10:19

 

 

def 355.26 2011-06-22 15:10:20

mno 355.26 2011-06-22 14:10:19

 

Here in the above situation if both values are same then i would like the stock that has been updated first (one with earliest time) to list on top

so it would be

mno 355.26 2011-06-22 14:10:19

def 355.26 2011-06-22 15:10:20

 

 

secondly is there anyway that we can put now() or something like that in the DATE( '2011-06-22' ).

 

once again thank you for your time, really appreciate.

[ebmigue]

SELECT
    DISTINCT
    *
FROM
    tblcompany c
NATURAL JOIN tblstock s
WHERE
  DATE(updated) = DATE(NOW()) --assuming that the current date is '2011-06-21'
AND updated = (SELECT MAX(_s.updated) FROM tblstock _s WHERE _s.comid = c.comid)
ORDER BY stock DESC, updated ASC;

 

Please review your MySQL manual, particularly the DATE functions, and the ORDER BY clause.

 

Hope it helps.

[bickyz]

ebmigue

thanks very very very much.

http://tridentonline.net/wp-content/uploads/2010/01/lifesavers.jpg[/img]

[ebmigue]

You're welcome.