How to avoid column name conflicts?

Question

Recently, I was assigned the task of creating an auction system. During my work, I met with numerous cases when my SQL queries with contained joins were not executed due to ambiguous column names. Consider this (simplified) auction table structure:

table auction:

• id
• name
• uid (ID of the user who created the auction)

table item:

• id
• name
• uid (identifier of the user who added the item)
• aid (auction identifier on which the item is available)
• price (starting price)

table user:

• id
• name

table bid:

• id
• uid (identifier of the user who posted the application)
• iid (position whose price has been raised)
• price (suggested price)

As you can see, there are many columns with conflicting names. Joining these tables requires the use of certain measures that will eliminate the ambiguity.

. -, , , a_id, i_id, bid b_i_id. , .

, , - :

SELECT `bid`.`id`, `user`.`name`, `bid`.`price`
FROM `bid`
JOIN `item` ON `item`.`id` = `bid`.`iid`
JOIN `user` ON `user`.`id` = `bid`.`uid`
JOIN `auction` ON `auction`.`id` = `item`.`aid`
WHERE `bid`.`price` > `item`.`price`
AND `auction`.`id` = 1
GROUP BY `user`.`id`
ORDER BY `bid`.`price` DESC;

, .

, , , , ? SQL-?

Answer 1

, , , , , , .

Answer 2

, ?

select * from table1 as T1
join table2 as t2 on (t1.key=t2.foreignkey)

Answer 3

AS.

, . , .

, .

Answer 4

, :

SELECT a.* FROM TableA A

TableA A.

Answer 5

, - , .

, .

Answer 6

, , . auction_id, bid_id, user_id .. , , . adding_user_id bidding_user_id, user_id . .

Answer 7

You can also define a table column next to the table name:

SELECT P.*,T.name AS type FROM <TABLE-A> AS P LEFT JOIN <TABLE-B> AS T ON P.id=T.id