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.
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'
I am new to sql so please bear with me here.I have two tables, COURSES and RESPONSES which have a common field userID. I am making a grid view which displays each users available courseName from the COURSES table and dateTaken from the RESPONSE table. So I simply wrote the query as:
SELECT c.*, r.* FROM COURSES c, RESPONSE r WHERE c.userID = @userID1 and r.userID = @userID1 and r.userResponse = NULL
I've got a TextBox to capture the value of "Name" in Table A.
Ive got a CheckBoxList that displays all the pre-determined values of Table B.
When the user selects an item from the CheckBoxList i want to populate Table C with the value of the selected item from the CheckBoxList and the cooresponding ID associated with the user name for that person's entry.
i have a SearchEntity (SE), which contains following propertes
[Code]....
What i want to do is to pass this entity to the services and return how many ppl has been selected, but i was a bit confused about how to solve the union and intersection issues among each parameters( ex.1) and SEs themselves(ex.2). for example the following scenarios,
1. what if i want to select ppl who ( age>20 and female ) or (who live in LA and (chose answer no.1 or answer no.2))
2. what if to select ppl who ((age <20 or age >60) and female and live in LA and chose answer no.1)
OR
((age <18) and male and live in LA and chose (answer no.1 or chose answer no.2))
OR
(age>65 and live in NY)
in brief, could anyone tell me how to re-design my SE entity so i can handle all the possible scenarios in terms of selecting union and intersection and subset issues?
I have a Winform that has fields need to be filled by a user. All the fields doesn't belong to one table, the data will go to Customer table and CustomerPhone table, so i decided to do multiple inserts. I will insert appropriate data to CustomerPhone first then Insert the rest data to Customer table.
A follow up question concerning the DAL layer part .. The first question about dealing with large objectsI have one table (PlacesTable) that is connected by like 6 other tables (1:Many and Many:Many relations)
In my DAL should I put a big query that join the 6 tables + the m:m reference tables (btw it produces multiple data that I don't need Please refer to my old question) and place all the data in it's specific object property or, should I use a single query for each table and create an object for each fetched table .. and finally send those to the UI ?If I wasn't clear enough please let me know what I further information do you need to know!
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.
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).........
I'm facing a problem in our current running application. There is a table in database which has 67 columns,among these 18 columns are nvarchar(max) datatype and contains 30K records. Problem is now that SELECT * FROM myTable it takes around 3 to 4 seconds. Which is degrading our application's performance.
n my button_click event, I have three data connections being opened and closed... I'm wondering... should I have one try/catch statement with the three connections and executions in the single try/catch... or should I have a try/catch statement for each connection/execution?
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?
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.
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?
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.
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.
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
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.
