help with left join

[picazo]

Hi.

 

I am using MySQL 5.0.45. I have the following 2 tables:

CREATE TABLE `assetpermissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `asset` varchar(30) NOT NULL,
  `description` text,
  `can_list` tinyint(4) NOT NULL DEFAULT '1',
  `can_view` tinyint(4) NOT NULL DEFAULT '1',
  `can_add` tinyint(4) NOT NULL DEFAULT '1',
  `can_edit` tinyint(4) NOT NULL DEFAULT '1',
  `can_delete` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;

CREATE TABLE `userassetpermissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `assetid` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=63 DEFAULT CHARSET=latin1;

 

Some sample data is:

 

assetpermissions

==================

1 asset1 desc1 1 1 1 1 1

2 asset2 desc2 1 1 1 1 1

3 asset3 desc3 1 1 1 1 1

4 asset4 desc4 1 1 1 1 1

 

userassetpermissions

==================

1 1 1 10

2 1 3 23

 

Here is what I want: Given a userid, I want to retrieve a list of all assetpermission records that also include the 'value' for that record and that user id. Using the sample data above, if the given userid is 1, then I want it to return:

 

results

==================

1 asset1 desc1 1 1 1 1 1 10

2 asset2 desc2 1 1 1 1 1 null

3 asset3 desc3 1 1 1 1 1 23

4 asset4 desc4 1 1 1 1 1 null

 

or even better, if I could get 0 instead of null.

 

I thought I could accomplish this by using a left join as follows:

 

select
ap.*, uap.value#, uap.groupname
from
assetpermissions ap
left join
	userassetpermissions uap
on
	uap.assetid=ap.id
where
uap.userid=[given id]

 

This however does not return anything for assetpermission records that do not have a matching record in userassetpermissions. For the example above, it returns:

 

results

==================

1 asset1 desc1 1 1 1 1 1 10

3 asset3 desc3 1 1 1 1 1 23

 

Please help with this issue.

 

Additionally, something that I have not attempted because I haven't been able to solve this initial problem is that I would like for it to return a list of all the assetpermission records and a value of 'null' or '0' for each record if the given userid does not exist.

 

Thanks in advanced,

Picazo

[fenway]

Your where clause needs to look for values in the non-left joined table, otherwise they'll be nulled out.