sql question

What is the relationship between the 3 tables?  Is it:

companies.name = link_comp_photos.company
photos.name = link_comp_photos.photo

If so, try this:

Select a.Company, a.Photo
From
(
Select '1' as 'Sort_Order', c.name as 'Company', p.name as 'Photo'
From companies c
INNER JOIN  link_comp_photos lcp on lcp.company = c.name
INNER JOIN photos p on lcp.photo = p.name
UNION
Select '2 as 'Sort_Order'', c.name as 'Company', p.name as 'Photo'
From companies c
where c.name not in (Select company from link_comp_photos)
) a
Order by a.Sort_Order, a.Company

<!--- SortOrder: 1 / Company has a photo, 2 / Doesn't have a photo --->
SELECT  c.Name AS CompanyName, 
             CASE WHEN mx.Company IS NOT NULL THEN 1 ELSE 2 END AS SortOrder
FROM    Companies c LEFT JOIN 
        (
           SELECT company, MAX(photo) AS MaxPhotoID
           FROM   link_comp_photos 
           GROUP BY company
        )
        mx ON mx.Company = c.ID
ORDER BY SortOrder, c.Name

Select allOpen in new window

Didn't even see the previous response.  Must have sat too long on the page ...

Thanks it's ok for the sort but i forgot to ask you something else, i need to get the name of the photo to put on the result list.

I give you the exact dbase structure.

companies
id_company
c_name
c_activity
c_country
c_email
c_website

activities
id_activity
a_name

countries
id_country
co_name

photos
id_photo
p_fileName
p_linkEmail
p_linkWebsite

Link_comp_photos
company
photo

c_activity link to id_activity
c_country link to id_country
link_comp_photos.company link to  companies.c_name
link_comp_photos.photo link to photos.id_photo

I need that the companies who have a photo are the first in list (order by her name) and after other companies.

i need order like : co_name,a_name,c_name

in coldfusion

<cfoutput query="queryName" group="co_name">
#co_name#<br>
<cfoutput group="a_name">
#a_name#
<cfoutput>
#c_name# <img src="photos/#p_filename#">
</cfoutput>
</cfoutput>
</cfoutput>





Thanks in advance, and thanks for the previous answers.

select C.name
 from company as c
 left outer join (SELECT DISTINCT COMPANY FROM link_comp_photos) as cp
 on c.id=cp.company
 order by caSE WHEN cp.company IS NULL THEN 1 ELSE O END , C.NAME

Thanks a lot .