none
Consulta RRS feed

  • Pergunta

  • Boa tarde...

    tenho 6 tabelas  cliente apartamento parcelas intermediaria fgts sfh  todas relacionadas pelo campo CodApto ok  e com o campo status = 'não pago' 

    'pago '

    Gostaria de fazer   uma consulta  que me retorne .. os resultados 

    ex.. consulta devedores  

    nome         apartamento           parcelas          intermediaria           fgts           sfh

     luiz                10                        100,00                5000,000              00,00       00,00

    Calors            11                        0,00                    100,000              500,00    00,00

    ...  um cliente pode ter pago  um parcela e não pagar intermediaria  etc

     

     

     

     

     

    quarta-feira, 1 de novembro de 2006 17:46

Todas as Respostas

  • vc pode  postar os scripts das tabelas ?

     

    Abs;

    quarta-feira, 1 de novembro de 2006 17:54
  • if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Conj_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Conj] DROP CONSTRAINT FK_Conj_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Controle_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Controle] DROP CONSTRAINT FK_Controle_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Fgts_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Fgts] DROP CONSTRAINT FK_Fgts_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Intermediaria_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Intermediaria] DROP CONSTRAINT FK_Intermediaria_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Parcelas_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Parcelas] DROP CONSTRAINT FK_Parcelas_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_sac_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[sac] DROP CONSTRAINT FK_sac_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SFH_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[SFH] DROP CONSTRAINT FK_SFH_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TbEtiquetas_Apartamento]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Tb_Etiquetas] DROP CONSTRAINT FK_TbEtiquetas_Apartamento
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tb_Desc_Parcelas]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
    ALTER TABLE [dbo].[Tb_Desc] DROP CONSTRAINT FK_Tb_Desc_Parcelas
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Apartamento]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Apartamento]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Controle]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Controle]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fgts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Fgts]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Intermediaria]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Intermediaria]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Parcelas]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Parcelas]
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SFH]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[SFH]
    GO

    CREATE TABLE [dbo].[Apartamento] (
     [idCod] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
     [cnpj] [numeric](18, 0) NULL ,
     [edificio] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [apto] [int] NULL ,
     [CodEdificio] [int] NULL ,
     [ValApto] [float] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Controle] (
     [idcod] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
     [cnpjConstrutora] [numeric](18, 0) NULL ,
     [CodEdificio] [int] NULL ,
     [CodApto] [int] NULL ,
     [contrato] [numeric](18, 0) NOT NULL ,
     [NomeCliente] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [endereco] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [tel] [varchar] (13) COLLATE Latin1_General_CI_AI NULL ,
     [email] [varchar] (35) COLLATE Latin1_General_CI_AI NULL ,
     [endCorres] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [dtContrato] [datetime] NULL ,
     [ValorApto] [float] NULL ,
     [Sinal] [float] NULL ,
     [igpm] [float] NULL ,
     [dtIgpm] [datetime] NULL ,
     [ValParcela] [float] NULL ,
     [QtParcela] [int] NULL ,
     [dtParcela] [datetime] NULL ,
     [ValIntermediaria] [float] NULL ,
     [QtIntermediaria] [int] NULL ,
     [DtIntermediaria] [datetime] NULL ,
     [tpvenda] [int] NULL ,
     [fgts] [float] NULL ,
     [sfh] [float] NULL ,
     [cep] [varchar] (15) COLLATE Latin1_General_CI_AI NULL ,
     [cpf] [varchar] (14) COLLATE Latin1_General_CI_AI NULL ,
     [Dtnasci] [datetime] NULL ,
     [profissi] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [EstCivil] [int] NULL ,
     [Regi] [int] NULL ,
     [ci] [int] NULL ,
     [NmConj] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [cpfConj] [varchar] (14) COLLATE Latin1_General_CI_AI NULL ,
     [CiConj] [int] NULL ,
     [MemObs] [varchar] (300) COLLATE Latin1_General_CI_AI NULL ,
     [Sequencia] [int] NULL ,
     [Periodo] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Fgts] (
     [idcod] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
     [codapto] [int] NULL ,
     [valor] [float] NULL ,
     [status] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [CodEdificio] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Intermediaria] (
     [idCod] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
     [CodApto] [int] NULL ,
     [DtIntermediaria] [datetime] NULL ,
     [QtIntermediaria] [int] NULL ,
     [ValIntermediaria] [float] NULL ,
     [Status] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [CodEdificio] [int] NULL ,
     [Controle] [int] NULL ,
     [Desconto] [float] NULL ,
     [Percentual] [int] NULL ,
     [ValorRestante] [float] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Parcelas] (
     [idCod] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
     [CodApto] [int] NULL ,
     [dtparcela] [datetime] NULL ,
     [QtParcela] [int] NULL ,
     [ValParcela] [float] NULL ,
     [Status] [varchar] (10) COLLATE Latin1_General_CI_AI NULL ,
     [CodEdificio] [int] NULL ,
     [ParcelaDesconto] [float] NULL ,
     [Desconto] [float] NULL ,
     [Percentual] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[SFH] (
     [idcod] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
     [CodApto] [int] NULL ,
     [sfh] [float] NULL ,
     [status] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
     [CodEdificio] [int] NULL
    ) ON [PRIMARY]
    GO

    segue o Script da  Tabela ... Grato Pela ajuda

    quinta-feira, 9 de novembro de 2006 17:59
  • Caro Adriley,

    Segue o exemplo de select que você solicitou. Gostaria também de te dar uma dica, sempre que você modelar o banco de dados procure dar o mesmo nome da chave primária para as chaves estrangeiras, ficou um pouco trabalhoso entender o modelo de negócio do seu banco de dados.

     

    SELECT

    NomeCliente AS nome

    , A.apto AS apartamento

    , P.ValParcela AS parcelas

    , I.ValIntermediaria AS intermediaria

    , F.valor AS fgts

    , S.sfh AS sfh

    FROM

    dbo.Intermediaria I

    INNER JOIN dbo.APARTAMENTO A

    ON

    I.CodApto = A.idCod

    INNER JOIN dbo.Controle C

    ON

    A.idCod = C.CodApto

    INNER JOIN dbo.Parcelas P

    ON

    A.idCod = P.CodApto

    INNER JOIN dbo.Fgts F

    ON

    A.idcod = F.codapto

    INNER JOIN dbo.SFH S

    ON

    A.idCod = S.CodApto

    where

    I.Status = 0 -- Devedor

    sábado, 16 de dezembro de 2006 02:02