We help IT Professionals succeed at work.

order by with join

sasha85
sasha85 asked
on
Medium Priority
234 Views
Last Modified: 2010-03-20
filed data in column "subid" look like this: 12132332-43234_solg.mod.de
i got 2 tables. "table1", "table2"

table1 got columns: "subid" "name"
table2 got columns: "subid" "time"

*in the sql, the time field set as text.

how can i print the names of subid's from table1 in order of the time in table2
(mysql)
?
Comment
Watch Question

Try:
SELECT table1.name
FROM table1 
    LEFT OUTER JOIN table2
        ON table1.subid = table2.subid
ORDER BY table2.time ASC

Open in new window

Author

Commented:
mysql="SELECT table1.name FROM table1 LEFT OUTER JOIN table2 ON table1.subid = table2.subid ORDER BY table2.time ASC"
i got syntax error, LEFT OUTER?

Commented:
Remove OUTER from the statement.

Author

Commented:
mysql="SELECT table1.name FROM table1 LEFT JOIN table2 ON table1.subid = table2.subid ORDER BY table2.time ASC"
like this? LEFT..ON..
Top Expert 2008

Commented:
yes, that should do it.
its left join.. on..
Mike EghtebasDatabase and Application Developer
Commented:
mysql="SELECT T1.name FROM table1 T1 LEFT JOIN table2 T2 ON T1.subid = T2.subid ORDER BY T2.time Where Not IsNull(T2.Name)"
Mike EghtebasDatabase and Application Developer
Commented:
mysql="SELECT T1.name FROM table1 T1 INNER JOIN table2 T2 ON T1.subid = T2.subid ORDER BY T2.time"
Mike EghtebasDatabase and Application Developer

Commented:
Do you want to show what T2.name are missing from Tabl1 as well?
mysql="SELECT B.time, A.name FROM table1 A, table2 B WHERE A.subid = B.subid ORDER BY B.time"
Mike EghtebasDatabase and Application Developer

Commented:
If both tables have the same number of records, use:

mysql="SELECT T1.name FROM table1 T1 INNER JOIN table2 T2 ON T1.subid = T2.subid ORDER BY T2.time"

If Table1 has more records, use:

mysql="SELECT T1.name FROM table1 T1 LEFT JOIN table2 T2 ON T1.subid = T2.subid ORDER BY T2.time Where Not IsNull(T2.Name)"
Mike, shouldn't the WHERE clause precede the ORDER BY clause in ur 2nd query ?
Mike EghtebasDatabase and Application Developer
Commented:
jinesh_kamdar,
you are correct. Thanks.
mysql="SELECT T1.name FROM table1 T1 INNER JOIN table2 T2 ON T1.subid = T2.subid ORDER BY T2.time"
 
or
 
mysql="SELECT T1.name FROM table1 T1 LEFT JOIN table2 T2 ON T1.subid = T2.subid Where Not IsNull(T2.Name) ORDER BY T2.time " 
 
Depending on your need.

Open in new window

Author

Commented:
i am kind of lost here...
i can't know wich onw of the tables will have more records...
what i shell use than?
Database and Application Developer
Commented:
if you are mostly concern with Table1 data use:

mysql="SELECT T1.name FROM table1 T1 LEFT JOIN table2 T2 ON T1.subid = T2.subid Where Not IsNull(T2.Name) ORDER BY T2.time " 

But if you want compare these two tables side by side, use:

mysql="SELECT T1.name FROM table1 T1 INNER JOIN table2 T2 ON T1.subid = T2.subid ORDER BY T2.time"

You can  have two other queries to let you know what records are present only in Table1 and another to show what records are present only in Table2.

Mike


>> what i shell use than?

This totally depends on ur reqd. o/p. E.g.
Table1 has values - (1, ABC), (2, DEF), (4, XYZ)
Table2 has values - (1, 2-Jan-08 01:30), (2, 3-Jan-08 15:15), (5, 4-Jan-08 22:45)

In this case what will be ur o/p from the foll. :

A - 1, 2
B - 1, 2, 4
C - 1, 2, 5
Mike EghtebasDatabase and Application Developer
Commented:
records are present only in Table1:
mysql="SELECT T1.name FROM table1 T1 LEFT JOIN table2 T2 ON T1.subid = T2.subid Where IsNull(T2.Name)"

records are present only in Table2:
mysql="SELECT T2.name FROM table2 T2 LEFT JOIN table2 T2 ON T1.subid = T2.subid Where IsNull(T1.Name)"

Mike
Don;t confuse the options A / B / C with the data ABC. My question should have been something like this:

In this case what will be ur o/p from the following 3 options ???

i - 1, 2
ii - 1, 2, 4
iii - 1, 2, 5

Author

Commented:
ok.ok:)
thank you all you gave me so much solutions i was shoked for a while...:)
all of them good once

Explore More ContentExplore courses, solutions, and other research materials related to this topic.