CODEWITHSUNDEEP
sql-server
FrankSharp
FrankSharp

Reputation: 2632

Problem with INNER JOIN in a VIEW

I need a view that Show each Location(including prénom_Nom, numéro_Teléphone, niv,no_Modèle, année) and all Paiements. Finally order by id_Location.

I try this but its not Work

CREATE View On_locations_Clients_Paiements AS

SelectClients.prénom_Nom,Clients.numéro_Teléphone,Véhicules.niv,Véhicules.no_Modèle,
Véhicules.année,Paiements.montant

FROM Location
INNER JOIN Location ON Clients.id_Client=Location.id_Client
INNER JOIN Véhicules ON Location.niv=Véhicules.niv
INNER JOIN  Modèle ON Véhicules.no_Modèle=Modèle.no_Modèle
INNER JOIN Paiements ON Location.id_Location = Paiements.id_Location 



--ORDER BY where id_Location = id_Location

This is the select (I'm not sure if its right)

CREATE View On_locations_Clients_Paiements AS

Select Clients.prénom_Nom,Clients.numéro_Teléphone,Véhicules.niv,Véhicules.no_Modèle,Véhicules.année,Paiements.montant

Those are my tables

CREATE TABLE [dbo].[Location](
  PK[id_Location] [char](6) NOT NULL,
[debut_Location] [datetime] NULL,
[premier_Paiement] [datetime] NULL,
[paiment_Mensuel] [smallmoney] NULL,
[nombre_Mensualité] [char](2) NULL,
  FK[id_Client] [char](6) NULL,
[no_Termes_location] [char](6) NULL,
  FK[niv] [char](20) NULL,


 CREATE TABLE [dbo].[Clients](
  PK[id_Client] [char](6) NOT NULL,
[prénom_Nom] [varchar](50) NULL,
[adresse] [varchar](50) NULL,
[ville] [varchar](20) NULL,
[province] [varchar](20) NULL,
[code_Postal] [char](6) NULL,
[numéro_Teléphone] [numeric](10, 0) NULL,


 CREATE TABLE [dbo].[Véhicules](
  PK[niv] [char](20) NOT NULL,
[no_Modèle] [char](6) NULL,
[année] [char](4) NULL,
[kilométrage] [int] NULL,
[location_Antérieure] [char](3) NULL,
[valeur] [smallmoney] NULL,
[tranmission_Automatique] [char](3) NULL,
[airClimatise] [char](3) NULL,
[antiDemarreur] [char](3) NULL,
[no_Couleur] [char](6) NULL,

  CREATE TABLE [dbo].[Paiements](
  PK[id_paiement] [char](6) NOT NULL,
[date] [smalldatetime] NULL,
[montant] [smallmoney] NULL,
  FK[id_Location] [char](6) NOT NULL,

Upvotes: 3

Views: 14142

Answers (2)

Wil
Wil

Reputation: 4270

You need to take the second location out and the tables:

CREATE View On_locations_Clients_Paiements AS

Select c.prénom_Nom
      ,c.numéro_Teléphone
      ,v.niv
      ,v.no_Modèle
      ,v.année
      ,p.montant
FROM Location AS l
INNER JOIN Clients AS c ON c.id_Client=l.id_Client
INNER JOIN Véhicules AS v ON l.niv=v.niv
INNER JOIN  Modèle AS m ON v.no_Modèle=m.no_Modèle
INNER JOIN Paiements AS p ON l.id_Location = p.id_Location

Upvotes: 2

JNK
JNK

Reputation: 65157

Change:

FROM Location
INNER JOIN Location ON Clients.id_Client=Location.id_Client

to...

FROM Location
INNER JOIN Clients ON Clients.id_Client=Location.id_Client

Updated - you are referencing Location twice and Clients never so I think you have a copy/paste error of some sort.

Upvotes: 0

Related Questions