SQL Server :: Check Multiple Tables For An ID Reference?

Jul 21, 2010

I have 3 tables

Restaurants
IDNameAddress
Location
ID (Foreign Key)LocationID
Food
ID (Foreign Key)FoodID

Here's what I want to do, I have two variables @FID(food ID) and @LID(LocationID). I want something like

select * from restaurant where ID in (( select ID from location where locationID = @LID) AND (select ID from Food where FoodID = @FID))

The bold part is where I'm having problems. I want it to check both the Location and Food table for a match.

I'm having a hard time putting it into words :p I hope you get what I mean

View 2 Replies


Similar Messages:

SQL Server :: How Do You Add Rows To Multiple Key Tables

Jul 24, 2010

I am accessing rows in an SQL database table based upon the contents of the Primary key and another column. But, I cannot insert new rows in the table when the content of the new Primary key column already exists in another row. Can I define the table with a multiple column Primary key so that a unique value can be based upon the content of both columns?

View 3 Replies

SQL Server :: Foreign Key On Multiple Tables?

Jan 30, 2011

I have four tables namely tblStudent, tblParent, tblEmployee and tblUnwantedVisitor.

tblUnwantedVisitor should get the primary key from the three tables, which is the visitor ID, tblStudent visitor ID starts from 10000, tblEmployee from 20000 and tblEmployee from 30000.

I used foreign key from one to one table only, so I don't know if my approach for the database is wrong or foreign key for multiple tables, which is somehow unlikely, is necessary.

View 14 Replies

SQL Server/T-SQL - Update Multiple Tables At A Time ?

May 21, 2010

I have two tables, Customer and CustomerPhone.

Single record in Customer can have multiple CustomerPhone records. As you see in the image below, Phone and Fax resides in CustomerPhone table whereas the rest of the fields resides in Customer table. If user want to edits a customer record, obviously i will have to update the record in Customer table and at least two records from CustomerPhone (Phone and Fax).

I could write two update statements, one Update customerPhone and the second update Customer table. Is there a better solution?

View 2 Replies

SQL Server :: Select Statement For Multiple Tables

Oct 1, 2010

SELECT u.Email FROM UserProfiles p, aspnet_Membership u WHERE p.RulesCheckBox = 'True' AND u.UserId = p.UserId This gives me a list of registered member email addresses that have selected the RulesCheckBox in their profile, so I can email them with an email script. There is a relationship between the Membership table and the UserProfile table, so it finds the email addresses from the Membership table where that user has the RulesCheckBox checked in their profile (in UserProfiles table). I'm wanting to add some additional names to this list, from a different table. The purpose is so I can manually add email addresses to the additional table, so the additional email addresses will also receive the same email, even though they aren't registered members of the site. So how can I adjust that SELECT statement so this one is combined with it somehow?:

SELECT Email FROM ExtraEmails WHERE Rules = 'true'

View 4 Replies

SQL Server :: How To Excel Data Into Multiple Tables

Sep 17, 2010

I have excel file with columns EmpName,Date1,Date2,...Date7 .I want to insert EmpName into Employee table after inserting i will get EmpNo(Identity column) with that i need to insert Date1,Date2...Date7 into TimeSheet table.

My Excel structure is like this
EmpName 8/1/2010 8/2/2010 8/3/2010 8/4/2010 8/5/2010 8/6/2010 8/7/2010
Naresh 17:00-2:00 17:00-2:00 14:00-12:00 7:00-12:00 7:00-12:00 .. Off
Similarly 500 employees data

I want to insert name i.e into Emp table after inserting i will get EMpNo i.e identity column with that i should insert Date1(8/1/2010) i.e excel header to date7 into Timesheet table Date column and corresponding time into TimeIn and TimeOut.I will split the time.I just want write Stored procedure for this task.

My database tables are like this

Emp-EmpNo,EmpName,CompaignId
Timesheet-TimeID,EMpNo,Date,TimeIn,TimeOut,TotalHours
Date in Timesheet should be like this
TimeId EmpNo Date TimeIn TimeOut T otalHours

1 100 8/1/2010 17:00 2:00 9hrs

2 100 8/2/2010 17:00 2:00 9 hrs

7

I just want this logic on how to insert Date1 to date 7 as 7 rows into Timesheet table and EMpName into EMployee table.while accesing each row i need to insert Date 1 to date7 as 7 rows into Timesheet table.

View 2 Replies

Searching In Multiple Tables Using MS SQL Server 2000?

Sep 14, 2010

We have a database with a lot of information about Persons. I won't post the entire database structure because it is too big, but it looks something like this:

Person
ID
Name
Street
City
State
Country
Language
LangCode
Language
Interest
ID
LastChangedBy
LastChangedOn
LocalizedInterest
InterestID
LangCode
Description
PersonInterest
PersonID
InterestID

Now, this is just a small example. In our database, we have about 8-9 localized tables (like Interest) a Person can be linked to. A Person can have multiple Interests, a Person can have multiple Jobs, a Person can have multiple Educations, a Person can have multiple Experiences, I have to build a search function. Let's say you enter "tom" as the search term. This should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables.

If you enter multiple words to search for (eg. "tom php"), it should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables AND "php" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables. At the moment there are about 4,500 records in the Person table and if I do an outer join of Person with all the tables I have to search, there are about 1,300,000 records and 40-50 fields to search in.

How should I approach this problem so that performance will be acceptable? The client expects "something like Google" in terms of speed and ease of use. We're using MS SQL Server 2000 and ASP.NET 2.0. The search functionality has to be added to an existing application and changing the technology or database structure is not an option.

View 1 Replies

SQL Server :: Updating Multiple Tables Simultaneously

Sep 8, 2010

Was wondering if someone can help me out with this multi update problem

I have a webform that populates a single table but due to certain dropdown data conditions it could also potentially populate 3 other tables

The tables information populates a gridview with amounts from accounts etc..

e.g.

Table 1

Site DataType account update

34 SS 12.50 N

Table 2

Site account

34 12.50

Say the above entry has the updated column changed to Y via the webform the amount 12.50 should disappear from the gridview. I was

a bit shortsighted and added the additional tables afterward. The information on the gridview pulls from the additional tables and since

the first table and the additional tables are not linked the data still stays the same in the gridview

I do have an Id column on both tables but unfortunately I added the rest of the tables after the first one so the Id's do not really match.

View 6 Replies

SQL Server :: Customer Referenced In Multiple Tables Vs Big Join?

Dec 23, 2010

Just looking through database design and wondering what would be the better option. I have customers, who all have orders, pickings, deliveries and invoices. Now an invoice can't exist without a delivery, a delivery can't exist without a picking and a picking can't exist without an order. So I could set the tables up in a linear fashion. e.g.

Customer -> Orders -> Picking -> Delivery -> Invoice

then in Orders, there would be an ID field, with a customerID field and in Picking, there would be an ID field, with an orderID field and in Delivery, there would be an ID field, with pickingID and in Invoice, there would be an deliveryID field. Linking them altogether, again, in a 'linear' fashion.

The problem is if I want to get, say, all the invoices for a specific customer, my query would be something like

[Code]....

My other option would be to have in a customerID column in each of the tables meaning a much easier query

[Code]....

View 3 Replies

SQL Server :: Rename A Column In Multiple Tables Using Sp_rename?

Sep 6, 2010

Originally "LoadDate" column (see below scripts) was there both in tables DIM_Table_1 and DIM_Table_2

According to change in requirement, LoadDate in both the tables to be chnged to LOAD_DATE. It has to be done through script

EXEC sp_rename 'dbo.DIM_Table_1.LoadDate',
'LOAD_DATE', 'COLUMN' -- Successfully executed
EXEC sp_rename 'dbo.DIM_Table_2.LoadDate',
'LOAD_DATE', 'COLUMN' -- Error: See below

Error: The new name 'LOAD_DATE' is already in use as a COLUMN name and would cause a duplicate that is not permitted.

View 2 Replies

SQL Server :: Search Multiple Tables Via Stored Procedure?

Jul 27, 2010

I'm a novice in .NET programming. I was trying to figure a way to make a MULTI TABLE search via a stored procedure for my website. I've written a SP to select records from one table, which isn't working for some unknown reasons:

[Code]....

Whatever I search for, the datagrid is displaying all records from my table.

View 5 Replies

SQL Server :: Query To Select Data From Multiple Tables And A Different Database?

Dec 1, 2010

Is there a way to select data from multiple tables other than the UNION ALL statement. Addtionally I need to select data from a different database.

My current statement is as follows (there are alot more fields but I have shortened it ):

SELECT server.dbo.SRFILE.SR_GROUP, server.dbo.SRFILE.SROWNERSHIP, server.dbo.SRFILE.SRCONMAE, BSFLBWF_1.Incidentx,
BSFLBWF_1.Severityx FROM server.dbo.SRFILE INNER JOIN
BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = server.dbo.SRFILE.SRONUMBER
WHERE (BSFLBWF_1.Closex IS NULL).........

View 4 Replies

SQL Server :: Adding Rows To Multiple Primary Keyed Tables?

Jul 27, 2010

I am accessing rows in an SQL database table based upon the contents of 2 Primary key columns. But, I cannot insert new rows in the table when the content in one of the new Primary key columns already exists in another row. I get an error message that says: "Violation of PRIMARY KEY constraint 'PK_ReferendumVoters'. Cannot insert duplicate key in object 'dbo.ReferendumVoters'. The statement has been terminated."

The reason why this error is occurring is because I first stored "Referendum 1" in one column along with "me" in the second column of a new row for a "ReferendumVoters" table. Then, I attempted to insert a new row with the same "Referendum 1" string in the first column along with a different name in the second column. But, it will not let me do this even though I created a new index that defines the two columns as unique. Is there any way that will enable me to do this?

View 5 Replies

SQL Server :: Slow Running Query Based On Multiple Tables Using Joins

Nov 15, 2010

One of my peer wrote a query based on multiple tables using joins. One of the table is in a different database The query was taking much time

1. Create an SP
2. Create a temp table and copy all records from the external db
3. Avoid using Left Join as much as possible and use Inner Join
4. Create and use indexes
5. Remove tables/columns that are not necessary

In this scenario, I would like to suggest to use Covering Index, but how can it be created for multiple tables and a temp table?

View 3 Replies

SQL Server :: Selecting Data From Multiple Tables Whilst Using A Union Statement?

Nov 29, 2010

I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different database that contains information regarding the group of the data.

I so far have the following:

SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx

I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.

View 3 Replies

Databases :: Importing - Updating Sql Tables From Excel Sheet Into Multiple Tables Daily

Jan 4, 2011

I have an C# ASP application I am writing that needs to have the capability to import a generated excel or a comma delineated sheet each day. A clerk will have this job each morning so it doesn't need to be automated. My problem in trying to understand the solution to this is that the 1 sheet contains loan information, including customer information all in the same sheet. I would like to send certain columns to update information in the loan table and send other information to update the customer table. I need it to create relationships when new loans appear in the spreadsheet.

View 1 Replies

Exporting Multiple Tables In A DataSet To An Excel File With Multiple Sheets?

Jul 8, 2010

I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets. I found a very good article in [URL].[URL] was able to successfully export each of my datatables in the dataset to excel worksheets. This solution worked for small number of rows. But when the data got larger I am consistently getting the system.outofmemoryexception. THis is because the code is using up the memory when creating excel.

Does anyone have another option to do the above?

View 2 Replies

Data Controls :: Search In Multiple Tables Using One TextBox And Display Results In Multiple GridView

Jul 22, 2013

I created 3 product  table . A,B and c.

I show product of table in different gridview.  

Now I am using one search box. but how to search product name with image in one query all of three table.... 

Simple how to search product from multiple table and show result...

View 1 Replies

SQL Server :: How To Export The Data From Dat File To Tables(Two Tables)

Dec 30, 2010

I am using bulk insert concept to export the data from dat file to tables(Two tables). I am using the temporary table for to do the calculation and insertion to two tables. My problem is whenever I selecting the temp table data after the execution of bulk insertion , the order is changing .I need to get the order as it is in file order(csv,dat,txt).

View 5 Replies

ADO.NET :: How To Check If The Tables Has Rows In Linq

Nov 23, 2010

I am very new to Linq , i want to check if the PrintingAdminSetting table has rows in it and then if it has then i want to get the value and assign it to txtMaxJobs textbox

below is teh code

, please let me know teh syntax to check .

var DC = new ServiceDataContext();
var rec = DC.PrintingAdminSetting.Where("").First();
txtMaxJobs.Text = rec.ParaName.ToString();

View 4 Replies

DataSource Controls :: SQL And Linking 2 Tables To Check Value?

Apr 12, 2010

i have a job application process where users apply for a job, in the admin section administrators want to be able to see which applications have not been viewed. Now i have one page which lists all the job vacancies and then users can click into that specific vacancy to see which people have submitted an application. This all works fine.

However on the page i display the job vacancies i need to make it so that if there are any applications within a vacancy that need viewing it appears in RED.

The two tables i am using are below:

tbl_vacancies
job_id
job_name
location
voucher
details
availability
region
date_added
tbl_applications
application_id
job_id
fname
sname
other_info
.
.
.
.
date_addedd
viewed

Now the viewed column is in tbl_applications table, the applications relate to a specific vacancy via the job_id.

So what i need to check is IF there are ANY applications where the viewed = 0, if so that means that there are applications that need to be seen, and i want this to appear in RED.

Now i tried this several ways i.e an INNER JOIN etc.. but the full list of vacancies won't appearing.

So i tried this:

[Code]....

[Code]....

This doesnt work at all. I have a datareader which runs through the returns, i don't get any errors, and the applications display correctly but when i use the IF to check it doesnt work..

View 2 Replies

MVC :: Check Boxes Not Updating Membership Or Roles Tables?

Feb 8, 2010

I've got an MVC 1.0 View that is more-or-less an attempt at reproducing WSAT.

On the Security->Manage Users page there's a column of checkboxes for "Active" for each user. Also when clicking the 'Edit roles' link a set of roles shows up in the Roles column of the table.

I'm using the standard SQL based Membership and Role providers that come out of the ASP.NET box.

For some reason, when I run the app from VS2008 all is well and I can update the Active and Roles values. However, when I deploy to a remote server, either my test server or in production, the checkboxes don't update the databse. Note that the links for 'Edit user', 'Delete user' work fine. I can create a new user or delete a user with no problems in any of the deployed locations. Also the 'Edit roles' link also works fine in that it brings up the list of all possible roles and shows which roles the user is in. The problem is specifically with the Checkboxes.

Interesting to note is that the JavaScript alert() (see below) does fire when clicking on any of the Role checkboxes. However, although the box is checked, the database is not updated on the any but VS2008 Casini. The database connection string is not changed on any of the execution sites which are running SQL 2008. IIS 7 on one deployed server (an ISP) and 7.5 on the other my in-house test server.

Here's the code I'm using to create the Roles portion of the page:

[Code]....

View 3 Replies

Check For A Null Object Reference When Validating Forms In MVC?

May 28, 2010

I'm experimenting with validating forms in the asp.net MVC framework. I'm focusing on server side validation for the time being. I've come across an error that I'm not sure how to rectify. System.NullReferenceException: Object reference not set to an instance of an object. The code that throws the error is:

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create([Bind(Exclude="ID")] MembersCreate mc )
{
mc.Modules = ModuleListDataContext.GetModuleList();
ViewData.Model = mc;
//Validation using ModelState
//
//
//line below errors when form field is empty
//
if ((string)mc.Member.Username.Trim() == "")
ModelState.AddModelError("Member.Username", "Username is required.");
if (!ModelState.IsValid)
return View();
try
{
// TODO: Add insert logic here
return RedirectToAction("Index","Home");
}
catch
{
return View();
}
}

When I put spaces in the field it performs exactly as i want, but if I leave the field blank and press submit I get the error. What's the best way to avoid this error and still validate blank form fields?

View 1 Replies

Web Forms :: Exporting Multiple HTML Tables To Multiple Excel Sheets Excel

Mar 30, 2010

is there any way that i could export multiple tables already formatted into multiple excel sheets. I know how to export data to multiple sheets through dataset while looping through tables, rows and columns and then add styles.

But I really want to export formatted html tables each into seperate sheet

View 3 Replies

Data Controls :: Display Data From Multiple Tables Using Multiple Dynamic GridViews

May 7, 2015

I have storedprocedure which returns four or more dataset!

example here

How load  and display all data in Default.aspx....

dataset 1

............................................

dataset 2

............................................

dataset 3

............................................

dataset 4

............................................

View 1 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved