joining a view to match main data in search query


I am currently building a telephone directory, i'm at the stage where I have the search functions runing smoothly. Except

the way I have the database set up is that the main table holds the business type as a numeral which was entered from

another table with coresponding names.

I have built views to tie these two tables together.

Now once a person searchs the listings they currently recieve a page of results, it is here that the problem occurs.

I need to to in my sql statment tie the main table of data to the view to pull the business type name out instead of the

business type numeral which is contained within the main data table.

Here is my current Sql Statement:

SELECT *
FROM dbo.MAIN, dbo.VIEW_BUSINESS_NAME
WHERE ENTRY_TYPE LIKE 'varSearchType' AND LAST_NAME LIKE '%varName%' AND INITIAL LIKE '%varInitial%' AND STREET_NAME LIKE

'varStreet' AND STATE LIKE 'varState' AND REGION LIKE 'varRegion' AND BUSINESS_TYPE LIKE 'varBusinessType' AND

dbo.VIEW_BUSINESS_NAME.Expr1 = dbo.MAIN.BUSINESS_TYPE
ORDER BY LAST_NAME

It is being put together in dreamweaver mx.

This is returning a multiple list of the one business repeated for as many listings as there are for that business type!

Hope this makes sense it's very early in the morning.
Posted On: Friday 26th of October 2012 12:09:03 AM Total Views:  229
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




view_definition

When retrieving view_definition from information_schema it appears as null because it is too long. Can anyone help me with retrieving the view_definition in this case. Cheers Ben
VIEWS ON THIS POST

90

Posted on:

Friday 26th October 2012
View Replies!

2 views same results one kills my server

hello all, i have 2 views view one - KIlls Server Code: SELECT dbo.CONT.CNUM, dbo.DEM.FIRST, dbo.DEM.LAST, dbo.DEM.EPN, dbo.DistanceQueryTable.D, dbo.DistanceQueryTable.SID FROM dbo.DEM INNER JOIN dbo.DistanceQueryTable ON dbo.DEM.EPN = dbo.DistanceQueryTable.EPN INNER JOIN dbo.CONT ON dbo.DistanceQueryTable.SID = dbo.CONT.SID View two Code: SELECT dbo.CONT.CNUM, dbo.[distance query].D, dbo.DEM.FIRST, dbo.DEM.LAST, dbo.STORE.SID, dbo.DEM.EPN FROM dbo.DEM INNER JOIN dbo.[distance query] ON dbo.DEM.EPN = dbo.[distance query].EPN INNER JOIN dbo.JOB INNER JOIN dbo.CONT ON dbo.JOB.JOBNUM = dbo.CONT.JOBNUM INNER JOIN dbo.STORE ON dbo.CONT.SID = dbo.STORE.SID ON dbo.[distance query].SID = dbo.STORE.SID ok in view one, i pull d from a table that has 800k rows in it. in View Two, i pull d from another view that has a result of 8 million rows. why does the first view kill my server and the second one doesn't
VIEWS ON THIS POST

86

Posted on:

Wednesday 7th November 2012
View Replies!

Create view that checkfor duplicated column data per same key id

i 've table called tbl1 with data as follow : Tbl1 name time1 time2 john 07:00 09:30 john 07:00 17:34 Jimmy 08:00 15:36 how to check time1 if duplicated for the same name , put a stat "" at the column "stat" so the result view is like this: name time1 time2 stat john 07:00 09:30 john 07:00 17:34 Jimmy 08:00 15:36
VIEWS ON THIS POST

200

Posted on:

Wednesday 7th November 2012
View Replies!

Test stored procedure & view results in query analyzer?

HI, I have a simple parameter query which has no errors according to the Query Analyzer . But, I'd like to set my parameters in the Query Analyzer and see the results. I have tried to figure out how to do that,but I dont know enough about SQL server yet. Can someone give me a hint I appreciate it! ! Here's my query: Code: CREATE PROCEDURE logtest ( @pword as char(8) , @logname as char(25) ) as select * from visitor where v_pass = @pword and v_login = @logname; GO
VIEWS ON THIS POST

122

Posted on:

Wednesday 7th November 2012
View Replies!

SQL SERVER 2000 DBA interview Question

hi, I am preparing for an interview on SQL Server DBA. Can anyone send me interview questions My email address lramesh1976@yahoo.com
VIEWS ON THIS POST

84

Posted on:

Saturday 10th November 2012
View Replies!

sql view error annoying

i have a view which was working fine and now for some reason... it keeps on giving me this error when i try to run it.. syntax error converting the varchar value '70,157' to a column of data type int. now 70 is a primarykey of a table and even 157.. can someone tell me what this error means ..
VIEWS ON THIS POST

146

Posted on:

Sunday 11th November 2012
View Replies!

Data view - export

hi, I created a view on a server, but I don't see any reporting options available for me to format the data. Where is this done in sql server 2000 I also thought I could export the view as a .rpt file, but I am not sure how I will be able to use it later. Can somebody point me in the right direction
VIEWS ON THIS POST

145

Posted on:

Thursday 15th November 2012
View Replies!

Comments in views and procedures

I am new to sql server and thought it was great that you were able to comment what you were doing when writing sql. I was using query analyzer to work out some of my sql. I even learned how to use the if and case statements creating comments to explain what I was doing. The question is after all of that work of commenting as I went was lost when I saved it as a view, and I also lost all the comments when I tried saving is as a stored procedure. Whats the point of comments if they dont get saved with the view or sp. Am I missing something Is there a way to save all your comments that you make when creating stored procedures or views I also tried to indent like you do in VB and keep everything lined up to. I lost all that too.
VIEWS ON THIS POST

127

Posted on:

Thursday 15th November 2012
View Replies!

Display numbers in a fixed format in a view

How do I use CAST (or any other function) to display a numeric value with a fixed number of characters after the decimal point My list of values is something like this: 345.45; 3245.46578; 3422.9; 3487.344 I want to show 3 characters after the decimal point, which will result in trailing zero's being added and extra characters being rounded off. The list must look like this: 345.450; 3245.466; 3422.900; 3487.344
VIEWS ON THIS POST

131

Posted on:

Thursday 15th November 2012
View Replies!

Filling sql datatable from grid view

I have my output in grid view in the following format. | outlook | < overcast > | yes | < rainy > | wind | < strong > | no | < weak > | yes | < sunny > | humidity | < high > | no | < normal > | yes | yes and no are my class values which is the answer.i want this grid view to populate a sql data table like the following. those within | | can come under parent and child column of data table based on conditions.those within < > are branches.yes and no come under class column only. the attribute outlook has 3 values-overcast,rainy and sunny wind has 2 values-strong and weak humidity has 2 values-high and normal id ---- parent-------branch------child-----class 1 ----outlook-------overcast---- ----yes 2------outlook-------rainy-------wind 3-------outlook-------sunny------humidity 4-------wind----------strong------ ----no 5-------wind-----------weak------- ------yes 6-------humidity-------high------ ------no 7--------humidity-------normal--- -------yes please give me suggestions of how to do it
VIEWS ON THIS POST

107

Posted on:

Tuesday 20th November 2012
View Replies!

SQL view filtering

Hi I'm really struggling with this... I want to be able to exclude an image once it has been approved by a moderator. There are two tables, tblPictures and tblApproved. So if the moderator has approved the image the details are inserted into the table tblAprroved. The approved table is very simple, it contains only a few columns: Code: [approvedID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [pictureID] [int] NOT NULL, [OwnerID] [int] NOT NULL, [blnYesNo] [int] NULL, [approvedDate] [datetime] NULL, OwnerID would be the moderators unique ID... I can supply scripts to generate them for you if that helps. I'm using this view to display only images that the moderator is required to check, anyone know how I can sort out the SQL I want the view to only return images that need voting upon. regards Jon Code: SELECT dbo.tblPictures.PictureID, dbo.tblPictures.processState, dbo.tblApproved.OwnerID AS approvedBy FROM dbo.tblPictures LEFT OUTER JOIN dbo.tblApproved ON dbo.tblPictures.PictureID = dbo.tblApproved.pictureID GROUP BY dbo.tblPictures.PictureID, dbo.tblPictures.processState, dbo.tblApproved.OwnerID HAVING (dbo.tblPictures.processState 'approved') AND (dbo.tblApproved.OwnerID 1)
VIEWS ON THIS POST

233

Posted on:

Tuesday 20th November 2012
View Replies!

Setting up user permissions to view and edit website

, I have a website connected to a MS SQL database via ASP connection strings. I want the general public to be able to view the data pulled out from the database and displyed on the website whilst also allowing the site owner to be allowed to edit the inventory in a domain.com/admin/ folder. What permssions and roles do I need to allow. I have created two users in my Helm control panel usera and userb I want usera to only be able to view the website (not edit/delete etc.) and userb to be able to have full admin rights. As it stands both have db_owner privilleges. Do I keep userb on this but amend usera to be just db_datareader and nothing more In my code I would then link to two different connection paths (one for live site, one for /admin)
VIEWS ON THIS POST

149

Posted on:

Tuesday 20th November 2012
View Replies!

Local variables in a view

. I need to set the value of a column from the value of another column in a view, like so: create view vBoatMetrics as select PK_BID, CASE when(var1 > 1) then A else B end as Column1Val CASE when(var2 > 1) then C else D end as Column2Val This part works fine, but when I try to: declare @Variable float set @Variable = (Column1Val*Column2Val), Column3Val = @Variable I get errors. Can someone please show me how to create a local variable in a view so I can carry the value throught the view I have several calculations in a view that need to be based on the outcomes of other calculations and so forth. I would GREATLY appreciate some help! MANY TIA, Brandon
VIEWS ON THIS POST

87

Posted on:

Tuesday 20th November 2012
View Replies!

Usning view to create table

Can anyone guide me how to create new table using view
VIEWS ON THIS POST

169

Posted on:

Tuesday 20th November 2012
View Replies!

Trouble joining 2 queries

Sorry problem solved
VIEWS ON THIS POST

100

Posted on:

Tuesday 20th November 2012
View Replies!

n00b question regarding DISTINCT in a view

, I'm quite new to the database scene and I have imported all these excel sheets into a MSSQL database. Basically it's all contacts and now I want to delete the "duplicates". Obviously the duplicates are from a human perspective duplicates the computer sees them as distinct records. My problem is that I have a column called company_name and a column called contact_person now under the company name I have several contact persons but I only need one and not specifically anyone. How can I get only one row per company no matter what the contact person is Cheers, Xander
VIEWS ON THIS POST

136

Posted on:

Tuesday 20th November 2012
View Replies!

views have disapeared

my views have disapeared from the management console however i can call them by asp its very strange anyone any ideas
VIEWS ON THIS POST

102

Posted on:

Tuesday 20th November 2012
View Replies!

MSSQL encryption of view + procedures

is there a way to encrypt all the views n procedures at once\t instead of clicking and encrypt one by one \t and are there only views and procedures able to be encrypted \t can i encrypt tables \t
VIEWS ON THIS POST

149

Posted on:

Tuesday 20th November 2012
View Replies!

SQL Recursie view or stored procedure

I have a table with a recursive parent-child relation: Parent_ID refers to Line_ID example of how that may look: Line_ID . Parent_ID ------------------ 1 . . . . . null -- higest item (no parent) 2 . . . . . 1 3 . . . . . 1 4 . . . . . 2 5 . . . . . 3 6 . . . . . 3 7 . . . . . 5 8 . . . . . 5 9 . . . . . 8 10 . . . . null What i need is the following fucntion/view/..: INPUT = any Line_ID OUTPUT = table with ALL its LOWEST children (a Line_ID that has no Parent_ID refering to it) example: . . 1 . . . . . . . 10 |----| 2 . . 3 | . |---| 4 . 5 . .6 . .|-| . .7 8 . . . | . . . 9 1 would give 4, 6, 7, 9 2 would give 4 3 would give 6, 7, 9 4 would give null 5 would give 7, 9
VIEWS ON THIS POST

178

Posted on:

Tuesday 20th November 2012
View Replies!

Compiling a view

I'm not sure if this has been discussed already - but my question is: at what point is a view on sql server2k compiled I'm pretty sure that a view is recompiled each time it is used (actually an execution plan is recompiled), but is it compiled at any other time Like when using alter/create view, or hitting ok/apply in Enterprise Manager when creating/changing the view.
VIEWS ON THIS POST

101

Posted on:

Tuesday 20th November 2012
View Replies!