views:

121

answers:

3

i have more than two tables in my database and all of them contains same field names like

table A           table B       table C
field1            field1        field1
field2            field2        field2
field3            field3        field3
.                 .             .
.                 .             .
.                 .             .
.                 .             .

I have to write a SELECT query which gets almost all same fields from these 3 tables.Iam using something like this :-

select a.field1,a.field2,a.field3,b.field1,b.field2,b.field3,c.field1,c.field2,c.field3 from table A as a, table B as b,table C as c where so and so.

but when i print field1's value it gives me the last table values.

How can i get all the values of three tables with the same field names??? do i have to write individual query for every table OR there is any ways of fetching them all in a single query????

A: 

This is an artifact of how your programming tool handles duplicated field names. If you like, you can use AS to alias field names:

SELECT a.field1 AS a_field1, ...

It should then be accessible as a_field1.

Ignacio Vazquez-Abrams
A: 

You can alias the columns. e.g. Note: The syntax can vary depending on your DB.

SELECT
    a.field1 `A_Field1`,
    b.field1 `B_Field1`

SELECT
    a.field1 [A_Field1],
    b.field1 [B_Field1]

SELECT
    a.field1 AS A_Field1,
    b.field1 AS B_Field1
Robin Day
+1  A: 

Just write like this,

select a.field1 as af1,a.field2 as af2,a.field3 as af3,b.field1 as bf1,b.field2 as bf2,b.field3 as bf3,c.field1 as cf1,c.field2 as cf2,c.field3 as cf3 from table A as a, table B as b,table C as c where so and so.
Karthik
However, do not write the whole query in a single line unless it's very short (e.g. "SELECT col FROM table WHERE arg=?")
ThiefMaster
my query is very long as there are almost 100 fields that i'll be selecting from each table......so how can i write that???
developer
if you want to select particular field concatenate particular field name with database alias name or otherwise use * in that and fetch the particular field with that database aliasname.
Karthik
im sorry but i didnt got your solution i.e "select particular field concatenate particular field name with database alias name"....can u please explain this with an example.....
developer
just i mention a.field1 as af1 this only. Table name alias is a and concatenate with field name it is enough to take the value.
Karthik
@developer If you have tables with lots of columns, I recommend using SqlYog, it has a free version and it can generate SELECT/INSERT/UPDATE/DELETE statements with a few clicks, and you can go from there.It's a windows application but it works with Wine in Linux also.Also if you need to add aliases you can paste the generated code in a spreadsheet and use a text function to generate your code if it's something repetitive. I use this trick and it takes a couple minutes to write some bigger queries when needed.
ceteras