[SOLVED] LEFT/RIGHT JOIN problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

[SOLVED] LEFT/RIGHT JOIN problems

Post by Janco »

Hi,

I'm having some trouble with LEFT and RIGHT JOIN.

queries I've tried:

1.

Code: Select all

select user_number, user_name, user_branch_cd from uni_bw_usr right join (select user_name  as template_name from bw_templates) on uni_bw_usr.user_level = bw_templates.user_level
2.

Code: Select all

select user_number, user_name, user_branch_cd from uni_bw_usr join bw_templates.user_name on uni_bw_usr.user_level = bw_templates.user_level
 
on query 1 I get the error: Every derived table must have its own alias

on query 2 I get error: 'bw_templates.user_name' doesn't exist

I've tried other queries but the user_name column of uni_bw_usr is overwritten with the user_name column of bw_templates and the user_name column is duplicated for every instance of the "ON"

All I want to do is to select the user_number, user_name and user_branch_cd column from the uni_bw_usr table and right join the user_name column of the bw_templates tables as column template_name so that the output will look something like:

_____________________________________________________
user_number | user_name | user_branch_cd | template_name |
------------------------------------------------------------

Can someone please assist me?

P.S. both the tables are located in the same DB
Last edited by Janco on Tue May 27, 2008 1:22 am, edited 2 times in total.
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: LEFT/RIGHT JOIN problems

Post by Janco »

Sorry I forgot to mention that I've went through the MySQL Ref Manual did searches on the web for left join with subqueries but unfortunately I couldn't find anything that either resembled or help my query.

One more thing is there a limit to how many instances after "ON" I can specify?
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: LEFT/RIGHT JOIN problems

Post by Janco »

I finaly got something:

Code: Select all

select user_number,user_name,user_branch_cd from uni_bw_usr right join (select * from bw_templates) as b on uni_bw_usr.user_level=b.user_level and uni_bw_usr.user_entry_menu=b.user_entry_menu
Now I have a couple of problems:
1. the bw_templates table is not joined to the uni_bw_usr table
2. if I don't state "(select * from bw_templates) as b" but instead say "(select user_name as template_name from bw_templates) as b" MySQL moans that the columns b.user_level and b.user_entry_menu doesn't exist

What am I doing wrong?
Last edited by Janco on Tue May 27, 2008 1:23 am, edited 1 time in total.
Janco
Forum Commoner
Posts: 37
Joined: Fri Apr 25, 2008 2:51 am

Re: LEFT/RIGHT JOIN problems

Post by Janco »

The answer, I found after bumping my head on a wall for the entire Friday, was a simple select query.

select t1.col1,t1.col2,t1.col3,t2.colxx as whatever from db1.t1,db2.t2 where ................

Being a newbie SUCKS!!

Like they say - insanity is not running into the same wall over and over again, but expecting a different result every time you do it.
Post Reply