Any SELECT Statement Gurus Out There?
Sep 12, 2005
Hi all,
I'm having real trouble wrapping my newbie brain around this problem. Can
someone please tell me the most efficient (or any!) way to write a SELECT
statement to return a set of rows from 5 tables:
These tables are part of our medical database. For example, a person seeking
healthcare may want to know the names and contact information for all
doctors in Reno, NV, who do cardiology. Sounds simple, but it isn't.
A medical group can have many doctors.
A doctor may be a member of more than one medical group.
A doctor or group can have multiple specialties.
A group can have multiple facilities (physical locations).
So the tables look like this...
Table: Doctors
---------------
DoctorName
DoctorID
Table: Groups
--------------
GroupName
GroupID
Table: Docs2Groups (provides many-to-many relationship between Doctors and
Groups)
---------------------
DoctorID
GroupID
Table: Specialties (provides many-to-many relationshop between Doctors,
Groups, and Specialties)
-----------------
SpecialtyID
SpecialtyDesc
DoctorID
GroupID
Facilities
---------
FacilityName (provides one-to-many relationship between Groups and
Facilities)
FacilityID
GroupID
Address
City
State
Zip
Phone
E-mail
Any help would be GREATLY appreciated.
--Eric Robinson
View 6 Replies
ADVERTISEMENT
Nov 3, 2000
Probably this will be a silly question?
1. How can I Put all the Q documents from Microsoft that are related to SQL server? So that I can search fast.
2. How can I search SWYNK for all the ANSWERS that MAK/CRAIG/RAY replied to the threads. Its so weird that I answered someone and now I forgot the answer what I posted. Now I am having the same problem. I cant search.
3. I wanna put all SWYNK's Questions responses in one database say SQLSERVER, so that I can search all the threads.
I had sent many emails to SWYNK. No reply so far.
Any inputs r well appreciated!!!!!!
-MAK
View 2 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
 UPDATE TABLE1
  SET TABLE1.FIELD2 = 1
 ELSE
  UPDATE TABLE2
  SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Sep 17, 2007
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View 2 Replies
View Related
Aug 6, 2007
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View 2 Replies
View Related
Jul 11, 2007
Hello
How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated
View 2 Replies
View Related
May 15, 2008
Hi,
I am a newbie to this site and hope someone can help....
I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:
if(TL_flag= '1', "yes") as [Trial Leave]
it is coming up with an error.... I can use Select case but I should not need to as this should work?
Any ideas?
View 2 Replies
View Related
Apr 21, 1999
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
View 1 Replies
View Related
Aug 23, 2013
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees,
(
SELECT COUNT(gifts.donor_id) AS Count2
FROM gifts
WHERE gifts.donor_id = ed.id_number
) subquery2
[code]....
View 1 Replies
View Related
Jul 31, 2014
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies
View Related
Oct 16, 2006
My boss has a crazy idea that he want to implement using SQL 2005 and ASP.NET 2. Its something like this... If a user retrieve a record and someone is updating that same record a control on a asp.net page would warn that user that its being updated. Just like a stop light, green for go, yellow for warning, and red for stop. Were implementing optimistic here not pessimistic. Is this possible? I know pessimistic can lock that certain record. But what about in optimistic? Is it possible to monitor or know if a certain record or row in SQL 2005 is being access, updated, and deleted? How?
View 3 Replies
View Related
Feb 14, 2001
Hi,
How to hide my database with the users, I am deploying my application, i want to hide my database to client. Please help me regarding this.
Rajesh.
View 2 Replies
View Related
Sep 22, 2000
Our company is looking for fulltime Java/ASP/SQL gurus immediately.Big pay.If interested please send resume to polasp@hotmail.com.
Thanks!
View 1 Replies
View Related
May 14, 2002
I have 2 tables :
tab1 col1 char(10), col2 char(10), col3 char(10)....Primary key on col1+col2
tab2 col4 char(10),col5 char(10),col6 char(10)......Primary key on col4+col5
I want to insert a row from tab2 if its not there in tab1.
And I want to update col3 of tab1 from col6 of tab2 if col1+col2 matches col4+col5.
Any thoughts on doing these 2 tasks. I am sure these will be 2 differnet commads.
SQL Gurus please help....
jfk
View 4 Replies
View Related
Jul 23, 2005
Hi,I have a table in which two fields(FirstID, SecondID) together make theprimary key for the table. Let's look at the following example:FirstID SecondID******* ********2 22 32 55 55 78 79 1011 12Here is what I am trying to do. For a given FirstID (say, 2) find allSecondID with this FirstID (2,3,5). Now for these second ID's is therea FirstID other than 2? (Yes, 5). I would say FirstID=2 is related toFirstID = 5. Extending the logic FirstID = 5 is related to FirstID = 8.I am trying to write a SQL (some kind of self join, I think) which willgive me all related FirstID for a given First ID. For First ID = 2 theSQL will give me 5 and 8 in the above example. For First ID = 9 the SQLwill give me 11.Any help will be greatly appreciated. Thanks!!-Raj
View 6 Replies
View Related
Aug 30, 2005
Hi guys,we has accidently restored our Database with a back up file created months ago. we have no back up for the huge data and Stored procedures ( I know it is very stupid :( and i m sad abt it all ), guys is there any way by which i can get restore my db back to what it was before restoration.the flks in team have also deleted(shift + delete) the transaction log file ( I know it is very stupid again :( and i m very sad abt it all )..please adviceThanx in advance ashish
View 2 Replies
View Related
Oct 24, 2000
We need to return the results of a query as a single string.For example if the query returns 10 rows,all the 10 rows should be concatenated and put it in an output @returnstring. Is that possible in SQL stored procedures ??
Thanks!
View 1 Replies
View Related
May 19, 2002
SQL Server Load Balancing -- Any ideas?
There is no inherent mechanism available in SQL Server (replication, log shipping, or clustering) which allows you to load balance you database server.
Clustering is only useful for a failover situation and does not allow active/active balancing.
Is is possible to use merge replication between two identical OLTP servers
and manage transactions via MSMQ? Will this mechanism allow for a load balanced OLTP server?
Will this work? If not, why not?
What will work? Will federated servers work for an entire database??
View 1 Replies
View Related
Jul 15, 2002
I need to determine the differences between two tables with the same structure. The primary key for these tables would be a combination of all the columnhs. They use the ID field to join all their records. (i know ID is a reserved word - the client made the tables) Below is the structure of the tables.
TableA
and
TableB
----------
ID int
ComBank int
inboth int
EqorMA int
TranType int
I need to know the rows that Are different in TableA from TableB.
Any help would be appreciated.
Thanks
James
View 2 Replies
View Related
Oct 9, 2001
Hi there!
I'm trying to insert a MSWord/Bitmap/Powerpoint file into a table in SQL 2000. Unfortunately, I haven't had the luck to get it work. Here is my table structure :
Create table tbl_blob (
doc_id varchar(20) not null,
doc_blob image null )
My question is, do you have any script on how I can insert a MSWord/Bitmap file in my blob table. I will be running the script using Query Analyser.
thanks!
100NA
View 3 Replies
View Related
Jun 28, 2006
i realize i am starting to ask a lot of questions here so, thanks to all who tolerate me.
In my previous post, i figured out (with the help of r937) how to create a non-identity column using a cursor. I now have some resulting questions.
1. where do cursors live? there is no folder like triggers or stored procedures. is a cursor its own file or is it embedded in a stored procedure?
2. how do i get my cursor to "act" upon my stored procedure? I want the values in my cursor's "relativeposition" column to replace the values in my stored procedure's photo_number column. how do i do this?
BELOW IS MY STORED PROCEDURE:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
--SET ANSI_WARNINGS OFF
INSERT INTO PersonalPhotos
(photo_name, photo_location, photo_size, user_name, photo_caption, photo_default, photo_private, photo_number, photo_date)
VALUES (@photo_name, @photo_location, @photo_size, @user_name, @photo_caption, @photo_default, @photo_private, @photo_number, @photo_date)
--SET ANSI_WARNINGS ON
END
RETURN
AND HERE IS THE CURSOR:
Code:
DECLARE RelativePositionCursor
CURSOR LOCAL FAST_FORWARD FOR
SELECT photoId, photo_date
FROM PersonalPhotos
ORDER BY photo_date ASC
OPEN RelativePositionCursor
DECLARE @curs_id INT
DECLARE @curs_date DATETIME
DECLARE @totalRows INT
DECLARE @firstRow INT
SET @firstRow = 0
DECLARE @Results TABLE
(
curs_id INT NOT NULL PRIMARY KEY,
curs_date DATETIME,
RelativePosition INT
)
FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @totalRows = (SELECT COUNT(*) FROM PersonalPhotos)
WHILE(@firstRow <= @totalRows)
BEGIN
SET @firstRow = @firstRow + 1
BREAK
END
INSERT @Results
VALUES (@curs_id, @curs_date, @firstRow)
FETCH NEXT FROM RelativePositionCursor
INTO @curs_id, @curs_date
END
CLOSE RelativePositionCursor
DEALLOCATE RelativePositionCursor
SELECT *
FROM @Results
ORDER BY curs_date
View 1 Replies
View Related
Jul 20, 2005
The Scenario:We have some data that can be in three states. One is a saved state,a temporary state, and a complete state.The complete state is the bulk of the data. This data will berequested 1% of the time, maybe even less.The saved state and temporary state will only number 10-15 records.This data will be used 99% of the time, very frequently.So to summarize there will be 10-15 records that will be used ALL thetime…. And 50,000-100,000 that will be used very infrequently.The programmer that I work with wants to combine the tables. I wantto keep the tables sepperate.My logic is that it will be much faster to work with 10-15 recordsthan sift threw 50-100k most of which are unnecessary to 99% of therequests. Also there are frequent deletions/modifications in thetemporary table(10-15 records). The complete table can NOT havemodifications or deletions (50-100k records). The optimization forthese tables is also very different.He says that since the structures are almost identical they should bein the same table, that we should just use a field to denote thedifferent types of records. He insists that my view is wrong and thatI know nothing about databases.I may be new to SQL, but am I really that out to lunch? Would it notmake sense to keep these tables separate?(Thank you for taking the time to read this)Sat
View 14 Replies
View Related
Jul 20, 2005
I am not good at SQL - hence here goesConsider this scenario of 2 tables X and Y with a many to manyrelationshipTable X (name,weightage)X1 2X2 1X3 5X4 1Table Y (name ,attrib)Y1 attrib1Y2 attrib2Y3 attrib3Y4 attrib4Relationship table Z (id,id)X1 Y1X1 Y2X2 Y1X3 Y1X3 Y2X3 Y4X4 Y4So based on Z the distribution (count of Y/ Total Y ) is likeY1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7Now I want to factor in the weighatge column in X alsoSoX1 Y1 2*1X1 Y2 2*1X2 Y1 1*1X3 Y1 5*1X3 Y2 5*1X3 Y4 5*1X4 Y4 1*1Sum: 21Given these tables ,How would I factor in the weights and get thedistribution in a single SQL query.Desired Output of SQL QueryId AttribDistrib--------------------------------Y1 attrib1 100*8/21Y2 attrib2 100*7/21Y3 attrib3 0Y4 attrib4 100*6/21ThxJP
View 19 Replies
View Related
May 2, 2007
Greetings All,
I have a SQL2005 table that I'm needing to export to a 'formatted' csv flat-file to emulate a mainframe output. I'm new to SSIS so don't beat me up to bad... The current output of my ssis csv file is as follows:
95752,95725,0001,0, ,N
11473,8704,0001,0, ,N
1279215,436612,0001,0, ,N
43595,43592,0038,0, ,N
and I need to get to here:
95752 ,95725 ,1 ,0 ," ","N ",0
11473 ,8704 ,1 ,0 ," ","N ",0
1279215 ,436612 ,1 ,0 ," ","N ",0
43595 ,43592 ,38 ,0 ," ","N ",0
Basically I need to pad the first 2 columns with trailing spaces to a fixed width of 11 characters, 3rd column convert number format and pad to fixed 5, 4th column pad to fixed 3, 5th column add double quotes around 20 character fixed. 6th column add double quotes around and pad to fixed 2. Finally adding a , 0 to each row which is used a LF/CR.
Any feedback in regards to getting pointed in the right direction would be greatly appreciated...
View 5 Replies
View Related
Feb 20, 2008
I have a problem.. My code below works fine but when I want to add user to the database, it gives me error.
I added this part
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
and it gives error here
MyCmd.ExecuteNonQuery()
Thanks Protected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
Dim name, address, nice As StringDim sConnString, sSQL As String
Dim u As MembershipUser 'Receiving values from Form, assign the values entered to variablesname = Request.Form("name")address = Request.Form("address")
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()
'comments = Request.Form("comments")'declare SQL statement that will query the database
sSQL = "INSERT into test (name, address, user) values ('" & _
name & "', '" & address & "', '" & nice & "')"
'define the connection string, specify database'driver and the location of database
sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString() 'create an ADO connection object Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection)
MyConnection.Open()
MyCmd.ExecuteNonQuery()End Using
Response.Write("The form information was inserted successfully.") End Sub
End Class
View 7 Replies
View Related
