Transact SQL :: Query That Returns Material (items) That Are Used In Event On Certain Day - RIGHT JOIN

Nov 29, 2015

I have a query that returns material(items) that are used in an event on a certain day.

SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID

There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.

View 4 Replies


ADVERTISEMENT

Transact SQL :: Left Join Returns Too Many Rows Vertically

Jun 18, 2015

I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records  from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.

View 5 Replies View Related

Transact SQL :: Same Query Returns Different Result?

Jul 13, 2015

I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.

Select[Field1]
From
(
SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, *
From[dbo].[Table1]
) A
WhereID > 1
OrderBy [Field1]

Those highlighted in yellow colours are duplicate records, I need to remove one of them.

View 8 Replies View Related

Transact SQL :: Query Returns 0 For Calculation

Aug 6, 2015

This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00

Declare @goodtries as int, @badtries as int
select @goodtries = convert(decimal(18,4),count(userID))
from table1
WHERE logintype IN ('Valid', 'Success')
select @badtries = convert(decimal(18,4),count(userID))

[code].....

View 7 Replies View Related

Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition

Oct 1, 2015

I have a query that returns the data about test cases.  Each test case can have multiple bugs associated to it.  I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data

TestCaseID TestCaseDescription  BugID BugStatus
1                TestCase1                       1      Closed
2                TestCase1                       2      Open
3                TestCase2                      11     Closed
4                TestCase2                      12     Closed
5                TestCase2                      13     Closed

How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.

View 3 Replies View Related

Transact SQL :: Return Preset Data Values Based On User Material ID

Jul 22, 2015

I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:

User Material = Wood-MDF
Preset Material = MDF Panel

I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?

View 2 Replies View Related

Need Timed Event To Delete Items With NULL Value

May 12, 2007

I am generally a C programmer and have little experience with DB programming, so I apologize right from the start.

I have a table that allows a registration item with a verification item that will be NULL when the item is created. What I wish to do is delete these if the verification item is still NULL after a specified time (say 24 to 48 hours).

I would prefer to do this with some sort of trigger in one of two ways and any suggestions are much appreciated.
1. Have a stored procedure or function (not sure which is best to utilize) that runs every 2 days which checks the table for the NULL values and deletes any older than 48 hours.
2. Create a stored procedure or function when the registration is made that will check if the verification is still NULL for that particular record 24 hours later.
I would think the first would be the simplest, but am not certain.

Again, forgive my inexperience with DB programming and again I appreciate any advice given.

Thanks,
Steven Henley

View 4 Replies View Related

Right Join Returns Same Results As Left Join

Feb 5, 2015

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]

View 3 Replies View Related

Transact SQL :: Filter - Join Query Rows

Nov 14, 2015

Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).

SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty  as CastedQty, MI.CounterStartup 
FROM        MouldItem MI
JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty
FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid

[Code] ....

View 2 Replies View Related

Transact SQL :: Summarizing Data Returned By Join Query

Nov 20, 2015

I have a table (edw_sbx_bt.jjenkins_OVERAGE2) of 40,000 MA_NUM.  

I want to find occurrences of those specific MA_NUM in another table (edw_ar_fl.ARTBASE) with specific attributes (CONTRACT_NUM = '593' and TRANSACT_STATUS_CD = 'D').  

When I run the query below it returns 4 million rows.  How can I write a query that will return the 40,000 MA_NUM (in edw_sbx_bt.jjenkins_OVERAGE2) with the sum total of MA_PAID_AMT associated with each?

SELECT edw_ar_fl.ARTBASE.MA_NUM,
       edw_ar_fl.ARTBASE.MA_PAID_AMT
FROM   edw_ar_fl.ARTBASE
       JOIN edw_sbx_bt.jjenkins_OVERAGE2
         ON edw_ar_fl.ARTBASE.MA_NUM = edw_sbx_bt.jjenkins_OVERAGE2.MA_NUM
WHERE  edw_ar_fl.ARTBASE.CONTRACT_NUM = '593'
       AND edw_ar_fl.ARTBASE.TRANSACT_STATUS_CD = '

View 4 Replies View Related

Query Or Script To Get The Event Viewer Event Properties?

Nov 2, 2007



Hi all,


Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?

Scenario:

>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application

click on one event.We can get the log info which is the manual procudure.

But now i want to get the event properties through the Query analyzer...

Any help would be great?


Thanks,

View 4 Replies View Related

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

Aug 18, 2015

I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.

Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.

But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.

Please see my query below:-

SELECT     Week_Day.Week_Day_name, Subject.Subject_Code,  Daily_Timetable.Period_No
FROM         Week_Day LEFT JOIN
                      Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
                      Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No

View 4 Replies View Related

Unary Select On Items IF Then Else Outer Join ??

Apr 17, 2008

I have a table of products, and many products have the same name only they are different sizes. I want to only select a particular size like 20 oz, but if it does not exist in that size I want to select the next appropriate size (and which case if it does not exist i wish to select the next appropriate size and so on ..
Does any one know how I can accomplish this and would be willing to help me out by posting some code, some direction ??

View 15 Replies View Related

Join Only Returns The Read Rows :|

Nov 1, 2005

Hi all,

I am trying to build a association table (t2) to store a list of users
have viewed an item in my records table (t1). My goal is to send the
UserID parameter to the query and return to the user a read / not read
marker from the query so I can handle the read ones differently in my
.net code. The problem is that I cannot work out how to return anything
but the read data to the client. So far my stored proc looks like this

DECLARE @UserID AS Int -- FOR TESTING
SET @UserID = 219 -- FOR TESTING

SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID

FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID

WHERE t2.UserID = @UserID

GROUP BY t1.MemoID, t1.strTitle,t2.UserID

It works fine but only returns those records from t1 that are read. I
need to return the records with null values also! I may have built the
assoc table wrong and would really appreciate some pointers on what I
am doing wrong. (assoc table has rID, MemoID and UserID columns)

Please help!

Many thanks

View 2 Replies View Related

SQL Call INNER JOIN Returns Too Many Records

Jan 25, 2006

I have my SQL call:
SELECT     CallLog.CallID, Journal.HEATSeqFROM         CallLog INNER JOIN                      Journal ON CallLog.CallID = Journal.CallID
There are multiple enteries in the Journal table for every entry in the CallLog table, so I receive multiple records:
CallID           HEATSeq00000164     983290904 00000164     983291548 00000164     983295209 00000231     984818271 00000231     985194317 00000231     985280248
I only want to return the LAST record in the Journal table, so the output will be:
CallID           HEATSeq00000164     983295209 00000231     985280248
Can this be done directly in the SQL call?

View 7 Replies View Related

Join Returns Duplicate Rows

Oct 23, 2007

Hi,
I'm having a little trouble with the following code:

SELECT DISTINCT cd1.*, cd2.*
FROM Table1 cd1 LEFT JOIN Table2 cd2
ON cd1.RegNr=cd2.RegNr
WHERE cd1.RegNr = $RegNr

I want it to return the 2 rows that is present in the tables but it returns 4.

1262007-10-20 10:14:00
1262007-10-20 10:14:00
1262007-10-20 10:17:00
1262007-10-20 10:17:00

View 18 Replies View Related

INNER JOIN Between A CLR-TVF And A Table Returns No Rows

Feb 24, 2007

I have the following query:

select sq.*, p.numero, p.nombre
from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero

The @lot parameter is declared at the top ( declare @lot int; set @lot = 1; ). GetListOfSquaresForShippingLot is a CLR TVF coded in C#. The TVF queries a XML field in the database and returns nodes as rows, and this is completed with information from a table.

If I run a query with the TVF only, it returns data; but if I try to join the TVF with a table, it returns empty, even when I'm expecting matches. I thought the problem was the data from the TVF was been streamed and that's why it could not be joined with the data from the table.

I tried to solve that problem by creating a T-SQL multiline TVF that is supposed to generate a temporary table. This didn't fix the problem.

What can I do? Does anybody know if I can force the TVF to render its data somewhere so the JOIN works? I was thinking a rowset function could help, but I just can't figure out how.

PLEASE HELP!!!!

Let me know if you want the code for the CLR TVF. This is the code for the T-SQL TVF:

CREATE FUNCTION [dbo].[GetTabListOfSquaresForShippingLot]
(
@ShippingLot int
)
RETURNS
@result TABLE
(
Number int, Version int, Position smallint,
SubModel smallint, Quantity smallint,
SquareId nvarchar(5),
ParentSquareId nvarchar(5),
IsSash smallint,
IsGlazingBead smallint,
Width float,
Height float,
GlassNumber smallint,
GlassWidth float,
GlassHeight float
)
AS
BEGIN
INSERT INTO @result
SELECT *
FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)

RETURN
END

View 6 Replies View Related

Transact SQL :: Match Items Between Two Tables

Jul 6, 2015

I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.

Then I have a table usergroups with two columns (groupid, user) with example:

1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott

I want to find groupid 2 and 4 because they exactly match the content of @tbl.

View 8 Replies View Related

OUTER JOIN And NULL Items - ASP VB Vs. Management Studio 2005

Feb 28, 2008



Hello,
please, is there SO who can help me resolve my problem? I'm developing a simple ASP application.

I have 2 tables - T_Employees , P_Users
T_Employees = all employees of a company, P_Users = users of the system, P_Users is sub-set of T_Employees, PERNR is link

I need result of:
SELECT e.Name, u.Permitions FROM T_Employees AS e LEFT OUTER JOIN P_Users AS u ON e.PERNR = u.PERNR and (e.PERNR = 1) .


If an employee with PERNR = x is not a user (is not in P_Users) , I expect to recieve 1 row with u.Permitions=null

But what happens!
If I put this query to Management studio, I recieve 1 row (as I expect). Than I run exactly the same query in ASP page and it doesn't return any row (recordcount=0).

And what is even more strange - it worked. Suddenly I met this problem in all my pages and I can't find where the problem consist in. I always take SQL query from ASP VB into query analyzer and it works. But not on my pages.

It looks like the ASP works with different settings or as QA corrects my query before execution.

I'm totaly confused by this. Have you ever met ST like this?

Thanks for your advice
Petr
petr DOT chary@gmail DOT c o m

View 7 Replies View Related

Inner Join Returns Multiple Duplicated Rows

Dec 3, 2013

Here is my query which returns multiple rows

SELECT
R.name, R.age,R.DOB,
ISNULL(D.Doc1,'NA') AS doc1,
ISNULL(C.Doc2,'NA') AS doc2
FROM
REQ R
inner join RES S ON R.Request_Id=S.Request_Id
inner join RES1 D ON D.Response_Id=S.Response_Id
inner join REQ1 C ON C.Request_Id=R.Request_Id

select * from RES1 where Response_Id = 111 -- return 3
select * from REQ1 where Request_Id = 222 --- returns 2

So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...

View 5 Replies View Related

Transact SQL :: Syntax To Group Items In One Line?

Nov 20, 2015

I have records that I get in this format:

ID                       Customer               Type                 TypeNUm
100                      Tiger                    Item                   T100  
100                      Tiger                     Item                   T200
100                      Tiger                     Item                   T300
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                   WE
200                       Panera                  PO                   POBBB

The reftypes are not always the same, what I need is to get it in this form

ID                       Customer               Type              TypeNUm
100                      Tiger                     Item                   T100,T200, T300 
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                      WE
200                       Panera                  PO                     POBBB

View 6 Replies View Related

Transact SQL :: NVARCHAR SortOrder Breaks For More Than 9 Items?

Jul 22, 2015

I have below sample data table,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),

[Code] ....

Here I want,

1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)

2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)

Below Query works perfect, when count of child or grand child count less than 10, as SortOrder column here is

NVARCHAR,
;With cte As
(Select t.ItemId, t.ItemName, t.ItemDate, t.ParentItemName, t.ItemOrder, t.ReportId,
Cast(t.ItemOrder As nvarchar(max)) As SortOrder
From @TBL t
Where t.ParentItemName Is Null

[Code] ....

The output rows with ItemOrder's 10, 11, 12 (test grand child 10, test grand child 11, test grand child 12) should display after ItemOrder = 9 (test grand child 9). I know this is happening due to varchar sort order?

View 4 Replies View Related

Performance Related Doubt On The Following Items --&&> LEFT/INNER JOIN Vs IN And Cost Of CONVERT

May 28, 2008

Hi,

1.
Right now in my queries I am using lots of LEFT Joins and INNER JOINs... and I was suggested to look at 'IN'... But with IN I did face some performance issues previously and stopped using it... but I have got new doubts on which query will give me better performance...

A query using LEFTJoin or a query using IN/NOT-IN


2.
This question is about CONVERT...

I have a stored proc which is used for updating a table... and multiple columns [of the same table] and corresponding values are sent to the proc [only a subset of the columns might be sent for updates everytime and the columns to update is not fixed for each run of the SP]...

I have to construct a UPDATE String out of it using string concatenation to finally be able to use "sys.sp_executesql" on that update statement...

This results in me having to use CONVERT() lots of times... and one of the columns among them on which I am doing a CONVERT is of the type XML...

So the question is as follows...
a. Is it preferrable to construct a single UPDATE statement string and execute it using "sys.sp_executesql"
b. Or Is it preferrable to give multiple UPDATE statments... i.e. one update statement for each column [Depending on whether that column has to be updated for that run or not]


i.e. The question essentially is:

Does a single update query constructed using lots of CONVERTS [Basically on INT and XML types]
give more performance over using multiple UPDATE statments on the table
Or is it the other way round..

Thanks,
Pratap.

View 5 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

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

Table Join Statement Returns Repeat Rows

Jul 22, 2007

Hello All.

I am struggling with the below join block in my stored procedure.
I can't seem to get the duplicate row problem to go away. It seems that SQL is treating each new instance of an email address as reason to create a new row despite the UNIONs.
I understand that if I am using UNION, using DISTINCT is redundant and will not solve the duplicate row problem.

Primary Keys: none of the email address columns are primary keys. Each table has an incrementing ID column that serves
as the primary key.

I am guessing I am encountering this problem because of how
I have structured my Join statements? Is it possible to offer advice without a deeper understanding of my data model or
do you need more information?

Thanks for any tips.


Code:


select emailAddress from Users union
select user_name from PersonalPhotos union
select email_address from EditProfile union
select email_address from SavedSearches union
select distinct email_address from UserPrecedence union
select email_address from LastLogin) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress = tab6.email_address

View 8 Replies View Related

Join Returns More Than One Row, Post Code Regular Expressions

Mar 23, 2006

Hi,I trying to write a select statement that will return each of my salesmen a region code based on a table of post codes using wildcards... eg.MK1 1AA would be matched in the region code table to MK1%SELECT dn.DEALER_CODE, dn.NAME AS DNAME, rc.REGION_ID,rc.POST_CODE, dn.POSTAL_CODEFROM REGIONAL_CODES rc CROSS JOINDEALER_NAW dnWHERE (dn.POSTAL_CODE LIKE rc.POST_CODE)The above statement works BUT there are some post code areas such asour friends in Milton Keynes that are split into two regions... eg MK1is region id 2 and MK10 is region 3.So a dealer with post code MK10 1AA would be matched to both rowsreturning duplicatesPOST_CODE REGION_IDMK1% 2MK10% 3I think the answer would lie in a subquery which returns the ID of theregion with the longest length of the postcode match (e.g.len(POST_CODE) for the rc table... return only the MAX....any ideas????Any help muchos appreciated, and I apologies now for the naming of thedealers name as a reserve word... not me!Ct

View 2 Replies View Related

Left Join Returns Values Where I Was Was Expecting Nulls

Nov 16, 2006

I have a query which is returning a different result set when it is run against identical tables in 2 different environments.

The query is like:

Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID


The Create Statement for the view is like

Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'


In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')


In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')


Ideas as to what changed gratefully received.

FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.

View 6 Replies View Related

Event Class 80 - Missing Join Predicate

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

Event Class 80 - Missing Join Predicate

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

Transact SQL :: Get Duration Of Each Event 0 Which Has Other Events Before And After?

Jun 22, 2015

I have a table that holds log event records, that keep getting appended. I need to get the duration of the each event 0 which has other events before and after. How can I do this - to get event duration of event 0 and the cumulative.

ID Event   Date
1    1        2015-06-21 21:01:44.457
2    1        2015-06-21 21:01:44.457
3    0        2015-06-21 21:02:04.780
4    1        2015-06-21 21:02:32.600
5    0        2015-06-21 21:02:57.967
6    1        2015-06-21 21:03:30.513

View 7 Replies View Related

Transact SQL :: How To Update ItemOrder With Ascending Numbers Starts With 1 For Child Items

Jul 23, 2015

I have a below table,

DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),('Item2', NULL, 2, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)
SELECT * FROM @TBL

Here,
1. for all ReportId, child items's ItemOrder  = 0
2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0

I need to,
1. update all child items with ascending numbers starts with 1 against each parent and each report.
2. for each different parent or different report, order by should starts with 1 again.

View 2 Replies View Related

Transact SQL :: Get List Of Items Present In Order Based On Confidentiality Code Of Product

Sep 29, 2015

I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.

I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.

whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.

If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.

Table 1:Order

Order_Id Order_No Customer_Id

2401 1234567 23
2402 1246001 24
2403 1246002 25

Table 2 : OrderedItems

OrderItem_Id Order_Id Item_Id Sequence

1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1

Table 3: ItemMaster

Item_Id Item_Name confidentCode

1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8

Out put for 1st grid 

**Note :** Logged-in user have confidentiality code 6

Order No Customer
1234567 23
1246001 24

3rd order is not displayed in the grid

After user selects the 1st order in the grid then the items present in that 1st order should be displayed as 

1001     Rice

the second item not displayed because that having confidentiality code greater than user.

After user selects the 2nd order in the grid then the items present in that order should displays

1005 Badham
1007 Oil

I need the query to display the order details in 1st grid.

View 3 Replies View Related

SQL Server 2012 :: Capture Statement From Missing Join Predicate Event?

Jan 27, 2015

After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.

The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.

I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run

select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt
where spid=169

it gets me the SQL being run at that time not the one that causing the event.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved