MySQL query

[xhunter]

Hi
I can't get this table join to work

lets say I have 2 tables

table 1

ID NAME
1  S1
2  S2
 

table 2

ID TIMECREATED ACTIVE
1   time                    1
2   time                    0
1   time                    1
1   time                    0
2   time                    1
 

What I want to query is "S1" and "S2" and then join the table 2 like "ON table1.id=table2.id" but I want only the latest value (1 record) created (ORDER by TIMECREATED , LIMIT 1) for that ID join the first table and if it is also possible to check for table2.ACTIVE , even better.

I can't seem to work it out :(

[mwasif]

Try this

SELECT * FROM  
 (
   SELECT * FROM table2 WHERE ACTIVE=1 
   ORDER BY TIMECREATED DESC
 ) as tmp
INNER JOIN table1 ON table1.ID = tmp.ID
GROUP BY table1.NAME

[xhunter]

Try this

SELECT * FROM  
 (
   SELECT * FROM table2 WHERE ACTIVE=1 
   ORDER BY TIMECREATED DESC
 ) as tmp
INNER JOIN table1 ON table1.ID = tmp.ID
GROUP BY table1.NAME

hmm.....

that makes things a little complicated in my scripts going around and joining table 1 instead..
I am gonna try to make it out, hope it works

by the way, does that make this "2" queries on the database ?, I am trying to get the number of my queries as minimal as possible,

[xhunter]

OK,

I think there is a problem I didn't think the first time.

actually my first table is a list of jobs and the 2nd is the list of logs,

so there actually could be no records in the logs for the jobs in the first table, so joining the table1 to 2 would skip the records with no log.

i got my hopes up for sec..... :/

[mwasif]

In this case you should use LEFT JOIN in this way

SELECT * FROM jobs 
LEFT JOIN 
 (
   SELECT * FROM logs WHERE ACTIVE=1 
   ORDER BY TIMECREATED DESC
 ) as tmp_logs ON tmp_logs.id = jobs.id
GROUP BY jobs.NAME

This query creates a temporary table for LEFT JOIN.

This code is not tested.

[xhunter]

thanx,

when I saw u using 2 select statements, that's when what I immediately thought to use but I ran into 1 little problem, I need to get a value from the first select to use in the 2nd one, I could get it

SELECT * FROM jobs d
LEFT OUTER JOIN (SELECT * FROM logs WHERE d.id = cjobid ORDER BY jobid DESC LIMIT 1) f
ON d.id = f.cjobid
 

as you would see, "d.id" it's not known in the 2nd select

[mwasif]

SELECT * FROM jobs d
LEFT OUTER JOIN (SELECT * FROM logs ORDER BY jobid DESC LIMIT 1) f
ON d.id = f.cjobid

You have d.id = cjobid in the JOIN.

[xhunter]

thanx alot,

I ended up using the "GROUP BY" instead, as the jobid was different for different jobs,

but anyways it works now they way I want it,

thanks again for ur help, I didn't think of using a 2nd select :)