Sorting 2 NON-related Tables

TOrpheus

Hi,

Scenario: I have 2 tables that do not have a common key, but they both have lastname as a field.

How do I join these 2 tables and SORT by the lastname field?

Thanks!

barand

SELECT * FROM table1 t1 INNER JOIN table2 t2
ON t1.lastname = t2.lastname
ORDER BY t1.lastname

hth

TOrpheus

Hi,

I tried that query, it didn't work. You know why?

I basically created 2 tables (t1, t2). I have only ONE field, (lastname). Tried your query... didn't work.

Thanks!

barand

The table names in my query are 'table1' and 'table2'

t1 and t2 are just aliases for those tables. Put your tablenames in the query instead of 'table1' and 'table2'

TOrpheus

Hi barand,

I know that t1 and t2 are aliases. I just created a quick test of 2 tables named t1 and t2 with ONE field to try out your sample.

It still didn't work. Can you try it yourself and see if it works?

I'm running MySQL 3.23.46.... is it compliant?

Thanks!

barand

Tried it and it works perfectly - also version 3.23

DeltaRho2K

Torpheus, create two tables called table1 and table 2, and then try it. (not t1 and t2)

See, the SELECT is on tblae1 and table2, just aliasing the names to be t1 and t2.

SELECT * FROM table1 t1 INNER JOIN table2 t2
ON t1.lastname = t2.lastname
ORDER BY t1.lastname

If on your tables (named t1 and t2 in your test setting), your query would gomething like:
SELECT * FROM t1 INNER JOIN t2
ON t1.lastname = t2.lastname
ORDER BY t1.lastname

TOrpheus

Hi All,

I know they are aliases... I just created the table names as short forms... so I didn't have to alias it...

I tried the query again, and it still wont' return any results...

barand

Originally posted by barand
Put your tablenames in the query instead of 'table1' and 'table2'

Let's hope you can get through 🙂

TOrpheus

Hi Barand,

This is what I ran:

SELECT * FROM t1 INNER JOIN t2
ON t1.lastname = t2.lastname
ORDER BY t1.lastname

I have 2 tables that are named t1 and t2 (NOT table1 or table2)... ONE field within each table called lastname... inserted a bunch of records... and didn't work 🙂

Thanks for continuing support! I appreciate it 🙂

barand

Are any of the lastnames in t1 the same as any of the lastnames in t2?

TOrpheus

Hi Barand,

Nope...

They are all different... My original post was asking how it was possible to sort 2 NON-RELATED tables...

barand

Originally posted by TOrpheus
Hi,

Scenario: I have 2 tables that do not have a common key, but they both have lastname as a field.

How do I join these 2 tables and SORT by the lastname field?

Thanks!

You original question was how to join the 2 tables and then sort, which isn't what you want - you want to merge the data.

if you had mySQL 4 you do this

SELECT lastname from t1
UNION
SELECT lastname from t2
ORDER BY lastname

However, as v3 doesn't support unions you can do it by creating a temp table and appending to it. Then selecting and sorting.

CREATE table temp (lastname varchar(30))

INSERT INTO temp SELECT lastname FROM t1

INSERT INTO temp SELECT lastname FROM t2

SELECT lastname FROM temp ORDER BY lastname

hth