[SOLVED] ORDER BY based on Results FROM Another table

[JonneDs]

So here i come,my first post and i'm already asking for help, huh? so lets go straight on the point....

 

I have a query that , and i need to order the result based on wheter or not the ID of my main table appears on another table, somewhat like this:

 

SELECT A.id AS Id, A.nome AS Nome

    FROM main_table A

    JOIN joined_table JT ON JT.id = A.id_cat

ORDER BY ("first whoever have a row in" another_table AT)

 

another_table  stores the image paths related to  main_table  , i need to show all query results from main_table  even if they don't have a image (row in another_table), but i need to show whoever have a image (a row in another_table) first...

 

I've tryied to join with another_table and even to use sobqueryes in ORDER B... but so far i got no results =/

it's even worst because this query is a monter with multiple lines (the sample is simplyfied...)

 

Any help would be apreciated!!

[cooldude832]

Like

SELECT 
A.id AS Id, 
A.nome AS Nome,
JT.id as OB_FIELD
FROM main_table A
    JOIN joined_table JT ON JT.id = A.id_cat

ORDER BY OB_FIELD

[JonneDs]

Like

SELECT 
A.id AS Id, 
A.nome AS Nome,
JT.id as OB_FIELD
FROM main_table A
    JOIN joined_table JT ON JT.id = A.id_cat

ORDER BY OB_FIELD

 

something like this  but not for JT , for Another table  (AT)...

 

the problem is this "AT" table have multiple references to Main_table and i cant use a ON clause because it would return only the rows in Main_Table that have a row in Another_Table, and thats not what i need...

[fenway]

Then you'll need a LEFT JOIN instaed.

[JonneDs]

Then you'll need a LEFT JOIN instaed.

 

Yep, i'm trying it but it's returning duplicated rows =/

[JonneDs]

Then you'll need a LEFT JOIN instaed.

 

Yep, i'm trying it but it's returning duplicated rows =/

 

I solved the problem of the duplicating rows using DISTINC.

Thank you for your help =)

[fenway]

I solved the problem of the duplicating rows using DISTINCT.

Not the best approach, depending on how many dupes you have... another option to do get distinct results from the 2nd table *first*, and then join it back to the 1st table.