How to create a View with joint and the column used to join?-CodePudding

So I am trying to create a View by joining 3 tables (FlavouredBeer, BeerInfo & FlavourNotes) by the beerId in 3 tables. However, I cannot select the beerId column that I think it's useful to be shown.

CREATE VIEW  FlavouredBeers AS 
SELECT beerId, brand, name, flavourDescription
FROM BeerInfo JOIN FlavouredBeer
ON FlavouredBeer.beerId = BeerInfo.beerId
JOIN FlavourNotes `
ON FlavouredBeer.flavourId = FlavourNotes.flavourId;

I got the following error:

enter image description: error here

And I could only take away the beerId from the SELECT and create a view with 3 columns.

enter image description : the view now here

How to include the id column I used to join?? Thanks!

Create a View with

beerId, brand, name, flavourDescription

4 columns.

CodePudding user response：

en el select debes especificar de que tabla mostraras los campos.

el mensaje de error dice que los campos que envias son ambiguos debido a eso.

CREATE VIEWFlavouredBeersAS

SELECT BeerInfo.beerId, BeerInfo.brand, BeerInfo.name, FlavouredBeer.flavourDescription

FROM BeerInfo

JOIN FlavouredBeer

ON FlavouredBeer.beerId = BeerInfo.beerId

JOIN FlavourNotes

ON FlavouredBeer.flavourId = FlavourNotes.flavourId;