How to join tables by the key columns (used to join tables)?

Question

0.00/5 (No votes)

See more:

I have 2 tables, the table A with two columns, the primary key column named "MS", the other column named "Name", the table B has 2 columns, the primary key column named "MS", the other column named "Phone".

I can join two these tables using left join ... or simply "where" but all such ways also return a couple of column displaying the same value at the same record, that is A.MS and B.MS, that is not good to look, and especially, when I use "select *" from the joined table, it will show two those column unless I have to select each column I want like as "select A.MS, Name, Phone" from the joined table. (if using select *, it looks like "A.MS, Name, B.MS, Phone" as the order from left to right of the columns of the joined table). I have, in fact, many tables with many columns, and I want to join all them into an only table. If using "select *", simply but unexpectedly, the other way is too tedious to do because of the large number of tables to be joined! Is there any way to do it effortlessly?
Thank you so much!

Posted 12-Apr-11 4:10am

King Boy

Updated 12-Apr-11 6:00am

wizardzz

v2

Add a Solution

Comments

[no name] 12-Apr-11 11:10am

Your question needs to be reformatted to make it more readable. Consider showing the table definitions in a table rather than inline text.

luisnike19 12-Apr-11 11:15am

I do not understand what your problem is. Give more detail and clarify your problem.

thatraja 12-Apr-11 11:53am

You must format your question not like this short story format. BTW mention which database(you are using) in the question Tag.

[no name] 12-Apr-11 12:34pm

I'm sorry for I have only my phone as a media to connect to the Internet, I am scared of viruses so much so I don't connect my old computer to the Internet.
If possible, I will express the processes from creating to selecting by SQL command lines.
CREATE TABLE A(MS CHAR(5) PRIMARY KEY, NAME NVARCHAR(30))
GO
CREATE TABLE B(MS CHAR(5) PRIMARY KEY, PHONE VARCHAR(20))
INSERTING ...something into the table A.
INSERTING something into the table B.
1. SELECT * FROM A, B WHERE A.MS = B.MS
2. SELECT A.*, PHONE FROM A, B WHERE A.MS = B.MS
the result returned from the command 1 will look like A.MS, NAME, B.MS, PHONE
Do you see the presence of A.MS and B.MS (two these columns are the same except their names). So I don't want it that way.
The result returned from the command 2 will be A.MS, NAME, PHONE this is what I want. But when I have dozens of columns in each tables, the command 2 will need me much effort to do. I am finding another way easier and that is what I want!
Ah, I am using a MS access database (mdb not accdb).
Thank you so much!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

wizardzz · Accepted Answer · 2011-04-12T06:00:00

Solution 1

Honestly, just don't use select *. If you use select * you will get all columns. If you only want some columns, then don't use select *.

Posted 12-Apr-11 6:00am

wizardzz

Comments

[no name] 12-Apr-11 12:41pm

Of course, I know that thoroughly. But I am wondering if there is a parameter that can be added to the command to exclude other key columns except the only one of a table, and if possible, we will rename that unique key column to a nicer name.

wizardzz 12-Apr-11 12:46pm

Does each table have a different set of columns? I'm trying to think of a way to make the tedious part of eliminating the select * easier.

[no name] 12-Apr-11 13:12pm

I don't know how worthy it is to care about "each table has a different set of columns", anyway, I think it will be the same for all cases (there are key columns which are only different in their names in the joined table). The important thing to exclude other key columns (let only one key column of some table be in the joined table) is when we need to put the result by SELECT command into a VIEW, or a new table, and the new view or table should need only 1 primary key, not too many the same columns. If we join the tables A, B, C, D..., Z with a key column named "MS" in each table, the new table or view may include A.MS, B.MS, C.MS, D.MS ...,Z.MS
That's too redundant. We only need one of those columns A.MS or B.MS ...
Thank you so much!

wizardzz 12-Apr-11 15:20pm

Yeah, there's not any way I know of to do a select except column "MS". I asked if they were the same set of columns that you didn't want displayed per table, because then you might want to do something post query programmatically or run another query against a temp table of all your joins, etc.

[no name] 12-Apr-11 15:38pm

I want only one of them to be a key column for the joined table, so it should be in the joined table, and the others should be excluded because they are not necessary. But I think I have found a way to do that, of course not by typing the select command with hands, I will program to get the select command string like as the string in the second way, maybe I have to use some loop!
Thank you!

wizardzz 12-Apr-11 15:40pm

Yeah, that's what I was leaning towards, either a loop or recursive query builder, or dump it all to a table and reduce the columns from there based on names...

Wendelius · Accepted Answer · 2011-04-12T08:14:00

Solution 2

In SQL you have only few options:
- use * to get everything
- use alias.* the get everything from a table (possibly with other columns for other tables)
- define each column you want to have in the result, one by one.
As you have already noticed you can mix option 2 and 3.

Posted 12-Apr-11 8:14am

Wendelius

Comments

[no name] 12-Apr-11 15:29pm

Does that mean I have no way to get what I want? I have thought again of what I want, it may not be necessary, it is a little matter when I want to select all but key columns, it is a hard try with writing code in SQL environment but it is easy to do with programming. Thank you!

Wendelius 12-Apr-11 15:36pm

Sorry to say, but yes it means exactly that. The only thing that comes immediately in mind is that you would build a 'generic' sql builder which you could instruct not to include certain columns, but I see no benefit in doing that, just lots of extra work.