order by with join

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)
?
sasha85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window

0
sasha85Author 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?
0
gnoonCommented:
Remove OUTER from the statement.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

sasha85Author Commented:
mysql="SELECT table1.name FROM table1 LEFT JOIN table2 ON table1.subid = table2.subid ORDER BY table2.time ASC"
like this? LEFT..ON..
0
ee_rleeCommented:
yes, that should do it.
its left join.. on..
0
Mike EghtebasDatabase and Application DeveloperCommented:
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)"
0
Mike EghtebasDatabase and Application DeveloperCommented:
mysql="SELECT T1.name FROM table1 T1 INNER JOIN table2 T2 ON T1.subid = T2.subid ORDER BY T2.time"
0
Mike EghtebasDatabase and Application DeveloperCommented:
Do you want to show what T2.name are missing from Tabl1 as well?
0
Jinesh KamdarCommented:
mysql="SELECT B.time, A.name FROM table1 A, table2 B WHERE A.subid = B.subid ORDER BY B.time"
0
Mike EghtebasDatabase and Application DeveloperCommented:
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)"
0
Jinesh KamdarCommented:
Mike, shouldn't the WHERE clause precede the ORDER BY clause in ur 2nd query ?
0
Mike EghtebasDatabase and Application DeveloperCommented:
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

0
sasha85Author 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?
0
Mike EghtebasDatabase and Application DeveloperCommented:
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


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
>> 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
0
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
Jinesh KamdarCommented:
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
0
sasha85Author Commented:
ok.ok:)
thank you all you gave me so much solutions i was shoked for a while...:)
all of them good once
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.