Creating a JOIN statement

[ScopeXL]

Hello,

 

Basically I am trying to create a SQL statement that joins data from 2 tables. I have tried many methods but come up short everytime. I need to select all from table1, and the last ID from table2 where table1.filed1 = table2.field2. So far I only get as far as

 

 

SELECT field1, field2 AS messageFROM table1 LEFT OUTER JOIN table2ON table1.field1 = table2.field2ORDER BY table2.field2 DESC

 

 

Which returns every result where table1.field1 = table2.field2 and I need it to only limit 1 per entry in table1.

[ScopeXL]

I seemed to find a query that does part of what I want

 

 

SELECT *FROM web_users LEFT OUTER JOIN web_messagesON web_users.username = web_messages.userGROUP BY web_users.username

 

 

But I still need it to select the LAST id from table2 (web_messages). I tried using ORDER BY and it errored on me.

[mikosiko]

try this

 

SELECT a.*, (SELECT MAX(b.ID) FROM web_messages b WHERE b.ID = a.ID) AS Mid
   FROM web_users a

[s0c0]

The sub select should be avoided at all cost as they are very slow. Left Join should be avoided as well.  Please post the schema of your table.

[mikosiko]

that is a very wide brush to paint the water....  IMHO 

[s0c0]

that is a very wide brush to paint the water....  IMHO 

 

IMHO its very true.  Always explore other options if you find yourself writing sub queries they are obviously slower and the Big O proves it and try to avoid LEFT JOINS because it analyzes more than you may need, but hey I'm not looking to get into a debate.

[ScopeXL]

CREATE TABLE IF NOT EXISTS `web_messages` (
  `id` int(11) NOT NULL auto_increment,
  `user` text,
  `date` text,
  `name` text,
  `fromA` text,
  `toA` text,
  `channel` text,
  `text` text,
  `extra` text,
  `color` text,
  `time` int(11) NOT NULL default '0',
  `function` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=140877 ;

CREATE TABLE IF NOT EXISTS `web_users` (
  `id` int(11) NOT NULL auto_increment,
  `username` text,
  `password` text,
  `channel` text,
  `server` text,
  `version` text,
  `views` int(11) NOT NULL default '0',
  `requests` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=65 ;

 

There is the schema for the 2 tables I am trying to join. Thanks for your help so far.

[s0c0]

mikosiko won't do the job because you are not storing the user id in the web_messages table.  Just an FYI, in the messages table you should store users by the web_users.id rather than username.  Searches on text take longer and you're having to store more data that way.  Just sayin'.

 

See if this works for you:

 

SELECT web_users.*,web_messages.* FROMweb_users LEFT JOIN web_messages ON web_users.username = web_massages.userGROUP BY web_users.id ORDER BYweb_messages.time DESC 

 

 

else you might have to sub select:

 

 

SELECT web_users.*, (SELECT web_messages.id FROM web_messages WHERE web_users.username = web_messages.user ORDER BY time DESC LIMIT 1) as web_massages_id FROMweb_users