We are in the process of buying a new server to run mssql. However
before this as a tempory fix to using a msaccess backend i believe
through odbc i need to address the following issue:
SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampaign,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNumber,
ai.street_address as Cust_AddressStreet,
ai.city as Cust_AddressCity,
ai.state as Cust_AddressState,
ai.zip as Cust_AddressZIP,
rec.file_name as AgreementRecordingFile
FROM agent_input ai, leads l, recordings rec
WHERE ai.whole_phone_number = l.whole_phone_number AND
l.call_status = 1110 AND
rec.whole_phone_number = l.whole_phone_number AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign AND
last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
ORDER BY ai.agent_login, ai.entry_date
I want to make the recordings entry optional so the same results come
out whether it matches a recording or not. If it does i want it to
populate the AgreementRecordingFile column above, if not just put a ''
as you would with '' as AgreementRecordFile.
Does anyone know how you can do this, in a access based database system
using SQL through i believe ODBC?
These two T-SQL statements return the same results.
If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN
-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER
USE AdventureWorks2008R2; GO SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder ;
USE AdventureWorks2008R2; GO SELECT DISTINCT Production.WorkOrder.ProductID FROM Production.Product INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID
In a single table I have 2 columns. Date | Number2006/09/01 - 2352006/09/03 - 2452009/08/01 - 230 I want to write a query that will return the AVG number between two dates. I am assuming this will require a JOIN but I'm having problems implementing my solution. I think about it, it's probably not a join but a sub query...I was trying the following: SELECT Date, AVG(Number) as AVERAGE_NUMBER FROM test.Table WHERE ( Date>='09/01/2006' AND DATE<='09/04/2006' ) But I keep getting date is invalid in the select list because it is not contained in either an aggregate funtion or a group by clause. Thanks in advance for your help.
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:
I have 3 tables I want to join:
Sale Table: Sale_No Cus_No Sale_Qty 1 Joe01 250
Order Table: Ord_No Sale_No Order_Qty ShipToCode 1 1 20 DestA 2 1 20 DestA 3 1 20 DestA 4 1 20 DestB 5 1 20 DestB
ShipTo Table:
Cus_No ShipToCode ShipToName Joe01 DestA Philadelphia Joe01 DestB Chicago Bob01 DestA Boston
A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.
I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).
What I came up with is this, but is this correct?:
FROM Sales INNER JOIN Orders ON Sales.sale_no = Orders.sale_no INNER JOIN ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND Sales.cus_no = ShipTo.cus_no
Which is of messages in a mailbox system, the MessageNum is my primary key, MailboxNum indicates which mailbox it is for and State indicates whether it is 'New' (value = 1) or 'Saved' (value = 0).
What I want to do is write a query to obtain a list of mailboxes, along with how many New and how many Saved messages they have, producing a result table like this:
My problem is I cannot seem to get my SQL right, so far I've got:
PHP Code:
SELECT NewQuery.MailboxNum, NewQuery.NewCount, SavedQuery.SavedCount FROM (SELECT MailboxNum, COUNT(1) AS NewCount FROM VMailMessages WHERE (State = '1') GROUP BY MailboxNum) NewQuery FULL OUTER JOIN (SELECT MailboxNum, COUNT(1) AS SavedCount FROM VMailMessages WHERE (State = '0') GROUP BY MailboxNum) SavedQuery ON NewQuery.MailboxNum = SavedQuery.MailboxNum
WHich works only if the mailbox has New messages as well as Saved messages. For mailboxes with only Saved messages, the count appears but, the MailboxNum is NULL. The opposite occurs if I change my SELECT clause to ask for SavedQuery.MailboxNum, but I really want both.
I feel like this is an easy question, but I can't describe it well enough to find the answer I need by searching. Anyway, in my spare time (I'm definitely not a SQL Query pro) I'm putting together a small app for our local Little League to help with scheduling.
I have 2 tables I need to join:
T1 = Schedule has the following fields: ID hTeamID (ID of Home team) vTeamID (ID of Vistor team) Time Date
T2 = Teams ID Name Other info...
I want to be able to do a SELECT statement on the schedule table and JOIN the team names for both home and visitor. I've tried a bunch of different ways but keep getting errors. I can think of 100's of reasons to join the same table more than once, but I still don't know how to and can't find the answer online.
I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating
I've got a simple ( I think) question on views. I've got a view that has a table join in it. With this view, we want to be able to perform updates, inserts, and deletes. At this time we can do the updates and inserts, but not deletes. I've checked the permissions and the users have SELECT, INSERT, UPDATE, and DELETE. Am I missing something or are deletes just not possible in a view with a join?
CREATE VIEW update_bd_view AS select D.BD_ID, D.BD_DESC, T.BT_TYPE_TID, T.BT_TYPE_FID, T.BT_JOB_FID FROM BILLING_DESC D JOIN BILLING_TIME T ON D.BD_ID=T.BT_ID GO
Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID
2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 5 O'Grady Patrick 1975-09-23 00:00:00.000 7 Greenfield Lynne 1955-07-26 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000
Any help in explaining where I have gone wrong here would be greatly appreciated.
select pname, cname from parent table a, child_table b where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1 Ben ben_Child2 Ben ben_Child3 ...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table. The makeid and orderdate belong to the OrderDetails table. The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
I want to be able to intersect many tables. I am building my query from vb code in asp .net based on key fields entered in a search engine box.my query should look like this, which will return the rows that will have the values var, var_2, var_3 in any columns. All three must be in a row for it to be a hit. I cant get this to work in ms sql. I don't know if it supports this feature.select * from t where column1 Like '%var%' or column2 like '%var%' or column3 like '%var%'intersect select * from t where column1 Like '%var_2%' or column2 like '%var_2%' or column3 like '%var_2%'intersectselect * from t where column1 Like '%var_3%' or column2 like '%var_3%' or column3 like '%var_3%' I also googled around and found a where exists... But cant seem to figure out how to do multiple tables:select * from t where exists (select * from t where column1 Like '%var_3%' or column2 like '%var_3%' or column3 like '%var_3%') i would like to add multiple conditions to the where exists table. Could anyone please tell me what I am doing wrong.
I have: 4 tables and 1 table variable. CCenters (ID, Name) Campaigns (ID, Name) Rel (ID, CCenterID, CampaignID) - [many to many] and @SCampaigns (ID, CampaignID) - represents the selected campaigns by the user
performing the commands below I would get the centers associated with the campaigns selected.SELECT CCenterID FROM Rel INNER JOIN @Campaigns ON @SCampaigns.CampaignID = Rel.CampaignID But what I really want are the common centers to the selected campaigns. Thanks
Hi,I've used the minus functionality which is available in Oracle andi would like to use it in SQL server, but i don't know how to. Thefolllowing is how it works in OracleSelect symbols from symbol_tableminusselect tsymbols from tradeIt returns a list of all the symbols from symbol_table which are notpresent in trade.Similarly, the intersect will return only those which are common toboth.I was wondering if someone throw some light on this problem for me.Thanks in advance,Sumanth
I am trying to update all records in #newtable that exist in #mastertable. I have been using Intersect to show me the duplicate records, but now I need to update a field in #newtable This was my syntax to show the records that exist in both tables, how can I change this to an update statement? The field in #newtable I want to update is [alreadyexists] and I want to update it with a yes value
So update #newtable set [alreadyexists] = 'yes' select uno, mucha, pablo, company from #Newtable intersect select uno, mucha, pablo, company from #mastertable
Hi,I'm coming back to Sql Server after 4 years away, using other RDBMS,and there's a few things I'm struggling to remember how to do (if Icould do them in the first place...)Main amongst those is EXCEPT syntax.In DB2, if I have two sets of data and I want to exclude the second setfrom the first, I can do:SELECT col1, col2, col3, ... colNFROM table1EXCEPTSELECT col1, col2, col3, ... colNFROM table2;But SQL Server balks at this. I've had a quick look in the T-SQL helpfor EXCEPT, but I didn't find that particularly enlightening. Anypointers as to how I should be doing this?ThanksJames
What i need to perform is a check that compares the data stored in both tables in order to know if there is any difference between them. Of course, the structure of both tables are the same.
I use the SP sp_addlinkedserver to link the servers, but the problem is that EXCEPT and INTERSECT didn't work.
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
