Is this UNION query possible...?

[Phippo]

Can anyone tell me if this union query is possible. I am running MySQL 4.something!

SELECT UserRights.UserRightValue, 'UserRights' as Source, UserRights.UserRight
FROM Users INNER JOIN (UserRights INNER JOIN Users_UserRights ON UserRights.UserRightValue = Users_UserRights.UserRightValue) ON Users.User_ID = Users_UserRights.User_ID
WHERE Users.User_ID = 10
UNION
SELECT DISTINCT UserGroups_UserRights.UserRightValue, 'GroupRights' as Source, UserRights.UserRight
FROM UserRights INNER JOIN (Users INNER JOIN ((UserGroups INNER JOIN UserGroups_UserRights ON UserGroups.UserGroup_ID = UserGroups_UserRights.UserGroup_ID) INNER JOIN Users_UserGroups ON UserGroups.UserGroup_ID = Users_UserGroups.UserGroup_ID) ON Users.User_ID = Users_UserGroups.User_ID) ON UserRights.UserRightValue = UserGroups_UserRights.UserRightValue
WHERE Users.User_ID= 10
ORDER BY UserRights.UserRightValue

 

[sleipnir214]

Have you tried it?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Phippo]

I have been porting an app from MS SQL Server to MySQL and I have only created the tables. They currently have no content. This query cannot be run until I have all the pieces of the Jigsaw in place.

I just wanted to know if it was theoretically possible without changing any of the JOIN SQL.

Cheers

Dave

 

[swampBoogie]

I doubt that Mysql will handle the parentheses in the from clause.

Remove them (when using MSSQL as well) and write a multiple table join as

select * from a inner join b on a.id = b.id 
inner join c 
on b.id = c.id

Thereby you don't dictate the join order as well.

Also you don't need distinct as a union will remove all duplicates anyhow.

 

[Phippo]

Well, amazingly enough the above query works in MySQL 4 with no modification. I removed the DISTINCT. One interesting thing that I can't fathom is there seems to be some sort of limit on setting the string as source section in the second select statement:

'GroupRights' as Source

Would only come out as GroupRight? I changed it in the code and all is working as expected.

Cheers for the suggestions

Dave

 

[r937]

the reason 'GroupRights' is truncated to GroupRight in the second query is because the datatypes of the result set columns (as well as the column names) are determined from the first query

in the first query, the second column in the result set is 'UserRights' which is interpreted either as char(10) or varchar(10) and therefore 'GroupRights' is truncated to fit

easy, eh?

when writing union queries, often you are cuttin' and pastin' single queries together, which might have aliases in them, so you might as well leave them in, and not bother removing them from the second (or subsequent) queries in the union (doesn't hurt, they are ignored, and removing them takes time, so why bother)

but if you're writing union queries from scratch, don't bother giving columns aliases in the second (or subsequent) queries because they'll be ignored


rudy

http://r937.com/