Double Select thingy

[Kunax]

Hello

I was wondering if it was possible to make a kind of double select, combining 2 tables into 1 result.

table1

id
name
type
responsible
date
booked
...

table2

id
s_name
s_type
s_responsible
s_date
s_booked
...

(SELECT date, name, type, book, responsible FROM table1 WHERE date>=now())
   UNION
(SELECT s_date, s_name, s_type, s_book, s_responsible  FROM table1 WHERE s_date >= now())

while($row=mysql_fetch_row($result))
{
   list($date, $name, $type, $booked, $responsible) = $row;
  do_table_stuff
 .....
}

This is what i got so far, but it just put the last result after the first, which is kind of stupid since i wanted it to be sorted the by date into 1 table.

moving on to look at join...

[Norman_Graham]

Hi Kunax

SORT BY s_date ASC

If you want it the other way round, use 'DESC'.

HTH

Norm

[Kunax]

Thanks for your reply, but i want the tables to be "interweaved" not just appended the 2nd to the 1st.

Lets say the sql in my first post result in a list like this:

nameX1, typeX1, date 01
nameX2, typeX2, date 02
nameX3, typeX3, date 03
nameX4, typeX4, date 04
nameX5, typeX5, date 05
nameX6, typeX6, date 06
nameX7, typeX7, date 07
nameX8, typeX8, date 08
nameX9, typeX9, date 09
s_nameY1, s typeY9, s_date 01
s_nameY2, s typeY9, s_date 02
s_nameY3, s typeY9, s_date 03
s_nameY4, s typeY9, s_date 04
s_nameY5, s typeY9, s_date 05
s_nameY6, s typeY9, s_date 06
s_nameY7, s typeY9, s_date 07
s_nameY8, s typeY9, s_date 08
s_nameY9, s_ typeY9, s_date 09

then i want:
nameX1, typeX1, date 01
s_nameY1, s typeY9, s_date 01
nameX2, typeX2, date 02
s_nameY2, s typeY9, s_date 02
nameX3, typeX3, date 03
s_nameY3, s typeY9, s_date 03
nameX4, typeX4, date 04
s_nameY4, s typeY9, s_date 04
nameX5, typeX5, date 05
s_nameY5, s typeY9, s_date 05
nameX6, typeX6, date 06
s_nameY6, s typeY9, s_date 06
nameX7, typeX7, date 07
s_nameY7, s typeY9, s_date 07
nameX8, typeX8, date 08
s_nameY8, s typeY9, s_date 08
nameX9, typeX9, date 09
s_nameY9, s_ typeY9, s_date 09

[Kunax]

btw the way i fixed the problem was to create a temp table, then inserted the data from my to table and ORDER by the date, problem solved, in only 4 querys(create, 2xinsert, select).

[Roger_Ramjet]

If your version of mysql supports subqueries then you can do it all in one query.

SELECT * FROM (SELECT 1 as i, date as d, name as n, type as t, book as b, responsible as r FROM table1 WHERE date>=now()
   UNION
   SELECT 2 as i, s_date as d, s_name as n, s_type as t, s_book as b, s_responsible as r  FROM table1 WHERE s_date >= now())
ORDER BY d, i

Note the aliasing. At the least you would need to alias the date column for the sort to work. I added the 1 & 2 as i to ensure that the results also combine in the required order.

[Kunax]

Thanks Roger Ramjet, look good but i only had time for a short test, but it looks promising.

Funky 1 Query SQL is the best 🙂, thanks again