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?
 
Mike EghtebasConnect With a Mentor Database 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
 
LordOfPortsConnect With a Mentor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gnoonCommented:
Remove OUTER from the statement.
0
 
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 EghtebasConnect With a Mentor Database 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 EghtebasConnect With a Mentor Database 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 KamdarConnect With a Mentor Commented:
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 EghtebasConnect With a Mentor Database 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
 
Jinesh KamdarConnect With a Mentor Commented:
>> 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 EghtebasConnect With a Mentor Database 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 KamdarConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.