Combining Two Queries: Subqueries?

[Beaudoin]

I want to combine the following queries:

SELECT table1.UserName, AS Editor FROM table1 INNER JOIN table2 ON table2.EditorID = table1.User_ID

SELECT table1.UserName, AS Actors FROM table1 INNER JOIN table2 ON table2.ActorID = table1.User_ID

I would like to query to return a column of Editors and Actors.

How do I do this?

[bazz]

Can you post the tbl structure please.

[Beaudoin]

Ok...

table1:
UserID, UserName

Table2:
EditorID, ActorID

EditorID and ActorID are relationally tied to UserID.

Is that good?

[bazz]

It may be that you have an overlap of data so, to make sure I understand, can you post a sample of data from both tables.

bazz

[Brandoe85]

Put in an OR on your join:

select table1.userName from table1 inner join table2 on table1.userID = table2.editorID OR table1.userID = table2.actorID

[Beaudoin]

Ok... is there a way for the query to return two different columns... one of Editors, and another of Actors? I wasn't very clear in my first post.

I have a table that contains the ID numbers of Editors, and ID numbers of Actors. Those IDs, are stored in one table with a unique UserID. All that I want to do is return the UserNames of Editors and Actors using their respective ID numbers.

[Brandoe85]

Use a union:

select table1.userName AS 'Editor', NULL AS 'Actor' from table1 inner join table2 on table1.userID = table2.editorID
union select NULL AS 'Editor', table1.userName AS 'Actor' from table1 inner join table2 on table1.userID = table2.actorID
Though, i don't really understand your structure...

[StupidRalph]

I was going to pitch in but I agree with Brandoe. If you can't get the above query to work you should try to better describe your database schema.

If applicable, you can use the mysql command line then use the following command to get the table's schema.

show create table `database_name`.`table_name` and then paste it here.

[Beaudoin]

I figured it out after a bunch of trials. This is what worked:

SELECT table1.UserName AS Editor, (SELECT table1.UserName AS Actor FROM table1 INNER JOIN table2 ON table2.ActorID = table1.User_ID) AS Actor
FROM table1 INNER JOIN table2 ON table2.EditorID = table1.User_ID;

I'll try Broadoe's approach and see if that worked too.

Thanks for your help. I'm obviously a novice at this.