Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

help with sql sql changes

Posted on 2007-07-24
8
Medium Priority
?
184 Views
Last Modified: 2010-05-18
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
Comment
Question by:zolf
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19563118
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
 
LVL 19

Assisted Solution

by:Limbeck
Limbeck earned 320 total points
ID: 19563119
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
 
LVL 21

Assisted Solution

by:ziolko
ziolko earned 80 total points
ID: 19563129
first you can try to simply replace dbo,Ports with dbo.tel_port

how different is table tel_port from ports?

ziolko.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:zolf
ID: 19563191

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
 
LVL 21

Expert Comment

by:ziolko
ID: 19563192
jeeez guys you are fast:)

ziolko.
0
 

Author Comment

by:zolf
ID: 19563213

help
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1600 total points
ID: 19563214
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
 

Author Comment

by:zolf
ID: 19563275
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question