PaymentMethod table contains the following fields:
PaymentMethodID
PaymentMethod
ClearanceCharge
ProductSale table contains the following fields:
ProductSaleID
UnitCost
CheckoutID
Assume the PaymentMethod table contains the following data:
PaymentMethodID PaymentMethod ClearanceCharge
1 Cash 0
2 Maestro Dom 0.565
3 Electron 0.35
4 Solo 0.345
5 Visa Delta 0.37
6 Mastercard 0.195
7 Visa 0.195
8 Amex 0.266
I want to calculate sum(ProductSale.UnitCost) where PaymentMethod is NOT equal to Cash or Amex. I have mocked this up and it seems to work:
Code SnippetSELECT Checkout.CheckoutDate, Sum(ProductSale.UnitCost) AS ValueOfSales, Count(*) AS NumberOfSales, Sum(Checkout.ClearanceCost) AS StreamlineCost
FROM Operator INNER JOIN (Checkout INNER JOIN ProductSale ON Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID = Checkout.OperatorID
WHERE (((Checkout.PaymentMethodID)=2 Or (Checkout.PaymentMethodID)=3 Or (Checkout.PaymentMethodID)=4 Or (Checkout.PaymentMethodID)=5 Or (Checkout.PaymentMethodID)=6 Or (Checkout.PaymentMethodID)=7))
GROUP BY Checkout.CheckoutDate ORDER BY Checkout.CheckoutDate DESC;
I also want to calculate sum(ProductSale.UnitCost) where PaymentMethod=Amex. Again, I have mocked this up and it seems to work:
Code SnippetSELECT Checkout.CheckoutDate, Sum(ProductSale.UnitCost) AS ValueOfSales, Count(*) AS NumberOfSales, Sum(Checkout.ClearanceCost) AS AmexCost
FROM Operator INNER JOIN (Checkout INNER JOIN ProductSale ON Checkout.CheckoutID = ProductSale.CheckoutID) ON Operator.OperatorID = Checkout.OperatorID
WHERE (((Checkout.PaymentMethodID)=8))
GROUP BY Checkout.CheckoutDate
ORDER BY Checkout.CheckoutDate DESC;
These queries only return results where the ValueOfSales and NumberOfSales is not zero... i would like them to return results for each day even if the result is zero...
Also, I want the results from both queries as one result set i.e. I want the results to look like:
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 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.
I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState JOIN tblProduct ON tblProduct.id_Product = tblProductState.id_Product WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total, MAX(RecJobAds.PostedDt) as PostedDt, MAX(RecJobAds.JobTitle) as JobTitle, RecJobAds.JobId, MAX (AgencyCandidate.AgencyId) as agentID, MAX(RecJobAds.AdStatus) as status, MAX(RecJobAds.CompanyId) as CompanyId, MAX(RecJobAds.RecId) as RecId FROM RecJobAds LEFT JOIN AgencyCandidate ON RecJobAds.JobId = AgencyCandidate.JobId GROUP BY RecJobAds.JobId ORDER BY Total ASC
i have the above query. but i would like to join in a third table, where my query is
Code:
select * from RecruiterMA where Activated = '1'.
anyidea in which place i can put the second query in the first query? thank you very much!
I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
Hi, im searching for some help, here is my problem:i have an Appointments Table ex:Appointments AppointmentID : int ContactsList : xmlstruture of the ContactsList column:<ArrayOfContactsInfos> <ContactsInfos> <ContactID>1</ContactID> </ContactsInfos> <ContactsInfos> <ContactID>2</ContactID> </ContactsInfos></ArrayOfContactsInfos>And my Contacts Table:Contacts ContactID : int FirstName : varchar(200) LastName : varchar(200)What i want to do... is find the right query for retreive a list of Appointment based on the FirstName or LastName of a Contact in the ContactsList collumnis this possible to do a sort of join between the xml and the Contacts Table?and if yes how?Please can you help me?
Hello,There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name) table B holds the names for gomA and gomB in tableA.I wish to select A table and inner join to it gom_name for gomA and gomB IDs.The problem begins when in table B there is multiple values for gom_id, with difference names.So, to retrieve the correct B.gom_id, there is 2 conditions. first bot_date>B.hist_date and after this, if we still gets multiple records then the top record will be selected (order by date) Any ideas?
I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author FROM news LEFT JOIN users ON news.news_author = users.user_id WHERE news_id =41
SELECT CONCAT('users.user_fname', 'users.user_lname') *AS edit FROM users WHERE users.user_id=54
my previous attempts are
trouble with query.
I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.
now when I'm editing the article I want to get the name of the author from the users table.
I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.
they both need to lookup the user_fname and user_lname fields from the users table.
my current sql is below
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41
which gives me
Code:
news_id *news_subject *news_article *news_author *news_date *news_edited *edit_author *org_author * 41InterclubBunbury IC club has asked us all to attend a inter...771090247547109041836254Adam Green
Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ??? every way I try doesn't work.
this is what I'm currently trying to get it with.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author FROM news LEFT JOIN users ON news.news_author = users.user_id LEFT JOIN (
SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author FROM news WHERE news.edit_author = users.user_id ) WHERE news_id =41
Heres a different approach that I thought would work
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author FROM news LEFT JOIN users ON news.news_author = users.user_id LEFT JOIN (
SELECT concat( users.user_fname, users.user_lname ) AS edit FROM users WHERE users.user_id =54 ) AS edit WHERE news_id =41
but no i get this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near '( SELECT concat( users.user_fname, users.user_lname ) *AS edit FROM users WHERE' at line 4
I also tried
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author, *(SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41
but got this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname ) *AS edit FROM users WHERE us' at line 2
can anyone help me with this query its been driving me up the wall all morning.
how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....
Select Distinct P.CustID, P.Acct, P.Title, P.FirstName, P.LastName, A.Trips, A.MoneySpent, A.Bal FROM dbo.Cust P INNER JOIN (SELECT CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent, Sum(Balance) as Bal FROM dbo.CAsh Where Accumulator='DP' GROUP BY CustID) A ON P.CustID = A.CustID
The 2 new tables are ViewDis (X) - Sum (Distance)and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.
Select Distinct P.CustID, P.Acct, P.Title, P.FirstName, P.LastName, A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building FROM dbo.Cust P INNER JOIN
i have two tables field1 is the primary key: table1 -------------------- field1|field2|field3 ---------------------
table2 --------------------- field1|field4|field5 --------------------- what we need is to get the results based on field1 matching datas, but is also a requirement that if there is no matching data in field1 of table2 w.r to table1 we just need to return table1 datas with null values for table2 fields.
is that possible, i used inner, right joins but all in vain.
I have two tables and one linking table they look something like this
<<<<<<<Application Table>>>>>>
AppID AppName 1 MS Word 2 Excel 3 SoftGrid
<<<<<<System Table>>>>>>>>
SysID SysName 1 System 1 2 System 2 3 Server 1
<<<<<<Link Table>>>>>>>>>
SysID AppID 1 1 1 3
How can i retirve the data so that it picks up and displays all the applications that are not on the the server as well as those that are
for example the output im trying to get is as follows from the data above.......
SysName AppName Y/N System 1 MS Word Y System 1 Excel N System 1 SoftGrid Y System 2 MS Word N System 2 Excel N System 2 SoftGrid N Server 1 MS Word N Server 1 Excel N Server 1 SoftGrid N
i want to able to get a dynamically generated Y/N colunm
Im ok with doing innerjoin queries but the problem is how do i get the rest of the results that have no links to show that there it does not exist.
Hi, I need to write a query to get the data in hierarchy from a table. Like emp table where each employee(EMPID) has an manager and manager is also an employee(EMPID). There could be 2 level of managers. Mean an employee who is a manager can also have manager. So i need to write query which shows three columns with column1=toplevel manager, column2=secondlevel manager and column3=team member.
select distinct safetyvideo2007.user_id,safetyvideoview.name from safetyvideo2007,safetyvideoview where safetyvideo2007.user_id=safetyvideoview.user_id order by safetyvideoview.name asc
this retrive records only where first table user_id= second table user_id
table1 = safetyvideo2007 user_id colum table2 = safetyvideoview user_id and name column
I want the it retreive records from safetyvideo2007 all records if first table only have user_id column no name column available.
ie. show table safetyvideo2007 all user_id if second table safetyvideoview user_id does not found ? ie. it found user_id in second table then ok otherwise it retreive all records from safetyvideo2007.
kind of a tough one i think but any help is appreciated. please try tostay away from T-SQL...I have a table with products and closing dates for each of 4 quartersand annual for several years back. (Earning_Dates)I have another table that has those products with dates and closingprices. (undPrices)i have a third table which has the id for all the products.I need a query that will look at earning_dates and return the latest 6records for each product.ALSOa query which will do that as well as return the closing_price at eachof those 6 dates for each of the products in earning_dates.parts of the tables:select top 10 *from WTGlobal.dbo.Earnings_Datesselect top 10 *from [wtchi-sqldb].pnl.dbo.undpricesorder by Trade_date descselect top 10 *from [wtchi-sqldb].pnl.dbo.productsProduct BB_exch Expected_Report_DtExpected_Report_Period-------------------------------- -------------------------------------------------------------------------------------------------A 3 2005-11-14 00:00:00.0002005:AA 3 2005-11-14 00:00:00.0002005:Q4A 3 2005-08-15 00:00:00.0002005:Q3A 3 2005-05-16 00:00:00.0002005:Q2A 3 2005-02-14 00:00:00.0002005:Q1A 3 2004-11-11 00:00:00.0002004:AA 3 2004-11-11 00:00:00.0002004:Q4A 3 2004-08-12 00:00:00.0002004:Q3A 3 2004-05-17 00:00:00.0002004:Q2A 3 2004-02-17 00:00:00.0002004:Q1(10 row(s) affected)Product_id Price_Open Price_Close Trade_DateTrade_Date_PrevTrade_Date_Nextumi----------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------7968 4.38000 4.43000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017963 23.32000 23.84000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017961 6.77000 7.20000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017960 10.05000 10.12000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017959 16.97000 15.99000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017958 15.72000 15.98000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017957 66.62000 70.59000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017956 31.35000 31.62000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017955 5.15000 5.09000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.00017953 5.25000 5.34000 2006-02-1600:00:00.000 2006-02-15 00:00:00.0002006-02-17 00:00:00.0001(10 row(s) affected)Product_id Product Currency_id Locale_idumi update_date----------- -------------------------------- ----------- ---------------------- ------------------------------------------------------3594 .DJX 1 40 12006-02-16 08:55:39.8103595 .MNX 1 40 12006-02-16 08:55:39.8103596 .MOX 1 40 12006-02-16 08:55:39.8103597 .NDX 1 40 12006-02-16 08:55:39.8103598 .OEX 1 40 12006-02-16 08:55:39.8103599 .OSX 1 40 12006-02-16 08:55:39.8103600 .RLG 1 40 12006-02-16 08:55:39.8103601 .RLV 1 40 12006-02-16 08:55:39.8103602 .RUI 1 40 12006-02-16 08:55:39.8103603 .RUT 1 40 12006-02-16 08:55:39.810(10 row(s) affected)
I'm trying to write a stored proc...Basically, I have a tblItems table which contains a list of every itemavailable. One of the columns in this table is the brand... for testpurposes, I hardcoded the BrandID=1...tblItems also contains a category column (int) which contains a categoryIDof 0..3...I then have a category table which has CategoryID, Name, and DisplayOrder...So basically what I'm trying to do is return a list of Category NAMES thathave items in them for a specifc brand... but I want to sort the returnedcategories by the DisplayOrder column...this is what I have now:select DISTINCT tblCategories.Name, tblCategories.DisplayOrder fromtblCategoriesINNER JOIN tblItemson tblCategories.CategoryID = tblItems.CategoryIDwhere BrandID=1 order by tblCategories.DisplayOrderthis does what I want it to do, but its returning TWO columns... Name ANDDisplayOrder... I only want to return Name, but if I take the DisplayOrderout of the select portion, it errors out because it can't order by that...Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies ofthe same category name.
I have a table:MasterFileID FileName ParentID1 Hello <null>2 World 13 Another <null>4 Again 1I want a query that will return the following result set:FileName ParentFileNameHello <null>World HelloAnother <null>Again HelloI believe this is a self-join, but I'm not getting it - any help would beappreciated!TIA
Hi,I have a question regarding join query syntax:In my DB there are Users table, and Transactions table (which has'TransactionDate' and 'UserName' fields). I would like to display alist of all users (each user will appear only once), and for each user- his last transaction date.Please advice.Thanks,Yaron
Hello, There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name)
table B holds the names for gomA and gomB in tableA. I wish to select A table and inner join to it gom_name for gomA and gomB IDs.
The problem begins when in table B there is multiple values for gom_id, with difference names. So, to retrieve the correct B.gom_id, there is 2 conditions. first bot_date>B.hist_date and after this, if we still gets multiple records then the top record will be selected (order by date)
Here is an example for what I wish to get: A has following values (1,'2007-02-04', 1,2) B (1,'2007-02-02','Name1') ; (1,'2007-02-09','Name2');(1,'2007-01-01','Name3') ; (2,'2007-02-01','Name4') ; (2,'2007-02-03','Name5')
Let's say A.bit_id=1, then, the bot_date is 2007/02/04, gomA=1, gomB=2, then the query will return from B table one record that answers 2 condition: A.bot_date>B.hist_date ('Name1' and 'Name3' ) , then from this records 'Name1' will be selected because this is the maximum date.
That's all was for gomA and now we need the name for gomB=2; bot_date is 2007/02/04 and so rows 'Name4' and 'Name5 are qualified, from this two records the biggest date is 'Name5''
and so, Name1,Name5 will be return by the function.
I guess the TOP function and the INNER JOIN can help here but I just failed to create such query...
Now I have the following query to get the data from both tables.
select b.Security_ID, b.Source_Code, b.Best_Bid, a.Best_Ask, b.Import_Date from @tblBestBidsForToday b left join @tblBestAsksForToday a on b.Security_ID = a.Security_ID and b.Source_Code = a.Source_Code and b.Import_Date = a.Import_Date
I would like to get all the data from both tables. But it seems that this query does not fully work. Because there may be records in the first table and not in the second or vice versa.
There may be a source_Code with an import_Date in the first table, this source_code can be present in the second table but not with all the same Import_Dates.
CURRENT RESULT (which is not quite correct): Note, in @tblBestAsksForToday, this record does not appear in the RESULT 125 GFI01 113.1250 2007-11-28 07:29:00
I have 2 tables, my vehicle data table and my config table. I need a query to join them by a datarow and a data column. Heres my tables...config table--------------------id name type--------------------1 make varchar2 model varchar3 color varcharveh table--------------------------id make model color--------------------------1 chevy s10 white2 ford ranger silver2 chevy blazer brownrecordset needed for veh.id=1---------------------------id name type value---------------------------1 make varchar chevy2 model varchar s103 color varchar white Thanks for any helpRyan
