CODEWITHSUNDEEP
phpmysql
nyongrand
nyongrand

Reputation: 618

Select from two table than order it

I have 4 table, default_table, table_a, table_b, table_c, each have two columns,
I want to select name and id from all table then sort it,
I try"SELECT Name,ID FROM default_table, table_a, table_b, table_c ORDER BY Name"
but this give me error, so can someone help me with this problem, i need code in sql or php, or even javascript if it can

Upvotes: 2

Views: 124

Answers (3)

paxdiablo
paxdiablo

Reputation: 881153

Since order by is applied after unioning, you can just do:

select id, name from default_table
    union all select id, name from table_a
    union all select id, name from table_b
    union all select id, name from table_c
order by name

Your problem is that you're doing a Cartesian product of all the table data so it's can't figure out which id or name you mean. Even if it could, you'd get way more rows than you'd expect, due to the multiplicative effect. You want to join the data simply by adding all the rows to the output.

Explaining the distinction further, let's say you have two tables:

table1          table2
======          ======
id     name     id     name
--     ----     --     ------
 1     pax       1     jill
 2     bob       2     debbie
 3     joe

The query:

select a.id, a.name, b.id, b.name from table1 a, table2 b

(the cartesian product) will give you:

1 pax 1 jill
1 pax 2 debbie
2 bob 1 jill
2 bob 2 debbie
3 joe 1 jill
3 joe 2 debbie

(the row count is the product of the individual row counts) whereas:

select id, name from table1
union all
select id, name from table2

(the union) will give you:

1 pax
2 bob
3 joe
1 jill
2 debbie

(the row count is the sum of the individual row counts).

Upvotes: 5

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

I guess you need UNION (or UNION ALL):

SELECT *
FROM
  ( SELECT Name, ID 
    FROM default_table
  UNION ALL
    SELECT Name, ID 
    FROM table_a
  UNION ALL
    SELECT Name, ID 
    FROM table_b
  UNION ALL
    SELECT Name, ID 
    FROM table_c
  ) AS un
ORDER BY Name

Upvotes: 1

scibuff
scibuff

Reputation: 13755

Try something like this

SELECT 
    t1.name AS t1_name, t1.id AS t1_id, 
    t2.name AS t2_name, t2.id AS t2_id, 
    t3.name AS t3_name, t3.id AS t3_id, 
    t4.name AS t4_name, t4.id AS t4_id
FROM 
    default_table AS t1,
    table_a AS t2,
    table_b AS t3,
    table_c AS t4
ORDER BY t1.name

Upvotes: 0

Related Questions