• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 258
  • Last Modified:

help with sql sql changes

hello there,

i have this query

SELECT
ds.ip,
p.UserName,
bl.BlockID,
bl.Row,
bl.BookhtNum,
bl.TelCenterID,
bl.slot,
btl.name
FROM
dbo.Ports p INNER JOIN dbo.bookht_BookhtsList bl  
INNER JOIN dbo.bookht_telcenter btl  ON btl.pkid = bl.TelCenterID ON p.Bookht_ID = bl.pkid
INNER JOIN dbo.DSLAMS ds ON ds.ID = bl.DSLAM_ID WHERE (p.UserName like '%12345678')

which needs to be changed.now the table ports has been removed and the UserName which is the phone number has gone into another table called tel_port.how should i do it.please help.

cheers
zolf
0
zolf
Asked:
zolf
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello zolf,

SELECT
ds.ip,
p.UserName,
bl.BlockID,
bl.Row,
bl.BookhtNum,
bl.TelCenterID,
bl.slot,
btl.name
FROM
dbo.tel_Port p INNER JOIN dbo.bookht_BookhtsList bl  
INNER JOIN dbo.bookht_telcenter btl  ON btl.pkid = bl.TelCenterID ON p.Bookht_ID = bl.pkid
INNER JOIN dbo.DSLAMS ds ON ds.ID = bl.DSLAM_ID WHERE (p.UserName like '%12345678')




Aneesh R
0
 
LimbeckCommented:
hi, if i understand correctly all you have to do is change the table name

SELECT
ds.ip,
p.UserName,
bl.BlockID,
bl.Row,
bl.BookhtNum,
bl.TelCenterID,
bl.slot,
btl.name
FROM
dbo.Tel_Port p INNER JOIN dbo.bookht_BookhtsList bl  
INNER JOIN dbo.bookht_telcenter btl  ON btl.pkid = bl.TelCenterID ON p.Bookht_ID = bl.pkid
INNER JOIN dbo.DSLAMS ds ON ds.ID = bl.DSLAM_ID WHERE (p.UserName like '%12345678')

0
 
ziolkoCommented:
first you can try to simply replace dbo,Ports with dbo.tel_port

how different is table tel_port from ports?

ziolko.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
zolfAuthor Commented:

this is the new table that has replaced ports.i dont have this p.Bookht_ID = bl.pkid in my new table.please help

CREATE TABLE [dbo].[Bookht_Terminal_Ports](
      [TERMINAL_PORT_ID] [int] IDENTITY(1,1) NOT NULL,
      [TPORT_NO] [int] NULL,
      [DSLPORT_ID] [int] NULL,
      [BUCHT_STATE_ID] [int] NULL,
      [PORTAL_STATE_ID] [int] NULL,
      [TERMINAL_ID] [int] NULL,
      [PhoneNumber] [varchar](50) NULL,
 CONSTRAINT [PK_Bookht_TERMINAL_PORTS] PRIMARY KEY CLUSTERED
(
      [TERMINAL_PORT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
0
 
ziolkoCommented:
jeeez guys you are fast:)

ziolko.
0
 
zolfAuthor Commented:

help
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT
ds.ip,
p.phoneNumber UserName,
bl.BlockID,
bl.Row,
bl.BookhtNum,
bl.TelCenterID,
bl.slot,
btl.name
FROM
dbo.tel_Port p INNER JOIN dbo.bookht_BookhtsList bl  
INNER JOIN dbo.bookht_telcenter btl  ON btl.pkid = bl.TelCenterID ON
p.Bookht_ID = bl.pkid   ---- instead of Bookht_ID  you need to out the approapriate column
INNER JOIN dbo.DSLAMS ds ON ds.ID = bl.DSLAM_ID WHERE (p.phoneNumber like '%12345678')


0
 
zolfAuthor Commented:
let me again describe more clearly,

i have these tables

CREATE TABLE [dbo].[Bookht_Terminals](
      [TERMINAL_ID] [int] IDENTITY(1,1) NOT NULL,
      [CO_ID] [int] NULL,
      [TERMINALBLOCK] [int] NULL,
      [TERMINALROW] [int] NULL,
      [TERMINAL_B_ID] [int] NULL,
      [TERMINAL_STATE] [int] NULL,

CREATE TABLE [dbo].[Bookht_Terminal_Ports](
      [TERMINAL_PORT_ID] [int] IDENTITY(1,1) NOT NULL,
      [TPORT_NO] [int] NULL,
      [DSLPORT_ID] [int] NULL,
      [BUCHT_STATE_ID] [int] NULL,
      [PORTAL_STATE_ID] [int] NULL,
      [TERMINAL_ID] [int] NULL,
      [PhoneNumber] [varchar](50) NULL,

CREATE TABLE [dbo].[bookht_TelCenter](
      [pkid] [int] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NULL,

CREATE TABLE [dbo].[Bookht_LineCards](
      [LINECARD_ID] [int] IDENTITY(1,1) NOT NULL,
      [SERIAL_NO] [varchar](50) NULL,
      [SLOT] [int] NULL,
      [LINECARD_B_ID] [int] NULL,
      [DSLAM_ID] [int] NULL,
      [LINECARD_STATE_ID] [int] NULL,

CREATE TABLE [dbo].[DSLAMS](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [DeviceID] [varchar](50) NULL,
      [City] [int] NULL,
      [CO] [varchar](50) NULL,
      [Brand_ID] [int] NULL,
      [IP] [varchar](32) NULL,

CREATE TABLE [dbo].[Bookht_DSLPorts](
      [DSLPORT_ID] [int] IDENTITY(1,1) NOT NULL,
      [DPORT_NO] [int] NULL,
      [DSLPORT_STATE_ID] [int] NULL,
      [LINECARD_ID] [int] NULL,

now i need to get
TERMINALBLOCK from table [dbo].[Bookht_Terminals]
TERMINALROW from table  [dbo].[Bookht_Terminals]
[IP]  from table DSLAMS
[Name] from table Bookht_Telcenter
[TPORT_NO] from table [dbo].[Bookht_Terminals_Ports]
[PhoneNumber] from table [dbo].[Bookht_Terminals_Ports]
Slot from table Bookht_LineCards
where [PhoneNumber] like '%1234'

please help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now