Transact SQL :: Retrieve Last Class Attended By The Student Using JOIN
May 14, 2015
In my query, I am showing Student Record and also want to retrieve the last class attended by the student using JOIN to the table (ClassHistory).
Master
ID Â Â Â Student_Name
1 Â Â Â Arindam
2 Â Â Â Brenda
3 Â Â Â Callie
(ClassHistory)
ID    Class    Year
1 Â Â Â 4 Â Â Â Â Â Â Â Â 2003
1 Â Â Â 5 Â Â Â Â Â Â Â Â 2004
1 Â Â Â 6 Â Â Â Â Â Â Â Â 2005
2 Â Â Â 4 Â Â Â Â Â Â Â 2003
2 Â Â Â 5 Â Â Â Â Â Â Â 2004
3 Â Â Â 4 Â Â Â Â Â Â Â 2003
DESIRED RESULTSET
ID   Student_Name   Class Record
1   Arindam                6
2   Brenda                  5
3   Callie                    4
The result is like - Arindam last class was Class 6, while Brenda was in Class 5 and Callie was in class 4.
Please note that the query will b part of a larger query and the Class record to be retrieved from ClassHistory if possible need to be retrieved through JOIN.
View 7 Replies
ADVERTISEMENT
Jun 9, 2015
We have SQL database now a days we are planing to update our students ID Records but i don't know the exact query for this. Table format is below
StudentIDÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Name
500132253/Prep              Aslam
112344883/Prep              Ali
451132537/Prep              Ahmed
Now i want to update only the grade from prep to KG-1 without update the registration number only prep to kg-1. in one column there is student register number and after slash the grade.
View 6 Replies
View Related
Feb 7, 2008
Hi all.
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass 80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen during very high traffic. I have isolated the query, included at the bottom of this post (cleaned up). There is very little info on this event class out on the web. Version is SQL 2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get the MJP. I would expect that a query without a JP is a query without a JP and it would be all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can’t make it happen in testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed light? Know of good links, etc?
Thanks!!
bbRichbb
SELECT
p.Product_Id,
MIN(ae.Enum_Value) AS color,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id,
COUNT(ps.SWATCH_STATUS) AS total_available_colors
FROM
Attribute_Enum_Value ae
INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
INNER JOIN Product p
INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
AND p.Product_Id = ps.PRODUCT_ID
WHERE
(pae.Attribute_Type_Id = 500001)
AND (p.Product_Class_Id = 2)
AND (p.Status_Code = 'ACTV')
AND (ps.SWATCH_STATUS = 'ACTV')
GROUP BY
p.Sequence_Number,
p.Product_Id,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id
HAVING
(p.Product_Id = 1209645)
ORDER BY
p.Sequence_Number,
p.Product_Id
View 1 Replies
View Related
Feb 7, 2008
Hi all.
I am a new SQL Profiler user trying to baseline our eCommerce site. I am receiving EventClass
80, Missing Join Predicate (hereinafter MJP), often enough to be concerned about what may happen
during very high traffic. I have isolated the query, included at the bottom of this post
(cleaned up). There is very little info on this event class out on the web. Version is SQL
2000, latest service pack. I know I don't have table DDL here; I'm just trying to get overall
direction without causing you much work/time.
Issues:
1. Even though only the value of product_id in the HAVING clause changes, I do not always get
the MJP. I would expect that a query without a JP is a query without a JP and it would be
all-or-none.
2. Although it happens maybe 20-30 % of the time in production, I can€™t make it happen in
testing.
Questions:
Anyone have experience with MJPs? How about the issue of why it's sporadic? Can anyone shed
light? Know of good links, etc?
Thanks!!
bbRichbb
SELECT
p.Product_Id,
MIN(ae.Enum_Value) AS color,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id,
COUNT(ps.SWATCH_STATUS) AS total_available_colors
FROM
Attribute_Enum_Value ae
INNER JOIN Product_Attribute_Enum pae ON ae.Attribute_Value_Id = pae.Attribute_Value_Id
AND ae.Attribute_Type_Id = pae.Attribute_Type_Id
INNER JOIN Product p
INNER JOIN Section_Product sp ON p.Product_Id = sp.Product_Id
INNER JOIN Section s ON sp.Section_Id = s.Section_Id ON pae.Product_Id = p.Product_Id
INNER JOIN PRODUCT_SWATCH ps ON ae.Enum_Value = ps.Color_Attr
AND p.Product_Id = ps.PRODUCT_ID
WHERE
(pae.Attribute_Type_Id = 500001)
AND (p.Product_Class_Id = 2)
AND (p.Status_Code = 'ACTV')
AND (ps.SWATCH_STATUS = 'ACTV')
GROUP BY
p.Sequence_Number,
p.Product_Id,
p.Product_Name,
p.Status_Code,
ps.Curr_Price,
s.Section_Id
HAVING
(p.Product_Id = 1209645)
ORDER BY
p.Sequence_Number,
p.Product_Id
View 1 Replies
View Related
Oct 8, 2015
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.
View 5 Replies
View Related
Jan 13, 2012
I want to join two tables of information together. I want to bring the order information together with the order charge. But since the OrderCharges table can have multiple values, I just want to retrieve the sum of those charges for a specific OrderID+ItemID.
My data looks something like this:
CREATE TABLE #OrderItems
(
OrderItemsID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
ItemID INT
)
SET IDENTITY_INSERT #OrderItems ON
[Code] ....
So I'm looking to see a result set that would like this this:
OrderItemsID-----ItemID-----OrderID----OrderDescription--OrderCharge
----1------------124---------1------------Shipping----------6.55
----2------------156---------1------------Shipping---------16.85
----3------------156---------2------------Shipping----------7.40
----4------------158---------1------------Shipping----------7.85
----5------------158---------2------------Shipping---------15.25
View 2 Replies
View Related
Jun 23, 2015
I want to reach and display the value of @data inside if [wwtest].
The UDT is from the database [test] and I retrieve an error message "The type name 'test.dbo.tvf_id' contains more than the maximum number of prefixes. The maximum is 1.".
I don't know how to retrieve the data from the database test inside of the query document for [wwtest]?
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
[Code] .....
View 4 Replies
View Related
Feb 21, 2008
Hai Guys,
I have a doubt Regarding SqlDataReader
i can able to create object to Sqlconnection,Sqlcomand etc...
but i am unable to create object for SqlDataReader ?
Logically i understand that SqldataReader a way of reading a forward-only stream of rows from a SQL Server database. This class cannot be inherited.
sqlDatareader belongs to which class is it sealed or static class?
can we create own class like SqldataReader .......
Reply Me ...... if any one know the answer..............
View 8 Replies
View Related
Jun 29, 2015
We are accessing a database through Linked Servers. That database has a bunch of views.We are able to get a list of columns for our views by querying [syscolumns]. However, how do we find out which of those columns have primary keys?
View 6 Replies
View Related
May 28, 2015
The below is my query in SQL SERVER 2012
DECLARE
@PN_INC INT,
@POSITION_DESC VARCHAR(15)
SELECT @PN_INC= MAX(PositionNumberInc) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
SELECT @PN_INC is returning null when there is no record in the table;
1. how can I make it return 0 when @PN_INC is null else it should pick the MAX(PositionNumberInc) value
2. I have written the below query to return 0 when @PN_INC is null but I need query to select the MAX(PositionNumberInc) value when @PN_INC is not nullÂ
SELECT @PN_INC= coalesce(MAX(PositionNumberInc), 0) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
View 6 Replies
View Related
Aug 31, 2015
I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
View 2 Replies
View Related
Jun 2, 2015
I have a table (can't change the schema of it since it is part of an off the shelf app ) that has columns for individuals which I need to extract several pieces of information, essentially Phone, Email Address, etc. See U1 - U6
What is a better way to return this information rather than multiple joins?
Select
[AccountNumber]
,a.[AccountId]
,[Name]
,[new_LocationID]
,[ws_name]
,[new_BillingManageruserName]
,[new_AreaServiceManName]
[Code] ....
View 3 Replies
View Related
Nov 22, 2015
I have a table of Affiliates, each Affiliate can have a ParentId, which refers to another Affiliate. How can I retrieve all children of a given Affiliate, along with their depth level? I have this query which selects the total number of descendants of a given Affiliate, but I need to select all the children + their depth level, up to a certain level.
WITH Descendants AS(
  SELECT RootId = Id, Id
  FROM Affiliates
UNIONÂ ALL
  SELECT RootId, Af.Id
  FROM Descendants
[Code] .........
What I want to achieve is a statement that has 3 parameters: RefererId, StartLevel, and EndLevel.
I need a statement that selects all the specified Affiliate.ReferrerId's descendants including their level distance from the referrer, filtered by the StartLevel and EndLevel (WHERE CurrentLevel >= StartLevel AND CurrentLevel <= EndLevel ).
View 2 Replies
View Related
Oct 29, 2015
I am trying to pick up the customers invoiced twice or more within a month. In the case below Pepsi Cola and Jack Daniel were invoiced twice in October. The query need to pickup the previous month, se being in October I need to pick up the invoice of September.
create table #forum (Customer varchar(20),Invoiced date)
insert into #forum values ('Pepsi Cola','2015-09-01') ,('Pepsi Cola','2015-09-06') ,
('Pepsi Cola','2015-10-01') ,('Pepsi Cola','2015-10-02') ,('Pepsi Cola','2015-11-01') ,
('Ferrarelle','2015-09-01') ,('Ferrarelle','2015-10-01') ,('Ferrarelle','2015-11-16') ,('Ferrarelle','2015-11-01') ,
('Jack Daniel','2015-09-01') ,('Jack Daniel','2015-09-04') ,('Jack Daniel','2015-09-06') ,('Jack Daniel','2015-09-30') ,
('Jack Daniel','2015-10-01') ,('Jack Daniel','2015-10-18') ,('Jack Daniel','2015-11-01') ,
('Bud','2015-09-01') ,('Bud','2015-10-01') ,('Bud','2015-11-01')
select * from #forum
View 5 Replies
View Related
Jul 16, 2015
I need a query to list the tables in SQL sever with respective database they belong across databases.
I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?
The Sys.Databases View list the  databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.
Which View will allow me to fetch database to table mappings?Â
View 5 Replies
View Related
Jun 8, 2015
I have to check for existence of mount points on drives and retrieve the Space information for those mount points. Is this even possible with t-sql??
View 2 Replies
View Related
May 25, 2015
Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query.Â
Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.
how i can achieve this query without using RANK() and other functions.
"SELECT  Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,
[Code] ....
View 7 Replies
View Related
Feb 1, 2008
Does any one inherit SqlDatasource class?
I treid it as :
public class MYDataSource : System.Web.UI.WebControls.SqlDataSource
{public MYDataSource(){
}
}
Debugger dont give any error or warning when i buld project. But when i use it in a page Visual studio is crashed.
Can any one help me ?
View 1 Replies
View Related
Jun 23, 2015
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values
[Code] .....
View 2 Replies
View Related
Sep 30, 2015
I work for an organization that repairs serialized devices. Each time a device is repaired it's serial number is recorded in a database table along with the date it was repaired along with other information about the device. There are multiple cases where a unit has been repaired more than once.
I am trying to write a query that will return the serial only once and that record will be the record of the latest repair date. To sum it up,
Return a list of serials where if a serial exists more than once in the table, return only the instance of the serial record(s) with the max(created_dt). The end result will be a list of distinct serial numbers.
Here is my Query. The problem I believe is in my sub-query but I am not sure how to structure it.
SELECT
S.Id
, RMA
, PinSerial
, L4Serial
, L4Model
[Code] ....
View 3 Replies
View Related
Jun 16, 2015
I have an SSIS Package which Retreives Data using a  SQL Query like belowÂ
select  a.* from dbo.test a (nolock)
JOIN dbo.test1 b (nolock)
ON a.DetailsId = b.DetailsId
JOIN dbo.test c (nolock) on c.DetailsLineId = b.DetailsLineId
where convert(date,c.CpPlacedDate)>?
and convert(date,c.CpPlacedDate) Â < convert(date,GETDATE())Â
and c.PartnerCode in ('akakak07')
The CpPlacedDate DataType is Datetime. After the Successfull Execution of SSIS Package the final output results in destination is lesser than source. Â
The Maximum of cpplaceddate in the Destination for a particular date is '2015-06-13 23:46:08.923'
The Maximum of cpplaceddate in the Source for a particular date is '2015-06-13 23:59:14.873'Â
I am missing 16 records in between this time Gap.
View 3 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Jan 10, 2005
i'm currently a student in computer information sciences, and i wanted to give my studying some more focus thus choosing dba. now i'd like to dba but be as broad as possible when i first start out my career. i know some programming languages and recently finished a C# book for doing .net development and a ASP.net book for scripting. but where should i go from here? should i just start studying ms sql server? what about a microsoft certification? i'm very curious to know where the next step in my studying should be, so anyone who was in my shoes, i'd love to hear any information you have to offer. TIA
View 14 Replies
View Related
Oct 1, 2015
I have 2 tables:
create table #InsuredLoc (
PolGK int,
InsuredLocGK varchar(20),
InsuredLocEffDt datetime,
InsuredLocType varchar(20),
InsuredLocStatus varchar(20),
InsuredLocAddress varchar(50),
PolEdrGk varchar(20),
[Code] ...
I will need to join the #InsuredLoc table to the #PolicyEndorsement table using PolGk and PolEdrGk and get the min(BkgDt) and min(PolEdrRowUpdateDt) for the distinct list of InsuredLocType, InsuredLocStatus, and InsuredLocAddress fields from the #InsuredLoc table above.
 I will also need the min(InsuredLocEffDt) and the max(InsuredLocUpdateDt) from the #InsuredLoc table for those records. So after the first run, i should get the following:
I tried to use CTE's with ranking, but some records are dropping off and I'm not sure why. Â
View 6 Replies
View Related
Jul 23, 2005
Hi,I'm finishing up a beginning SQL class where we learned on an Oracledatabase and the transition to working on SQL Server is easy. The next moreadvanced course will be in PL/SQL, but I know I will be working on SQLServer in the workplace, so my question is if I should take this course.Will I benefit from the basic philosophies that will be covered, or will itjust make a transition for me more difficult? Will it be partly a waste oftime and money and I'd be better served getting a book and self teachingmyself? I know that in a greater sense learning something isn't necessarilya waste, but I mean from the perspective of my goal of being able to use SqlServer, will this course be useful?thanks.
View 10 Replies
View Related
Oct 18, 2007
I am starting a class on SQL Server, and my school wants me to builddb's either on campus or remotely at my home connecting to their SQLServer Enterprise.Will I be okay to use Developer Edition at home to connect with anEnterprise-licensed SQL Server on campus, to build databases,sprocs, .net apps that connect to the database stored on my school'sserver?I would rather avoid the Express edition.thanks
View 1 Replies
View Related
Sep 21, 2015
I am trying to JOIN table on datetime column truncated to full minutes with other table, where I store time intervals (in order to have all minutes in result even when there was no event in main data table).
SELECT
dateadd(minute, datediff(minute, 0, StartTime), 0) as StartTimeMinute
,ApplicationName
,COUNT(*) as Requests
FROM dbo.Profiler as p INNER JOIN dbo.MinuteIntervals as i ON i.TIMEVALUE = p.StartTimeMinute
WHERE EventClass IN (10,12)
GROUP BY dateadd(minute, datediff(minute, 0, StartTime), 0), ApplicationName
ORDER BY StartTimeMinute
How to join on p.StartTimeMinute? I do understand why this doesn't work, as p.StartTimeMinute is calculated in my query, so JOIN cannot find it in original p table.
View 5 Replies
View Related
Aug 24, 2015
SELECT A.EmpId,A.IncidentDate
FROM EmployeePoints1 as A
WHERE IncidentDate=
(SELECT MAX(IncidentDate)
FROM EmployeePoints1
WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A')
LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId
I am trying to left join another table but I got
Incorrect syntax near the keyword 'LEFT'.
View 9 Replies
View Related
Jun 8, 2015
I am doing some audit and i  have below query, how can i get rid of duplicates from the below query any T SQL to get rid of duplicates...
I am using SP_Who2 and sql server  Audit for auditing all data happening on sql server databases  and dumping them to tables  Audit_DBAudit  abd Audit_sp_who2  and from then i am trying to get data which is not repeating/duplicate ...
Â
SELECT
A.ProgramName
,a.HostName,[Server_principal_name],[Server_instance_name],[Database_name],[Object_name],F.Statement
FROM Audit_DBAudit as FÂ
 Join [Audit_sp_who2] AS a
on LTRIM(RTRIM(F.server_principal_name))=LTRIM(RTRIM(A.Login))
View 11 Replies
View Related
Apr 23, 2015
i have two tables Table one have 2 columns id and value
    Â
id value
1 Dell
2 Hp
B2 Hp-mini
B3 Hp-lapTop
3 Acer
the second table have 3 clomuns id,name,idTable
1 TeaBou B
2 Mark B
3 Jack 1
4 Piere 2
5 Jean 2
6 Mark 3
i tried this query
select*from table1 t1 ,table2 t2
where t1.id=t2.idTable
i had some data but also i need to include the person who have the B product.
View 6 Replies
View Related
Jul 14, 2015
I have a table like this :
reg.no |Â Â description |Â start_date |Â end_date |Â Â load_date
I want to join this table with itself on reg. no. But not all the rows in table must be joined.
But for example, rows with load_date 01-07-2015 to be joined with rows with load_date 02-07-2015. And the rest of the rows should not used in join (for example, rows having other load_dates)
Is this possible ? and how?
View 4 Replies
View Related
Sep 10, 2015
I have the following 2 tables:
Location:
Policy:
1 Location can be attached to many policies.
I need to create a query to get 1 row per location and get the minimum PolicyBookingDt and RowUpdateDt from the policy table. All the attributes from the Location table should also be from the Policy that has the minimum PolicyBookingDt.
 So from the above example, i need to get the following:
Getting a little confused on how to create the syntax for this.
View 4 Replies
View Related
Jan 18, 2008
I'm a kid and I'd like to know what kind of work a database administrator does. It would be great to learn from a real database administrator how you spend your typical day at the office. What are some of your main duties? Thanks so much in advance.
View 1 Replies
View Related
