[SOLVED] MySQL table join

piyush128k · 09-11-2012, 09:59 AM

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.

piyush128k · 09-11-2012, 11:42 AM

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???

TB0ne · 09-11-2012, 11:57 AM

Quote:

Originally Posted by piyush128k

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.

tronayne · 09-11-2012, 11:59 AM

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.

piyush128k · 09-11-2012, 12:36 PM

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.

TB0ne · 09-11-2012, 02:56 PM

Quote:

Originally Posted by piyush128k

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.

piyush128k · 09-12-2012, 08:39 AM

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. :-)