SQL select with three tables-CodePudding

Hi guys I'm new with databases and I'm trying to make a query where I join 3 tables. I could make it and I want to clean up the result. I want to know how can I delete the column "pin" from users table and maybe some "ids" columns.

Select * from "wish-list"

Join products
On "wish-list".id = products.holiday_id

Join users
On "wish-list".user_id = users.id

Where "wish-list".id = 1

CodePudding user response：

You need to specify which columns you really need in your output. At the moment you are using
SELECT * which outputs all columns of all joined tables.

Here is what it should look like:

SELECT holiday, products.description, users.pin FROM "wish-list"
JOIN products ON "wish-list".id = products.holiday_id
JOIN users ON "wish-list".user_id = users.id
WHERE "wish-list".id = 1

It's important that you reference all columns which are not your main entity (here wish-list) with tablename.column (products.description and not only description). It will work without referencing strictly but only if the column name is unique in your query.

Furthermore you can rename columns. This is useful for example if you want to get the id's of the product table and the wish-list table.

SELECT product.id AS product_id, id AS wishlist_id FROM "wish-list"
...

Hope that helps!