Join Returns Duplicate Rows

Oct 23, 2007

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


Unique Index Returns Duplicate Rows

Oct 25, 2004

We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique.

select top 100 s.*, x.col1
from Table_1 s
left outer join Table_2 x
on x.col1 = s.col1 and x.sys1 = 'SYSTEM0'

Unfortunately this query returns duplicate rows. And every time the result is different

But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups.

Any ideas?



View 1 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

SET @UserID = 219 -- FOR TESTING

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

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

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.


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
@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
FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)


View 6 Replies View Related

Inner Join Returns Multiple Duplicated Rows

Dec 3, 2013

Here is my query which returns multiple rows

ISNULL(D.Doc1,'NA') AS doc1,
ISNULL(C.Doc2,'NA') AS doc2
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

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.


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

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

Duplicate Result Rows From 2 Table Join

May 7, 2012

I am using SLQ Server 2008 R2. The database was designed by another company.

I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.

Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.

I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.

My SQL is:

select distinct (a.client_id), a.cli_last AS Last_Name,
a.cli_first AS First_Name, a.cli_middle AS Mid_Init, AS City, b.county AS County, b.state AS State
from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b
on a.client_id = b.client_id
inner join ECBH.dbo.tbl_client_insurance c
on a.client_id = c.client_id
inner join ECBH_TEST.dbo.tbl_GEF_County d
on b.county = d.COUNTY_NAME
where c.plan_id = 4
order by a.cli_last, a.cli_first

Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?

View 5 Replies View Related

How To Remove Duplicate Rows From Full Join Query

Jan 26, 2008

I have 4 tables (SqlServer2000/2005). In the select query, I have FULL JOINED all the four tables A,B,C,D as I want all the data. The result is as sorted by DDATE desc:- 
AID     BID      BNAME          DDATE                                   DAUTHOR
1          1          abcxyz              2008-01-20 23:42:21.610
1          1          abcxyz              2008-01-20 23:41:52.970
1          2          xyzabc              2008-01-21 00:17:14.360
1          2          xyzabc              2008-01-20 23:43:17.110        
1          2          xyzabc              2008-01-20 23:42:43.937
1          2          xyzabc              NULL                                      NULL
2          3          pqrlmn              NULL                                      NULL
2          4          cdefgh              NULL                                      NULL 
Now, I want unique rows from the above result set like :- 
AID     BID      BNAME          DDATE                                   DAUTHOR
1          1          abcxyz              2008-01-20 23:42:21.610
1          2          xyzabc              2008-01-21 00:17:14.360
2          3          pqrlmn              NULL                                      NULL
2          4          cdefgh              NULL                                      NULL 
I want to remove the duplicate rows and show only the unique rows but contains all the data from the first table A. I have to bind this result set to a nested GridView.

View 8 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

Case Stmt Returns Duplicate Result

Nov 28, 2007

Hi there,

The following is my table whereby i have joined projects table with issue table (this is 1 to many relationship).

I have the following query:
odf.mbb_sector sectorid,

SUM(case when odf.mbb_projecttype = 'lkp_val_appl' then 1 else 0 end) total_appl,
SUM(case when odf.mbb_projecttype = 'lkp_val_infrastructure' then 1 else 0 end) total_infra,
SUM(case when odf.mbb_projecttype = 'lkp_val_eval' then 1 else 0 end) total_eval,
SUM(case when odf.mbb_projecttype = 'lkp_val_subproject' then 1 else 0 end) total_subprj,
SUM(case when odf.mbb_projecttype = 'lkp_val_nonit' then 1 else 0 end) total_nonit,
SUM(case when odf.mbb_projecttype = 'lkp_val_adhocrptdataextract' or
odf.mbb_projecttype = 'lkp_val_productionproblem' or
odf.mbb_projecttype = 'lkp_val_maintwoprogchange' then 1 else 0 end) total_others,
COUNT(distinct prj.prid) total_prj



prj.prid =
AND srm.is_active =1
AND odf.mbb_projecttype not in ('lkp_val_budget','lkp_val_itpc')
AND odf.mbb_funcunit = 'lkp_val_operation'

which returns me the following result :

The problem is at the lkp_val_infosystem where it returns 3 instead of 1 in the total_infra column. How do I correct my case stmt to return the correct no of projects breakdown by different project type? Currently, only the total_prj which returns correct data.


View 3 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

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View 4 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:

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]
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

Sys.dm_os_performance_counters Returns Zero Rows

Jan 3, 2008

After changing the Service account for a SQL Server 2005 SP2 machine the DMV sys.dm_os_performance_counters
is returning zero rows. These values were availiable prior to change

The change in Service account was done via the Configuration manager and the Service account is also part of the Local Administrator group

Since this change the Performance Counters in Perfmon for SQLSEVERatabases and others are also not availiable

Any suggestions on where to start looking ?


View 4 Replies View Related

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 View Related

DataAdapter Always Returns 0 Or Empty Rows

May 7, 2007

Good day,
I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.
I thought the connection is not the problem since I got my columns right. Below is my code snippet.
const string COMMAND_TEXT = @"select distinct somefield as matchcode, count(somefield) "
+ "as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";
SqlDataAdapter adapter = new SqlDataAdapter(COMMAND_TEXT, connection);
DataTable dt = new DataTable(sometablename);


View 13 Replies View Related

Group By - Count Returns No Rows

Jun 12, 2007

Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2  FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.

View 4 Replies View Related

Select * From Sysperfinfo Returns 0 Rows.

Aug 15, 2003


I have DB monitoring jobs which use Sysperfinfo to monitor some of the counters. On One SQL 2K Server since few days the select on sysperfinfo returns 0.

Do you think I need to start any service from the OS side to enable this?

Your input is highly apppreciated.


View 2 Replies View Related

Select Distinct Returns Multiple Rows With Same Value

Apr 24, 2008

I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?

View 4 Replies View Related

Creating SQL Statement That Returns Rows ONLY Between A Certain Date?

Sep 8, 2004

I maintain a simple employment (job) tracking web application.

Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled.

There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to.

Here is a short example of what a few entries might look like:

Status Date Job number

Open10/6/2002 2:34:56 PMTEST2845
Hold10/11/2002 12:19:29 PMTEST2845
Closed10/29/2002 2:00:54 PMTEST2845
Pending10/25/2002 3:37:06 PMTEST2877

What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates.

Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs.

Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open.

If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months.

Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs.

The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month.

This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month.


View 2 Replies View Related

Xp_AvailableMedia Returns 0 Rows In SQL Server 2005

May 2, 2007

Without systemAdmin role, xp_AvailableMedia can still run (after granting execute permission) but just return 0 rows.

It seems we have to give the user SystemAdmin role, in order to get the list of devices in SQL 2005, while in previous versions of SQL Server, the user granted the execute permission for xp_AvailableMedia will be able to get the list of devices.

Is this be design? Is there any way the a NON-SA user can retrieve device list? If yes, what extra permissions do we have grant?

Thanks in advance for your help.


View 3 Replies View Related

How To Speed Up Stored Procedure That Returns 35,000 Plus Rows Of Data?

May 8, 2008

I'm pretty new to .NET and am looking for advice on how to speed up a simple stored procedure that returns 35,000 plus rows.   It's a super simple query that returns a client list.  It's just that there is soooooo many rows, it's super slow when the page loads. 

View 4 Replies View Related

T-SQL (SS2K8) :: Fetch Next From Cursor Returns Multiple Rows?

Aug 9, 2014

I don't understand using a dynamic cursor.

* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.

Now the Skript to reproduce the whole thing.

create database objects

-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;


Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.

View 8 Replies View Related

SQL Server 2012 :: Import XML Returns 0 Rows Affected Failing

Nov 29, 2013

I am trying to do a bulk import of data from XML into sQL.

My query returns no errors but no data gets imported.

Here is my XML

?xml version="1.0" encoding="utf-8"?>


View 2 Replies View Related

Joining Large Fact Table To A View That Returns 120 Rows

Jan 19, 2015

I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.

Inner Join Dimension.Age_Band ON
Group By

I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.

Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?

View 9 Replies View Related

Inner Join Duplicate Data

Jun 13, 2000

i have 2 tables not connected in any way
but both have orderid filed (same filed).
In one table this filed (and onther one) are keys,
the second table dose not hace a key at all.
The same order_id CAN repeat itself in each table.
When i try to join the tables (some rows just in one table and some in both):
Select, tab1.orderid, tab2.sku
from tab1 inner join tab2 on tab1.orderid=tab2.orderid
The result i get is duplicate.
each row is multiple.
What I'm doing wrong?

View 3 Replies View Related

Duplicate When Do Left Join

Jan 27, 2014

select a.[Cuno], [doc_type], [Doc_name]
from [assembly] a
left join [assembly_Doc] doc
ON = doc.assembly_id
left join [assembly_Recog] re
ON = re.assembly_id
where [activate] =1
and doc.doc_type = 1

I only have two records for doc_type =1 in the [assembly_Doc] table. Without joining the third table [assembly_Recog], the results is corrected, 2 records returned. I need to join with [assembly_Recog] table, but when it joined, I got the duplicate records. It listed twice. pls see the results below:

CusNoDocType DocName
CUS-022 1B1489-01 Rev 1
CUS-022 1B1489-01 Rev 1
CUS-022 1B149-02 Rev 2
CUS-022 1B149-02 Rev 2

View 2 Replies View Related

SQL Server 2008 :: Query That Returns Only Rows That Meet Specific Condition?

Oct 22, 2015

Here's what my table looks like;

UniqID | Code |

1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC

I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.

View 9 Replies View Related

Make Tablix Column Group Show When Filter Returns No Rows

May 20, 2008

I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.

However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.

In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.

Am I just missing a simple option?


View 1 Replies View Related

SQL 2012 :: Duplicate Records On A Join?

Apr 8, 2014


I had a problem before of not been able to find the rows with 0 values. I've now managed this although it's brought up duplicate rows due to the discounts been different on the same Mfr_part_number. I tried using the max function on the isnull (Exhibit_Discount.Discount, 0.00) AS Discount instead but to no success. i think i maybe something to do with PK keys not been used in the set-up of the database.

Use Sales_Builder


View 7 Replies View Related

SQL Server 2012 :: List Catalog Reports With No Execution History Returns 0 Rows

Feb 13, 2015

comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:

SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4

However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:

FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])

I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).

select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'

View 4 Replies View Related

Copyrights 2005-15, All rights reserved