1

I have a union query as follows: 

(SELECT t.id, t.name, c.company AS owner, t.creation_date AS date, t.notes
 FROM tool t, client c
 WHERE t.id_customer = '15' AND t.trash_flag = '1')
  UNION
(SELECT f.id, f.name, CONCAT(m.first_name, ' ', m.last_name) AS owner, f.date, f.notes
 FROM file f, meta m
 WHERE ((f.acl = 0) OR (f.acl = 1 AND '1' = TRUE) OR (f.acl = 2 AND f.id = '7')) AND f.id = '15' AND f.trash_flag = '1' AND m.user_id = f.id_user) 
 ORDER BY 'name' 'ASC' LIMIT 0,20
Everything works fine but I have two questions:
  1. How do I add a column to the entire result set that gives the row number
  2. Could I do this without using UNION e.g. an advanced join?

Thanks for your time MySQL gurus!

RolandFlyBoy
  • 193
  • 3
  • 14

1 Answers1

4

I can't test it righ now but from what I found, following might work:

Reference: Row Number Variable

SQL Statement

SELECT  @rownum := @rownum + 1 rownum
        , t.*
FROM    (
            (SELECT t.id
                    , t.name
                    , c.company AS owner
                    , t.creation_date AS date
                    , t.notes 
            FROM    tool t
                    , client c 
            WHERE   t.id_customer = '15' 
                    AND t.trash_flag = '1' 
            ) UNION (
            SELECT  f.id
                    , f.name
                    , CONCAT(m.first_name, ' ', m.last_name) AS owner
                    , f.date
                    , f.notes 
            FROM    file f
                    , meta m 
            WHERE   ((f.acl = 0) OR (f.acl = 1 AND '1' = TRUE) OR (f.acl = 2 AND f.id = '7')) AND f.id = '15' AND f.trash_flag = '1' AND m.user_id = f.id_user) 
            )
        ) t
        , (SELECT @rownum := 0) r
ORDER BY 
        'name' ASC
LIMIT   0, 20
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • +1 The `ORDER BY name ASC LIMIT 0,20` is probably meant to be in the outer select but this is correct. – ypercubeᵀᴹ May 25 '11 at 11:05
  • 1
    Besides the `UNION ALL`, one more thing to speed the query: You can add a `LIMIT 0,20` in both subqueries and in the outer one. – ypercubeᵀᴹ May 25 '11 at 11:05
  • @ypercube, thank you. I have implemented your correction and suggestion. – Lieven Keersmaekers May 25 '11 at 11:11
  • @ypercube - I have removed the LIMIT from the subqueries. Without ordering of the subqueries, the end result would be different than what OP already had. – Lieven Keersmaekers May 25 '11 at 11:16
  • @Lieven: Yes, you are right. And it wouldn't work with `LIMIT 20,20`, `LIMIT 40,20` anyway... – ypercubeᵀᴹ May 25 '11 at 11:22
  • Thanks guys but get a *#1248 - Every derived table must have its own alias* MySQL error (ASC should prob also not have quotes around it). Tried changing t alias to something else as I use it for the tool table but didn't work :-( – RolandFlyBoy May 25 '11 at 11:24
  • @RolandFlyBoy - I have fixed the `ASC`error but unfortunatly, I have no idea how to fix the errormessage. – Lieven Keersmaekers May 25 '11 at 11:28
  • @RolandFlyBoy - following question on SO is about that specific error but I still don't spot the error in my answer. http://stackoverflow.com/questions/3363918/mysql-error-1248-42000-every-derived-table-must-have-its-own-alias – Lieven Keersmaekers May 25 '11 at 11:31
  • 1
    Cracked it (both UNIONS need to be bounded by brackets): `SELECT @rownum:=@rownum+1 rownum, tt.* FROM ((SELECT t.id, t.name, c.company, t.creation_date, t.notes FROM tool t, client c WHERE (t.id_customer = '15'AND t.trash_flag = '1' AND t.id_client = c.id)) UNION (SELECT f.id_file, f.name_file, CONCAT( m.first_name, ' ', m.last_name ) AS owner, f.date_file, f.notes_file FROM file f, meta m WHERE (f.id_customer = '15'AND f.trash_file = '1'))) tt, (SELECT @rownum := 0) r ORDER BY name DESC` – RolandFlyBoy May 25 '11 at 12:06