Not sure if this is the right group to post this to but.
This is the current query that I have.
select tableA.id,tableB.artist,tableB.image,from tableA,tableB where
tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20
order by tableB.price DESC'
What I need is, for each row returned I need information from a third
and fourth table. tableC, and tableD.
tableC has information ( the tableA.id = tableC.eventId) that I need to
obtain tableC.accountId = tableD.accountId in order do select the
the binding information in tableD between a Vendor(name,address..etc..)
and tableB.image
I have two tables in which I need to select data from and I don't know what construct to use. The two tables are SY and MV. SY contains stocks and MV contains a log of all price changes of these stocks. I need to produce a report of price changes between the latest price and the previous price and take the difference between the two. I'm using MSSQL. Here are the important fields in my tables:
SELECT sy.syid, sy.sycode, mv.price, (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) AS lastprice, (mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) AS diff, (mv.price - (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC)) / (SELECT TOP 1(mv.price) FROM MV, SY WHERE mv.syid = sy.syid AND mv.date < '8/27/2007' AND sy.ACTIVE = '1' ORDER BY mv.date DESC) * 100 AS percentdiff, mv.date FROM mv, sy WHERE mv.syid = sy.syid AND mv.date = '8/27/2007' AND sy.ACTIVE = '1' ORDER BY sy.syid, mv.date DESC
I have two tables with the following relevant fields:
Apps appID appName
PBC pbcID appID appCT
These are joined on appID. appCT can be 1 of 2 values, either "PC" or "LA". So an example of a few records in PBC would be:
1 1 PC 2 1 LA 3 2 PC 4 2 LA 5 3 PC 6 4 LA ... ...
You can see that for each App, in PBC there can be two related records - PC and LA. But for example, record number 5 is App 3 PC, but there is no App 3 LA. I am trying to build a select to tell me which Apps are not in PBC at all, AND which Apps only have either LA or PC, not both.
I'm working on a purchasing website for a store. A request has many line items, and a line item can have many products. One of the characteristics of the line item data table is a total price, calculated from multiplying lineitems.quantity and product.price.
INSERT INTO lineitems (request_id, quantity, product_id, total_price)VALUES (@rid,@quant,@pid,@totalprice)WHERE @totalprice = (SELECT products.price * @quant FROM lineitems, products WHERE lineitems.product_id = products.id) Visual Studio isnt accepting this. Is there a way to do this better?
How do you combine the following 2 updates into one Update statement (1 SUBSELECT statement)
Update SPLL_Policy SET SPLL_Policy.Prog_Year = (Select TOP 1 Prog_Year From SPLL_WinsPolicy_Input Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
Update SPLL_Policy SET SPLL_Policy.Prog_NAME = (Select TOP 1 Prog_Name From SPLL_WinsPolicy_Input Where SPLL_WinsPolicy_Input.Policy_Number = SPLL_Policy.Policy_Number ORDER BY SPLL_WinsPolicy_Input.DATE_TIME_RECEIVED DESC)
I have the following table: CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1)) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0) INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)
-- Those works and gives me 11,12,21,22 SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3) SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2','3')
-- This doesn't works declare @Rule varchar(10) set @Rule='2,3' SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule) Any idea? I don't mind to change the data type if it works.
I have a query which contains 2 subselects joined with a union all. The select for each is just a count, so I'm only returning 2 rows. I then want to be able to perform a calculation between these 2 results... ie divide one by the other to get the percentage.
The only way I could think of doing that was make the whole query a subselect of another query where I could then perform the calculation in the new select statement, however it doesn't like this. I just get incorrect syntax near the closing bracket of the from section.
Any ideas? Thanks!
SELECT * FROM
(SELECT count(t0.product) FROM (SELECT t0.packslip , t1.date_upld , t0.product AS product , t0.qty_topick as topick , t0.qty_picked as picked , t0.qty_topick - t0.qty_picked as shorted, (t0.qty_picked / t0.qty_topick) * 100 as linefill FROM rbeacon.dbo.shipline2 t0 INNER JOIN rbeacon.dbo.shiphist t1 ON t0.packslip = t1.packslip WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101)) t0
UNION ALL
SELECT count(t1.product) FROM (SELECT t0.packslip , t1.date_upld , t0.product AS product , t0.qty_topick as topick , t0.qty_picked as picked , t0.qty_topick - t0.qty_picked as shorted, (t0.qty_picked / t0.qty_topick) * 100 as linefill FROM rbeacon.dbo.shipline2 t0 INNER JOIN rbeacon.dbo.shiphist t1 ON t0.packslip = t1.packslip WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-3, 101) AND t0.qty_picked <> t0.qty_topick) t1) t2
Hello,I have a problem with a subselect I use in a stored procedure:UPDATE #TEMP_TABLESET P_ID_1=(SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID),P_ID_2=PARENT_ID,P_ID_3=IDWHERE PARENT_ID IN (SELECT P_ID_2FROM #TEMP_TABLE b)So the subselect is (SELECT top 1 b.P_ID_1 from #TEMP_TABLE b whereb.ID=PARENT_ID), and it returns NULL. The cause of that is mostprobably the fact that I try to link ID from inner table b withPARENT_ID from the outer table. I thought it had to be done this way,but obviously not. Can somebody help me with this syntax problem?Thx,Bart
Is possible to use like hint with subselect? , i mean i want to find all rows in table A that contains a word in a field(CALLED CONTENT) in table B, concretely in a field called content too, i show you the idea although the syntax is incorrect.
select ' + char (39) + @country + char (39) + ' as PAIS, A.ID, A.IDUSUARIO MSISDN, NULL AS MSISDN_COD, convert(char(19),A.FECHA_ALVENTO, 121) AS FECHA_MO_LOCAL, NULL AS FECHA_MO_LOCAL_D,
Hope someone could help me in revising a long running query. Here is the query
select * from table1 where classid is null and productid not in ( select productid from table1 where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
Hope someone could help me in revising a long running query. Here is the query
select * from table1 where classid is null and productid not in ( select productid from table1 where classid = 67)
In here table1 could have several occurance of productid in which productid could have different classid. The possible values of classid are: NULL,1,2,3,67. Basically I am looking for all records whose classid is null but should never had an instance in table1 where its classid is 67.
Do you have something like a "join" statment that will only include all records in the left table that is not in the right table?
Hope someone could help me with this. Thanks in advance.
I have a stored procedure what produces N number of rows.The rows are ordered by a cataegoryType as followscatAcatBcatCWhat is needed to do on the C++ code side is break these out intotheir respective categories by iterating through the rows and checkingthe category type. Is there a way to let the DB do this via some sort ofsubselect on the rows returned via the stored procedure.Thanks in advance.
I have a sqldatasource, and on the selectcommand I'm trying to use a case statement with a subselect. The case statement works fine without the subselect, but I'm trouble getting it to work with the case statement. Could you help me with the syntax? ThanksSelectCommand=" SELECT DISTINCT RecipeID, Title FROM [Recipes] WHERE (CASE WHEN @Type='Appetizer' THEN Appetizer WHEN @Type='Pies' THEN (Select Distinct RecipeID, Title From Recipes WHERE Title like '%Pie%') WHEN @Type='Beverages' THEN Beverage WHEN @Type='Dessert' THEN Dessert WHEN @Type='Kids' THEN Kids WHEN @Type='Side' THEN Side WHEN @Type='Soup' THEN Salad WHEN @Type='Main' THEN Main WHEN @Type='Breakfast' THEN Breakfast END) = 1"
Help!I'm trying to understand the new ANSI join syntax (after many years ofcoding using the old style). I am now working with an application that onlyunderstands ANSI syntax so I am struggling.My first (old style syntax) SQL statement below produces 60 rows:SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1,ADDRESS A6WHERE A1.ADDRESSID=A6.ADDRESSIDAND A1.CONTACTID IN(SELECT A4.CONTACTIDFROM CONTACT_LEADSOURCE A4,LEADSOURCE A5WHERE A4.LEADSOURCEID = A5.LEADSOURCEIDAND A5.DESCRIPTION = 'some_description' )AND A1.CONTACTID IN(SELECT A2.CONTACTIDFROM TICKET A2,ENROLLHX A3,EVENT A7WHERE A3.STATUS IN ('R', 'Confirmed')AND A2.TICKETID = A3.EVXEVTICKETIDAND A3.EVENTID = A7.EVENTIDAND A7.CODE IN('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99'))ORDER BY A1.LASTNAME ASCI am trying to convert this to the newer ANSI sytax. My second SQL statementbelow produces 67 rows (duplicates):SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT,A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAILFROM CONTACT A1JOIN ADDRESS A6 ON (A1.ADDRESSID=A6.ADDRESSID)JOIN( SELECT C.CONTACTIDFROM CONTACT CJOIN CONTACT_LEADSOURCE A4 ON (C.CONTACTID= A4.CONTACTID)JOIN LEADSOURCE A5 ON (A4.LEADSOURCEID =A5.LEADSOURCEIDAND A5.DESCRIPTION ='some_description' )) AS C1 ON C1.CONTACTID = A1.CONTACTIDJOIN(SELECT C2.CONTACTIDFROM CONTACT C2JOIN TICKET A2 ON (C2.CONTACTID =A2.CONTACTID)JOIN ENROLLHX A3 ON (A2.TICKETID =A3.TICKETID AND A3.STATUS in ('R', 'Confirmed'))JOIN EVENT A7 ON (A3.EVENTID = A7.EVENTIDAND A7.CODE IN ('AHS00','AHS01','AHS02','AHS03','AHS04','AHS98',' AHS99')))AS C3 ON C3.CONTACTID = A1.CONTACTIDCan anyone shed some light on what I am missing?cheers,Norm
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp), CASE DATENAME(dw, sph.datestamp)Â Â Â WHEN 'Monday' then 'Monday'Â Â ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')Â END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
I was writing a query using both left outer join and inner join. And the query was ....
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN Â Â Â Â Â Â (Production.Products AS P Â Â Â Â Â Â Â Â INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier   country   productid   productname   unitprice   categorynameSupplier QOVFD   Japan   9   Product AOZBW   97.00   Meat/PoultrySupplier QOVFD   Japan  10   Product YHXGE   31.00   SeafoodSupplier QOVFD   Japan  74   Product BKAZJ   10.00   ProduceSupplier QWUSF   Japan   13   Product POXFU   6.00   SeafoodSupplier QWUSF   Japan   14   Product PWCJB   23.25   ProduceSupplier QWUSF   Japan   15   Product KSZOI   15.50   CondimentsSupplier XYZ   Japan   NULL   NULL   NULL   NULLSupplier XYZ   Japan   NULL   NULL   NULL   NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
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.
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
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?
We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.
INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date) SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)
[Code] ....
the count returned from both the queries is different.
I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name] from Contact right join Account on Contact.[Account Name] = Account.[Account Name] where Contact.[Account Name] = Account.[Account Name]
Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.
This is the simple sql (no join on the tables):
select 1.code, 2.rowdetail from tblcodes 1, tblelements 2
I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.) THANKS
