I'm using this statement to get info from 2 tables:
select holiday_notes.*, lookup.lookup_desc as type_desc from holiday_notes left join lookup on holiday_notes.[type]=lookup.lookup_id
where holiday_id=@holiday_id and delete_date is null order by create_date desc
It uses a specific id (holiday_id) to get the notes for that id.
What I need is all records from holiday_notes table, then the type (from holiday_id table) and also the holiday_name from Holiday_Ref table (which uses the holiday_id from holiday_notes table).I guess it's another join on the holiday_ref table? right join?
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database. The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records. I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered). I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these). I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes. I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem. So my question is: Is it normal for such a type of read query to take 90 seconds to complete? Is there anything I could do to speed it up. Any other thoughts? Thanks
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
Hello. What's the correct way of declaring a condition that selects two tables,with the following condition? Here's my code, it does not work. SelectCommand="SELECT * FROM [table_1, table_2] WHERE table_1_data IS NULL, table_2_data IS NULL" table_1_data is from table_1. table_2_data is from table_2.
Hi I will be thankful if any one help me with the queryI have 5 tables InventorySalesInvoiceMasterInventorySalesMasterInventorySalesInvoiceSalesDeliveryNodeIds with fields salesInvoiceId and salesDeliveryNoteIdInventorySalesReturnMasterInventorySalesInvoiceSalesReturnIds with fields salesInvoiceId and salesReturnIdI want to retrive datas from InventorySalesInvoiceMaster and the below query works fine but if salesReturnId is not present for a salesInvoice the qurey is not returning any value the query is select ISIM.salesInvoiceId,ICM.customerName,ISIM.salesInvoiceDate from InventorySalesInvoiceMaster ISIM,InventoryCustomerMaster ICM,InventorySalesMaster ISM,InventorySalesInvoiceSalesDeliveryNodeIds ISISDNID,InventorySalesInvoiceSalesReturnIds ISISRNID,InventorySalesReturnMaster ISRM where ISIM.customerId=ICM.customerId and ISM.salesId=ISISDNID.salesDeliveryNoteId and ISRM.salesReturnId=ISISRNID.salesReturnId and ISIM.salesInvoiceId=ISISRNID.salesInvoiceId and ISIM.salesInvoiceId=ISISDNID.salesInvoiceId and ISIM.salesinvoiceId=32Thanks in AdvanceAnu Palavila
Hi there,I want to select records from 3 tables. In SQL Server 2005, I'm using of "For XML" like this:Select *, (Select * From dbo.PageModules Where (PageId = 1) For Xml Auto) As Modules, (Select * From dbo.PageRoles Where (PageId = 1) For Xml Auto) As Roles From dbo.PagesThat works fine in SQL 2005 but not in SQL 2000, Because SQL 2000 does not support nested "FOR XML".Is there any way for selecting records from multiple tables by a query?Thanks in advance
Is there some sort of sql command where a tuple in a table has one of its cells updated depending on the value of a cell from another table. Please I would appreciate some help. Thanks
Hi How do I make a SELECT statement to use in WebMatrix to access data that selects from 2 tables: based on LastnameID it selects from table Contacts and based on first name and Lastname it selects from student tables regards daniel
I need to select data from within a stored procedure into a file, then FTP the file. Do I need to select it into a temporary table and bcp it or is there a better way?
In my new job I have to administer an existing SQL-database with approx. 50 tables. In this database are no joins :confused: defined between the tables. We use a Visual Basic 6 application to create a GUI and within this VB6 app. there are several SELECT statements to retrieve the required data. In these SELECT statements are all the INNER and OUTER JOINS between the tables defined. My question: is this a correct way to work with or is it better to create all the JOINs between the tables on the database itself? Or should I create different views and define the JOINs in there? My main concern is the speed to retrieve data and second the required time to administer this database.
how would i go about selecting data from a table...using the first letter of the staff_name field...
eg: i have a list at the top of my page..links, A - Z...for a staff directory page...and i want it so when you click on a link (A - Z), it display's only the people whose name begins with the letter clicked..
ive tried working my head arround it, using the left function to get the first letter...but i dont know how to structure my queries or my loop...
Table USERS Contains columns User_id and UserName Table DOMAIN Contains columns Domain_id and DomainName Table USER_DOMAIN Contains columns User_id, Domain_id, count, day, month, year
I am looking to run a report that pulls its information from USER_DOMAIN but instead of displaying User_id, Domain_id, it returns the UserName and DomainName associated.
The query to pull the info i need is very simple, where i am having problems is linking the user_id to the UserName and the Domain_id to the DomainName.
on sql-server-performance.com i read : Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO EXECUTE statement. If you do, locks on the syscolumns, sysobjects, and sysindexes tables in the TEMPDB database will be created, blocking others from using the TEMPDB database, which can significantly affect performance. [6.5, 7.0, 2000] Added 9-1-2000
I have a question does this negative effect also include simple SQL commands apart from stored procedures. For example if from vb i execute a "Select into" temporary table. Will this have the same negative impact as with executing this from a stored procedure ?
I have those 3 tables (sales,source, sales, sales_details),
Sales_source (table) id, name
1 Office 2 Post 3 Web
Sales (table) id, saleid, customer, source, date
01, 230, bill din, 1, 2013-10-22
Sales_details (table) id, saleid, object, delivery, date
01, 230, card, no, 2013-10-23 02, 230, box, yes, 2013-10-24 03, 230, car, no, 2013-10-31
And I want to create if is possible one insert command
And make the user complete the details of Sales_details (that they have inner join for the sales_source table so he can see only the sales_source_name and behind the sales_source_id to be stored)
And after to be able to add in as many [sale_details] he need for the select sale…
Is this possible to be done with one command ???
I think is not possible as i have made a small research on the web, and i will need to make one insert for the sales (table) independent and after to programming i guess a way to open the sale_details and add the rest….
hi this is probably a dumb question with an easy answer but does anyone know how to do this...
2 tables, one is a supplier list (id, supplier_name) and the other is an order list (id, order_name, supplier_id... etc)
basically i need to join the 2 tables together (id=supplier_id) but i need to display the results without the supplier_id and the supplier table id, only the supplier name?
do i have select only the specific fields I want? or can I exclude the supplier_id and the id from the supplier table?
Hi all, How can I run select statement for two tables that are on two different databases?
Let's say I have a SQL server 2005 on Windows 2003 server. On this SQL server 2000 I have two databases. Let’s say DB1 and DB2. On db1 I have Tbale1 and on DB2 I have Table2.
I like to run a select statment: Select Table1.*, Table2.* from Table1, Table2 Where table1.id = Table2.id And Table1.user_name like '%jim%'
Hi all! I just registred (very nice site) and have problem with getting some data from multiple tables, I would like to get result in one result set and best would be in one sql query.
I have DB for miniMessenger proggy, what i try to do is retrieve list of contacts.
Table containing user account information.
CREATE TABLE `account` ( `id_account` mediumint(8) unsigned NOT NULL auto_increment, `userdata_id` mediumint(8) unsigned NOT NULL default '0', `login` varchar(15) NOT NULL default '', `pwd` varchar(15) NOT NULL default '', `messenger_id` mediumint(8) unsigned NOT NULL default '0', `logged` tinyint(1) NOT NULL default '0', `ost_login` varchar(11) default NULL, PRIMARY KEY (`id_account`), UNIQUE KEY `messenger_UN` (`messenger_id`), UNIQUE KEY `userdata_UN` (`userdata_id`) )
Contact list, first field is contact number (like 4356789 - MESSENGER id) next to this number is its contact number, auth - if contact was authorised, ban selfexplained :) I just take every row with number 4356789 and get contact numbers next to it. CREATE TABLE `contacts` ( `contact_id` mediumint(8) unsigned NOT NULL default '0', `contacts` mediumint(8) unsigned NOT NULL default '0', `auth` tinyint(1) unsigned NOT NULL default '0', `ban` tinyint(1) unsigned NOT NULL default '0', KEY `Contacts ID` (`contact_id`) ) INSERT INTO `contacts` VALUES (4118394, 3333333, 1, 0); INSERT INTO `contacts` VALUES (4118394, 1234567, 0, 1);
Its table for messenger data, ID, status of contact (offline,online,ect), description, chat archiwum, CREATE TABLE `messenger` ( `id_messenger` mediumint(8) unsigned NOT NULL default '0', `status_id` tinyint(3) unsigned NOT NULL default '0', `description` varchar(255) NOT NULL default '', `archiwum` mediumtext NOT NULL, PRIMARY KEY (`id_messenger`) ) INSERT INTO `messenger` VALUES (1234567, 0, '', ''); INSERT INTO `messenger` VALUES (3333333, 1, '', ''); INSERT INTO `messenger` VALUES (4118394, 2, '', '');
Status is enumeration of status states(off,on,brb ect). CREATE TABLE `status` ( `id_status` tinyint(3) unsigned NOT NULL default '0', `stat` varchar(15) default NULL, PRIMARY KEY (`id_status`) ) INSERT INTO `status` VALUES (0, 'offline'); INSERT INTO `status` VALUES (1, 'Online'); INSERT INTO `status` VALUES (2, 'brb');
What i want to get is contact list + additional info of specific user by its messenger id. Like: id_messenger,contacts,auth,ban,stat
which is userID, contact ID, authorisation, ban, status
My query looks like this: SELECT id_messenger,contacts,auth,ban,status_id FROM account,messenger,contacts WHERE account.login = 'User' AND messenger.id_messenger = account.messenger_id AND contacts.contact_id = messenger.id_messenger
And it shows in stat only status of user of which i retrieve contact list. Please help me, im tired of working on this, im sure it is trivial :(
Please give your ideas about how to create a query from two tables, which have many-to-many relationship. For example, we have table Book and table Author and consider, that one author had wroten several books and one book had being wroten by several authors. So we have many-to-many relationship of these two tables. Usually this sutuation resolves by maens of creating the third table AuthorBook, which consist of key fields from the Author table and key fields from the Book table. So that the Author table is the parent table to AuthorBook, and the Book table is the parent table to AuthorBook. So we have two relations one-to-many. Now the question is how to get all related values from both tables, using JOIN operation and the conjunctive table?
Can someone explain to me why this would be considered "bad"? One thing that pops in my mind is that I really don't need all the columns from all these tables, only specific columns. Would this cause a performance issue when used in a stored proc for a transactional app?
SELECT * FROM CASE_XREF CX, CASE_RENEWAL_XREF CRX, RENEWAL_BATCH RB, PROPOSAL P WHERERB.MKT_SEG = @MKT_SEG AND RB.CORP_ENT_CD = 'oh' AND RB.RENEWAL_DT = '01/01/2008' AND CRX.TRIGGER_TYPE_CD = 'P' AND RB.BATCH_ID = CRX.BATCH_ID AND CRX.CASE_ID = CX.CASE_ID AND CRX.REN_PROSPECT_ID = P.PROSPECT_ID AND CRX.REN_PROP_NUM = P.PROP_NUM AND P.PROP_STATUS <> 'C' AND CX.ACCT_NBR = 123152
HiI'm using Access 2002. I have 2 tables tblGroupContact,tblGroupPermission, both have 2 fields identical structure:ContactID GroupID (Both are Composite keys and both hold integers)tblGroupContact holds everybody and the groups they are members of.tblGroupPermission holds only those people who have permission to makechanges to another part of the DB.The SQL at the end of post works, but opens a dialogue box looking fora parameter value 'query1.ContactID'Clicking enter or cancel (without entering anything) works OK.What have I done wrong to cause this parameter request.Thanks ColinKSELECT tblGroupContact.ContactID, tblGroupContact.GroupIDFROM tblGroupContact LEFT JOIN tblGroupPermission ON(tblGroupContact.ContactID = tblGroupPermission.ContactID) AND(tblGroupContact.GroupID = tblGroupPermission.GroupID)WHERE (((tblGroupPermission.ContactI*D) Is Null) AND((tblGroupPermission.GroupID) Is Null));
Hi,my query is:SELECT a.symbol_art,b.node_keyFROM artykuly a, art_podz bWHERE a.symbol_art=b.symbol_art------------- -------------------table: artykuly table art_poda.symbol_art b.symbol_artAA-0001 AA-0001 = record okAA-0002 NULL = >>>>>>>>I want to view diferencerecords symbol_art in two tablesHow select all a.symbol_art where in table art_podz b.symbol_art is no exists?tnx.Tom
So my aim is to display ALL DATA for each of these tables.
Tried the below but doesn't return any rows...
Code SnippetSELECT gd.Quantity, c.Comments, gc.GPositionID, cc.CPPositionID, cd.PositionDate FROM ReconComments AS c INNER JOIN RGCrossRef AS gc INNER JOIN RGData AS gd ON gc.GPositionID = gd.PositionID ON c.GPositionID = gc.GPositionID INNER JOIN RCPData AS cd INNER JOIN RCPCrossRef AS cc ON cd.UniquePositionID = cc.CPPositionID ON c.CPPositionID = cc.CPPositionID WHERE gc.ForcedMatch = 'yes' AND cc.ForcedMatch = 'yes'
Hello! I've got the following procedure: ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS select A_ProspectPipeline.ID, (case when [Completion Date] is null then '13' else case when YEAR([Completion Date])>year(GETDATE()) then '13' else case when YEAR([Completion Date])<year(GETDATE()) then '1' else month([Completion Date]) end end end)- (case when YEAR([Start Date])=year(GETDATE()) then month([Start Date]) else case when YEAR([Start Date])<year(GETDATE()) then '1' else '13' end end)as [CY],
(case when [Completion Date] is null then '13' else case when YEAR([Completion Date])>year(GETDATE())+1 then '13' else case when YEAR([Completion Date])<year(GETDATE())+1 then '1' else month([Completion Date]) end end end)- (case when YEAR([Start Date])=year(GETDATE())+1 then month([Start Date]) else case when YEAR([Start Date])<year(GETDATE())+1 then '1' else '13' end end)as [NY]
from a_ProspectPipeline where A_ProspectPipeline.ID = @ID
What i need to do is insert the two returned values [NY] + [CY] into two different tables. Can anyone help me with this?
How do I Query two tables and minus the result to be displayed in a gridview. I will appreciate all the help that I can get in this regard. Find below my two select statement 1st Select StatementDim SelectString As String = "SELECT DISTINCT [Course_Code], [Course_Description], [Credit_Hr], [Course_Type], [Course_Method] FROM [MSISCourses] WHERE (([Course_Type] = Core) OR ([Course_Type] = Information Integration Project) "If radBtnView.Checked = True ThenSelectString = SelectString & " OR ([Course_Type] = 'Knowledge')"End IfIf chkGView.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Data Management')"End IfIf chkGView2.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'General')"End IfIf chkGView1.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Electronic Commerce')"End IfIf chkGView3.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Network Administration and Security')"End IfIf chkGView4.Checked = True ThenSelectString = SelectString & " OR ([Specialization] = 'Healthcare Information Systems')"End IfSqlDataSource3.SelectCommand = SelectString 2nd Select Statement"SELECT DISTINCT [Co_Code], [Co_Description], [Cr_Hr], [Co_Type], [Co_Method] FROM [StudentCourses] WHERE ([Co_Code] = StdIDLabel)" my gridview<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="Course_Code" DataSourceID="SqlDataSource3" GridLines="Horizontal"><Columns><asp:BoundField DataField="Course_Code" HeaderText="Course_Code" ReadOnly="True" SortExpression="Course_Code" /> <asp:BoundField DataField="Course_Description" HeaderText="Course_Description" SortExpression="Course_Description" /> <asp:BoundField DataField="Credit_Hr" HeaderText="Credit_Hr" SortExpression="Credit_Hr" /> <asp:BoundField DataField="Course_Type" HeaderText="Course_Type" SortExpression="Course_Type" /> <asp:BoundField DataField="Course_Method" HeaderText="Course_Method" SortExpression="Course_Method" /> </Columns></asp:GridView>
I have a table of students, and a table of lecturers, and I wish to use one select statement to return a list of users.
What I really need to do is something like this:
SELECT Students.UserID AS UserID, Lecturers.UserID AS UserID, Students.FullName AS FullName, Lecturers.FullName AS FullName FROM Students, Lecturers
which would (in my dream world!) produce something like:
UserID FullName s0002131 Darren Student s0054242 Richard Student e13412 Michael Lecturer x92342 Linda Lecturer
Does anyone know how I should do this? Should I be doing it in an stored proceedure and returning the record set from that? Currently I'm only using ASP and one line SQL statements, and would like to continue doing so.
