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.
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.
Hello.... i have 4 tables in a databse(tab1,tab2,tab3,tab4). id is the attribute of TAB1 and id also foreign key of all the tables.....That mean id attribute exists on all the tables........so when i want to query to more than 1 table , i have to go for Joins...basically i am using innerjoin......... Can u tell me is there any other way to find the same output as the innerjoin......without using the keyword INNER JOIN for example ......SELECT * FROM TAB1 INNER JOIN (TAB2 INNERJOIN TAB3 ON TAB2.ID=TAB3.ID) ON TAB1.ID=TAB2.ID
another query........ SELECT *FROM TAB1,TAB2,TAB3 WHERE (TAB1.ID=TAB2.ID) AND (TAB2.ID=TAB3.ID) AND (TAB3.ID=TAB1.ID) above queries are same or not??????????
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 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 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
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 wheretableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20order by tableB.price DESC'What I need is, for each row returned I need information from a thirdand fourth table. tableC, and tableD.tableC has information ( the tableA.id = tableC.eventId) that I need toobtain tableC.accountId = tableD.accountId in order do select thethe binding information in tableD between a Vendor(name,address..etc..)and tableB.imageAny help would be greatly appreciated.
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?
Hi, i am using the DTC in my code to connect to two different servers on the network through a SQL query which is unfortunately very slow; can u please guide me with an alternative for the same
SELECT *FROM organizationWHERE (departmentID = divisionID) AND (divisionID = branchID) AND(branchID = sectionID) AND (sectionID = unitID)Is there anyway I can make this query more simlified w/o repeating thesame column in the where clause?thankss/RC
Hi everyone, I'm trying to come up with a replacement for @@IDENTITY, because I have SQL code I want to make more portable.
Original:ID = MyDataLayer.Execute("INSERT INTO X(a,b,c) VALUES(A,B,C); SELECT @@IDENTITY") Proposed solution: lock(MyDataLayer) ID = MyDataLayer.Execute("SELECT MAX(id)+1 FROM X") if(ID==null) ID=1 MyDataLayer.Execute("INSERT INTO X(id,a,b,c) VALUES(ID,A,B,C)") unlock(MyDataLayer) (This is of course pseudocode, for SQL Server I'd need SET IDENTITY_INSERT.)
Do you think the preceding solution is equivalent to the original? Do you know something better?
Equivalent should mean here, not necessarily generating the same ID's, but maintaining functionality and consistence all over the database.
is there a way to get around not using USE in a PROCEDURE?
I need to because I have a main site that inserts information into other DB's that i use for various subdomains. But without being able to use USE i cant select which database is needed.
My company develops software that is distributed to thousands of customers. We chose MSDE as the database engine. Over the past 4 months, we have spent countless hours with customers, Microsoft, Installshield and web searches trying to resolve issues with installing MSDE. The issues seem to vary by customer and most take a great deal of support time. We understood MSDE to be a product that requires little support but in hindsight, it appears that it requires a great deal of knowledge just to get installed. We make small steps but no leaps forward.
It has come time to evaluate other products. If there is a magic bullet, I would love to hear about it. In its absence, does anyone have success to share with other products?
Just curious, is there any alternative to SQLXMLBULKLOAD for shredding and loading very large (800 megs) XML files ? Due to the nature of the XML data sent to me (which I have no control over)I am having great difficulty loading data into tables. More specifically, I can load parent data but not the child data beneath it despite using sql:relationships.
I have a situation where my SQL works everywhere else but my COBOL compiler complains wherever I use PARTITION BY. I can't find a workaround for that problem so I would like to remove all the PARTITION BYs. I'm not confident that I can do this accurately and would like some help getting started.
Here is my simplest example:
SELECT FESOR.REGION, FESOR.TYPE, COUNT(*) OVER (PARTITION BY FESOR.REGION, FESOR.TYPE) FROM FESOR, FR where FESOR.phase = 'Ref' and FESOR.assign is null and FESOR.comp_date is null and FESOR.region = FR.REGION and FESOR.type = FR.TYPE and FR.REP_ROW='A' GROUP BY FESOR.REGION, FESOR.TYPE
What I'm looking for is a modified version of the SQL above which returns the same result set without using PARTITION BY.
In a stored procedure I'm processing, via a cursor, a table of, potentially, 100,000 rows on a daily basis. The only column in each row is a 12-byte transaction control number. I know that using cursors can cause performance issues. Is there an alternative to using a cursor that has less of a performance impact ?
Cameron writes "Thanks for taking a look @ my question....
Basically, is there an alternative to indexing that maintains the fast searching capability (or possibly faster)?
We maintain over 500 databases on a single SQL server and currently (the way I am told) the server is limited to indexing 256 databases, so we have to basically create a new database with ALL the searchable data and use it for searches. While this works, it seems like there should be an alternate method. Any suggestions?
