LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 09-11-2012, 10:59 AM   #1
piyush128k
Member
 
Registered: Jun 2012
Posts: 68
Blog Entries: 1

Rep: Reputation: Disabled
MySQL table join


Hello,

I have just installed LAMP on SLES 11.

I have two simple tables name "usernames" and "personaldata". table username has 4 columns -> SNo, UName, Busterid, ActiveUName. personaldata has 3 columns -> SNo, Busterid and ActiveInactive. usernames table has Busterid as the primary key while personaldata has Busterid as the foreign key.

I want to join these two tables using the Busterid and run the query which would result in a new third table in my metadata database. This new third table would look like:-> SNo, Busterid, UName, ActiveInactive.

The command am using for join is not working.
select column_name(s) from table1, table2 where (table1. column =table2. column);
Rather it gives me the error Busterid is ambiguous.
Here column_name=Busterid in the above command and I feel it is not wrong. Help me solve this.

Once am done with this, I need to make a bash script that would compare a user list file on my another linux box with the user list created from the third table above. This comparison is to reflect in a new file .

So i have two things to achieve.

Last edited by piyush128k; 09-11-2012 at 12:07 PM. Reason: Foreign key correction
 
Old 09-11-2012, 12:42 PM   #2
piyush128k
Member
 
Registered: Jun 2012
Posts: 68
Blog Entries: 1

Original Poster
Rep: Reputation: Disabled
This is the error message I get while trying to join them:-

select Busterid from usernames, personaldata where (usernames.Busterid = personaldata.Busterid);
Error 1052 (23000): column 'Busterid' in the field list is ambigious

select Busterid from usernames left join personaldata on usernames.Busterid = personaldata.Busterid;
Error 1052 (23000): column 'Busterid' in the field list is ambiguous

How hard can this be???

Last edited by piyush128k; 09-11-2012 at 12:43 PM. Reason: comment at the end
 
Old 09-11-2012, 12:57 PM   #3
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 19,262

Rep: Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440
Quote:
Originally Posted by piyush128k View Post
This is the error message I get while trying to join them:-

select Busterid from usernames, personaldata where (usernames.Busterid = personaldata.Busterid);
Error 1052 (23000): column 'Busterid' in the field list is ambigious

select Busterid from usernames left join personaldata on usernames.Busterid = personaldata.Busterid;
Error 1052 (23000): column 'Busterid' in the field list is ambiguous

How hard can this be???
Not hard at all...and neither is putting this error into Google:

http://forums.devshed.com/mysql-help...0t-356256.html
http://answers.yahoo.com/question/in...1154200AAiBUkl

The field exists in BOTH tables. So specify the table you're referring to in your query. Also, by bumping your own thread, all you succeeded in doing is removing it from the zero-reply list, and making it less visible.
 
Old 09-11-2012, 12:59 PM   #4
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,541

Rep: Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060Reputation: 1060
A syntax for doing this would look something like
Code:
select a.sno, a.busterid, a.uname, b.activeinactive
from   usernames a, personaldata b
where  b.busterid = a.busterid;
You could add joins for the sno column and uname column but those would probably be overkill (assuming that busterid is unique in both tables).

A couple of rules of thumb is to select only what you need, keep indexes to a minimum (and don't index anything less that about 1,000 rows (or more, depending upon test results), the engine is good enough at looking for things), and normalize your data. "Normalize" means do not duplicate data in one table in another table (except unique identifying key(s) used for joins, such as unique employee identifier, serial number, etc.).

If the above isn't quite what you're thinking of, perhaps post your schema and your query.

Hope this helps some.
 
Old 09-11-2012, 01:36 PM   #5
piyush128k
Member
 
Registered: Jun 2012
Posts: 68
Blog Entries: 1

Original Poster
Rep: Reputation: Disabled
select username.SNo, usernames.Busterid, username.UName, personaldata.ActiveInactive from usernames, personaldata where (usernames.Busterid = personaldata.Busterid);

Now am going to work on the bash script. One part (silly one) solved. I just did not think outside the box. I was stuck on, one column from both tables but by using the from clause.

I also understand your point of "bumping the thread". I will take care. but remember, I would use the same thrrad for the bash script in the same one or I can re-open my earlier bash script thread which is closed right now? Let me know.

Last edited by piyush128k; 09-11-2012 at 01:38 PM.
 
Old 09-11-2012, 03:56 PM   #6
TB0ne
LQ Guru
 
Registered: Jul 2003
Location: Birmingham, Alabama
Distribution: SuSE, RedHat, Slack,CentOS
Posts: 19,262

Rep: Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440Reputation: 4440
Quote:
Originally Posted by piyush128k View Post
select username.SNo, usernames.Busterid, username.UName, personaldata.ActiveInactive from usernames, personaldata where (usernames.Busterid = personaldata.Busterid);

Now am going to work on the bash script. One part (silly one) solved. I just did not think outside the box. I was stuck on, one column from both tables but by using the from clause.

I also understand your point of "bumping the thread". I will take care. but remember, I would use the same thrrad for the bash script in the same one or I can re-open my earlier bash script thread which is closed right now? Let me know.
Then you're not understanding what bumping your own thread is. That is, posting the same question TWICE, or asking for follow up, when no one has answered you, as you did here. But you seem to have done it on all the threads you've posted so far.

If you have a question about writing a bash script, that would be a different question than one about MySQL wouldn't it? Different question = new thread. Simple. And before posting a question about a bash script, think about whether or not that question could be answered by searching Google, or even searching here. There are THOUSANDS of bash scripting tutorials you can easily find, and many more examples of MySQL query scripts.

Last edited by TB0ne; 09-11-2012 at 03:58 PM.
 
Old 09-12-2012, 09:39 AM   #7
piyush128k
Member
 
Registered: Jun 2012
Posts: 68
Blog Entries: 1

Original Poster
Rep: Reputation: Disabled
Ohh okay. I get it now! Yes I do research but like I said, for a me (new guy on linux) things are not as clear as they would appear to be. I read tutorials that I find on the web...its all scattered. I read blogs, which all the more are confusing.
Scrip will be another thread. I ask you, if I can re-open the one I have closed already because it is the continuation of that same thread just that I will now link it to mysql query. Or I could open another thread if I do not find the answer on the internet.

Also thank you for your time to explain to me these things. :-)

Last edited by piyush128k; 09-12-2012 at 09:51 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] mysql join three tables secretlydead Programming 1 09-21-2011 07:35 PM
mysql 'Table 'mysql.user' doesn't exist' Joe of Loath Linux - Server 13 08-05-2010 07:54 AM
mysql join secretlydead Programming 2 10-12-2009 09:14 PM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 01:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 12:30 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 01:12 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration