SQL Select Multiple FIelds from Multiple Data Issue

I 开发者_如何学运维have a mysql query

SELECT content_type_clients.nid, content_type_clients.field_logo_display_value,
files.filepath, node.title, node.created 
  FROM content_type_clients, files, node 
WHERE content_type_clients.nid=node.nid 
AND files.fid=content_type_clients.field_client_logo_fid 
  ORDER BY field_logo_display_value DESC, node.created ASC

But some of the fields won't have a files.fid=content_type_clients.field_client_logo_fid value. Is there a way to see if files.fid and content_type_clients.field_client_logo_fid have a value (not NULL) and if it is NULL don't do it?

Thanks

If the fields don't match (or have a NULL) value, your query won't return results. So if it's NULL, it doesn't do it.

Your query

SELECT content_type_clients.nid, content_type_clients.field_logo_display_value, files.filepath, node.title, node.created FROM content_type_clients, files, node WHERE content_type_clients.nid=node.nid AND files.fid=content_type_clients.field_client_logo_fid ORDER BY field_logo_display_value DESC, node.created ASC

is actually an older way (ANSI-89) of writing the more modern (ANSI-92)

SELECT content_type_clients.nid, content_type_clients.field_logo_display_value, files.filepath, node.title, node.created 
FROM content_type_clients 
JOIN files ON files.fid=content_type_clients.field_client_logo_fid 
JOIN node ON content_type_clients.nid=node.nid 
ORDER BY field_logo_display_value DESC, node.created ASC

Now if you want the query to return results even when the files do not match, use a LEFT OUTER JOIN instead:

SELECT content_type_clients.nid, content_type_clients.field_logo_display_value, files.filepath, node.title, node.created 
FROM content_type_clients 
JOIN node ON content_type_clients.nid=node.nid 
LEFT OUTER JOIN files ON files.fid=content_type_clients.field_client_logo_fid 
ORDER BY field_logo_display_value DESC, node.created ASC

You could use:

files.fid IS NOT NULL

in your WHERE clause.

If they have null values, they'd never be caught in the join operation. In SQL, null cannot ever be equal to anything, including itself.

SELECT ..
FROM ...
WHERE x = null

will always return nothing, because you cannot test for equality with null. Instead, you have to use the special

WHERE x IS null

syntax

This should also work. It only selects files that exist and are in the content_type_clients table.

SELECT content_type_clients.nid, content_type_clients.field_logo_display_value,     
files.filepath, node.title, node.created
  FROM files left outer join content_type_clients on files.fid=content_type_clients.field_client_logo_fid 
    left outer join node on content_type_clients.nid=node.nid 
ORDER BY field_logo_display_value DESC, node.created ASC