I have 2 tables. Plan and User. In my User table I have the following columns
UserId ClientId Login Password PlatformId
1 1 abc XXX 1
4 1 jcn XXX 2
20 1 djfd XXX 1
and the Plan table
PlanId PlanName
1 abc plan
2 Plan etc..
and ihave a sproc that will return the all the plans based on the clientId but i want to also get all the plans that have PlatformID 1 for the same client. This is my Sproc..
SELECT
NULL PlanId,
'< All Plans >' PlanName
UNION
SELECT Distinct
PlanId,
PlanName
FROM
Plan cp
Join User u on cp.ClientId = u.ClientId
WHERE
u.PlatformId = 1
AND
cp.ClientId = @ClientId
OR
(
cp.ClientId = cp.ClientId
AND
@ClientId IS NULL
)
Hi all, I need some help please with an select query. here's so scenario:3x Tables linked: Vehicles, Requests & ResultsTable Vehicles - vId, make, model, priceTable Requests - rId, name lastname, requestedmake, requestedmodelTable Results - vId, rId I am trying to do a select on the Vehicles table where vId is not already also in the Results table for an given rId What happens on the app: 1. dealers add cars on website - populate Vehicles tbl. 2. clients do a request for a vehicle - Populates Requests tbl. 3. dealers go page where they can asossiate their vehicles with requests - which populates the results tbl with both id's.All help is very much appreciated. RegardsJacques
Hey guys, Hoping someone can help me ot here.. I am developing an SQL statement that can be run on DB2 and have got something already setup, but i need to modify it so that it only returns the highest record for a certain field.
let me explain/Show you what i have got, and what i want to acheive: The highlighted rows, is what i want to work with, as i only want to return the row with the highest value in ICPNLX (first Column)
Im hoping someone can help me out please, one person told me to use a sub-select query, but i am having trouble doing it.
SQL CODE:
Select icpnlx , IBPUSL, IBPUST, IBSUNO, XBXSMT, ICPUNO, IBITNO,ICPNLI, ICPNLS, ICREPN, IBRVQA-IBSDQA as REMAIN, MBWHSL FROM mvxbdtaCF3.MPLIND INNER Join mvxbdtaCF3.MPLINE On ICCONO = IBCONO And ICFACI = IBFACI And ICWHLO = IBWHLO And ICPUNO = IBPUNO And ICPNLI = IBPNLI And ICPNLS = IBPNLS INNER Join mvxbdtaCF3.XANLIN On ICPUNO = XBPUNO And ICPNLI = XBPNLI And ICPNLS = XBPNLS And IBCONO = XBCONO And IBFACI = XBFACI INNER JOIN MVXBDTACF3.MITBAL ON ibitno = mbitno and ibcono = mbcono and ibwhlo = mbwhlo WHERE (IBCONO = 200) And (IBFACI = '010') And (IBWHLO = '010') And (XBXSMT = '91730') AND IBITNO = '8552-6654' AND (ICREPN > 0) And (IBPUSL >= '50' And IBPUSL < '75')
Hi, Table A member_no - character 5course1 - character 1course2 - character 1 Table Bcourse_no - character 1course_name - character 20I'd like a resultant view that contains A.member_no, A.course1, B.coursename as COURSE1NAME, A.course2, B.course_name as COURSE2NAMEI know how to make the join and pick up the name for course1 but I DON'T know how to pick up both names of course 1 and course 2 at the same time.I'm thinking I have to make a join on one column and then join the result of that a second time to pick up the other column's value? Thanks in advance for any assistance,Stewart
Hi, My users table contains a field called researchInterestId which looks like this: 1, 5, 10 This is because users where allows to select multiple options when choosing their research interests. I have another table which contains the names of those research interests, which looks like this: researchInterestId researchInterestName 1 Biology 2 Cancer
My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10) Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called? Thanks Sam
Hi, I am trying to do a simple select statement which returns data from the table. The criteria is only if the count of the records is more than 10. Say, if a vendor called test exists 10 times in the table, I would like to display that vendor only, if less than 10, then that vendor won't be displayed at all. Here is what I have started.
Select vendor_id, vendor_name, Invoice_Date, Invoice_Number from Vendors where Invoice_Date Between '01/01/2007' And '03/03/2008' Group By vendor_id, vendor_name, Invoice_Date, Invoice_Number Having Count(Invoice_Number) > 10 This doesn't seem to work. What am I doing wrong here?
I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path.
I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belonging to that account that was established in the earlier query labelled as 'accountrequesting'. The script I have below returns all orders for all accounts even though I have tried a wide variety of endings on the Sql search on the orders table.
The code may look a little untidy and to advanced programmers may look rough but I am still learning.....please can anyone help.
sub Page_Load(sender as Object, e as EventArgs)
'collecting the user identity Dim user as string user=request("User.Identity.Name")
'running a query to establish what account they have access to all from users table Dim accountrequesting as string accountrequesting = "select account_id from users where username = " & user & "'"
'opening the data source and connection Dim ConnectionString As String = "Data Source=xxxx;Database=xxxx;User ID=xxxxxxx;Password=xxxxxxx" Dim Myconn as New SQLConnection(connectionstring)
'this is the line I am stuck on, the account id in this orders table needs to match against the value gained in accountrequesting above tried a lot of different endings
Dim MySQL as string ="select prodshipped, account_id from orders"
What is the correct syntax for querying the first 20 characters of amemo field?I'm trying toSELECT CAST (varchar(20) fieldname) as newfieldnameand i get a bunch of errorsCan someone please help me?MS Access DB used with ColdFusionThanks
I have a table consisting of two fields, OStype and OSversion, withentries like:OStypeOSversionsolaris 2.5solaris 2.6redhat 6.2redhat 6.2solaris 8redhat AS4solaris 10solaris 10redhat AS2.1redhat AS3redhat AS4I want to create a select statement that returns for each OS type, thetotal number of entries and for each version the total number ofentries.In the example the result would be:OStype OStype Count OSversion OSversion Countsolaris 52.5 12.6 18 110 2redhat 66.2 2AS2.1 1AS3 1AS4 2Thanks in advance for your help.Ian
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g
(326-229) + 278 = new measure_value.
This is for each id-product where the following 3 conditions meet:
the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.
thanks
Jay
p.s this what i have started with at the moment as a test however it dont work.
SELECT ID_PRODUCT, ID_MARKET, ID_BUCKET, ID_COLLECTION, ID_MEASURE, MEASURE_VALUE FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT, dbo.DPOUT_EXCEPTIONS_326.ID_MARKET, dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET, dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION, dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE, dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE' FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)
I'm very green with SQL so I could do with some advice please.
I need to pull some data from a table based on the year portion of a datetime field, so far I've got this...
USE MfgSys803
SELECT orderdate, ordernum FROM orderhed
WHERE ((SELECT CONVERT(VARCHAR(4),GETDATE(),111)) = (SELECT CONVERT(VARCHAR(4),ORDERDATE,111) FROM orderhed))
... the field 'orderdate' is the datetime. The purpose of the WHERE statement is to get the current year fromt he system and then compare this to the current year of the field 'orderdate'.
Unfortunately I get the error...
Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
... the 'SELECT CONVERT' portions of the WHERE work fine on thier own but I can't use them together.
I have 2 SQL 2000 servers. One has been added as a linked server. They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain. Thanks
With the OLEDB source, is it wrong to use a table / view as a source and only check the columns required or is it beneficial to write a select col1, col2 etc etc as a SQL command?
I cannot see any difference in performance between the two.
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
Hi, TABLES (Association CONTAINS COLUMNS AssociationID), (Group CONTAINS COLUMNS GroupID, GroupName, AssociationID), (GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved), (UserProfiles CONTAINS COLUMNS UserProfileID, UserID), (AllUsers CONTAINS COLUMNS UserID, FirstName, LastName) I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I. Note that Users can belong to more than one group and have more than one profile. I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
Hi I have a function which returns the periodId from the period table and the function is as follows: ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime) RETURNS int AS
BEGIN
DECLARE @month int SET @month = Month(@Date) RETURN CASE WHEN @month BETWEEN 1 AND 3 THEN 5 WHEN @month BETWEEN 4 AND 6 THEN 6 WHEN @month BETWEEN 7 AND 9 THEN 7 WHEN @month BETWEEN 10 AND 12 THEN 8 END END Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS. How do i check if all the three parameters have the same value.. Any help will be appreciated.. Regards, Karen
Hi, I have this query SELECT o.OrderId, o.OrderDate, o.CreatedByUserId, c.LoginId, o.Quantity, o.RequiredDeliveryDate, cp.PlanId, cp.ClientPlanId --cp.ClientId FROM [Order] o Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId Inner Join User c on o.CreatedByUserId = c.UserId WHERE --cp.ClientId = @ClientId --AND o.OrderDate BETWEEN @StartDate AND @EndDate ORDER BY o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840 16534/25/2008 10:15:42 AM378550 16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045 16624/25/2008 2:30:43 PM4174387130 16634/25/2008 2:47:14 PM154825 16644/25/2008 2:49:10 PM265085 it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button or user can fill 2 parameter and hit enter so on the other hand (2nd page) how should i query the database accordingly ....
I have some data that looks like this: field1(pk) field2 field3 563136 199535840A D2119562408 199615461C D2119562806 199748610A D2119547463 199748610A D2368562278 200255598A D2368562286 200255598A D2468 Field2 can have the same value. What I need is to return all records where field3 is not 'D2468' ever for field2. So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'. So, I can't simply say: SELECT a.field2FROM table1 a inner join table2 b on a.field2 = b.field2 and a.field5 = b.field5where a.field3 not like 'D2468' because it will still return record 5. Can anyone help me with this? Thank you!
I need help here. I have two queries one on each server and one does not work the way it should be. Can some help me here in figuring out if both these queries are same or no. If there is any difference, what is causing that difference. Thanks
Query1: SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName,Shipment.Zip, Shipment.WarehouseCd, Phone FROM Orders INNER JOIN ((NewWarehouse INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (Shipment.CountryCd = Warehouse.CountryCd) AND (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd)) ON Orders.OrderNum = Shipment.OrderNum WHERE BE = 5 AND AddressTy = 1 AND WarehouseCurrency.CurrencyCd ='USD' AND Active = -1 GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName, Shipment.Zip, Shipment.WarehouseCd, Phone ORDER BY Min(Shipment.OrderNum) DESC)
Query 2: (This is working) SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName FROM ((Orders INNER JOIN (NewWarehouse
INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) ON Orders.OrderNum = Shipment.OrderNum) INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd) INNER JOIN Warehouse ON (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd) AND (Shipment.CountryCd = Warehouse.CountryCd) WHERE (((Orders.BE)=5) AND ((Shipment.AddressTy)=1) AND ((WarehouseCurrency.CurrencyCd)='USD') AND ((NewWarehouse.Active)=-1)) GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName ORDER BY Min(Shipment.OrderNum) DESC
I am sorry I am not that good in sql as some of you may be. Thanks.
I have 2 tables: Table1 has a field [Description](varchar) 100 Table2 has a field [IDDescription] int and field [Description] varchar(100)
In table 2 I have IDDescription store but some time also description wich is not store in table2 I need to pull all description (if it is from table2 or table1). How do I do this?
I have a query that need to do something like this.
About the data: --------------- I have a rules table which has got all the rules, and a log table that has the logs which says whether the rules are satisfied or not over a point of time.
select rules, sum(decode(status,'pass','1')) as passed, sum(decode(status,'fail',1)) as failed from rulestable,logstable where rulestable.logid=logstable.id
Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.
Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.
The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).
consider i have these tables: tbl_requests id description owner
tbl_owners id name
tbl_RequestStatus id description
am looking for output look like this: OwnerName New Completed x 2 5 y 4 3
table requests holds number of requests where each request has an owner, table owners holds the owners names, table status holds values for a request status for example : new, in progress and completed, the query am looking for should show each owner with the number of request he/she is handling grouped by the status, so we can say that owner X has 2 new requests and 5 completed requests.
