Click here to Skip to main content
14,978,135 members
Please Sign up or sign in to vote.
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
Updated 12-Apr-11 6:00am
v2
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!

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 *.
   
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...
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.
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900