Strange Result From A Simple JOIN
Sep 18, 2007
I am currently studying Transact SQL and playing around with queries from a sample database. Recently I created the following query.
USE MemtrackSQL
SELECT m1.MemberID, m1.Surname, m1.FirstName, m1.DateOfBirth
FROM tblMember m1 JOIN tblMember m2
ON m1.FirstName = m2.FirstName
WHERE m1.MemberID <> m2.MemberID
This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
2 Scharenguivil Rodney 1958-06-24 00:00:00.000
5 O'Grady Patrick 1975-09-23 00:00:00.000
7 Greenfield Lynne 1955-07-26 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
8 Harvy Simon 1965-08-27 00:00:00.000
Any help in explaining where I have gone wrong here would be greatly appreciated.
Cheers
View 3 Replies
ADVERTISEMENT
Mar 2, 2008
help strange result whan i do this
Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList
i try to get next unit value to next month
why i get this -1
on date
01/01/2008
1
-1
unit_date unit new_unit
01/02/2008
2
1
01/02/2008
1
4
01/01/2008
1
-1
01/02/2008
1
4
21/01/2008
1
-1
21/01/2008
1
-1
01/02/2008
1
4
TNX
View 3 Replies
View Related
Oct 29, 2006
Hi Folks!I have a strange thing happening; I have a field that Counts the number ofrecords and another field that shows the number of clients(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression"Count(RecordID)/NoOfClients" the expected result should be 1.5. Insteadthe result I get is 1. Any ideas?Thanks!Rick
View 4 Replies
View Related
Mar 7, 2007
I am having in writing a simple select command........here's My ProblemI am using the ASP SQLDataSources in VS2005.... and my need is that i need to show Products details in a Gridview...... Actually through QueryString a StoreID is being fetched..... and the Products under those StoreID are shown..... if there is nothing in query string then it should show all the results... ........ It means that my querystring should be something like thisselect * from tblProducts where StoreID = <xyz>and <xyz> should be some thing that could show all the rows in that table ........ i mean that it should show all the possible results that can be shown through...select * from tblProducts
View 6 Replies
View Related
Apr 11, 2005
I have a SQL Server 200 database called sampleDB in my local server that uses windows authentication, and all objects are owned by 'dbo' user. I created a creation script using the Eneterprise Manager 2000.
Then I ran the script against a database on my hosting service provider's server, that uses sql server login and not windows authentication, and I found some stored procedures were created with 'dbo' as owner and some with 'sun21170' as owner. 'sun21170' is the user for the database of my hosting service provider's server. Why should not all stored procedures be owned by dbo, as they were in original database on my local server?
View 7 Replies
View Related
May 17, 2006
Hello all, I have the following Stored Procedure that has been working perfectly for the last year:
CODE
====================================================
DELETE FROM tblReportMainMembers
INSERT INTO tblReportMainMembers (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM tblCart INNER JOIN tblMemberships ON tblCart.membership = tblMemberships.idWHERE (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444'
UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname, --tblReportMainMembers.userid = tblRecords.id, tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city, tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip, tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone, tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid
UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id
UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account
SELECT RTRIM(emplid) AS EMPLID, RTRIM(userid) AS USERID, RTRIM(fname) AS FNAME, RTRIM(lname) AS LNAME, RTRIM(membership) AS MEMBERSHIP, CAST(price AS varchar(12)) AS PRICE, RTRIM(approvecode) AS APPROVECODE, CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE, CONVERT(varchar(20), wpecend, 101) AS WPECEND, RTRIM(address) AS ADDRESS, RTRIM(city) AS CITY, RTRIM(state) AS STATE, RTRIM(zip) AS ZIP, RTRIM(homephone) AS HOMEPHONE, RTRIM(officephone) AS OFFICEPHONE, RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname
As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users.
But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again.
Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it.
Any ideas? Thanks in advance.Richard M.
View 1 Replies
View Related
Mar 7, 2007
Hello once again !
This is a very strange result I had noticed while using the IsMissing property.
I had the following Expression.
=IIF(Fields!CertainField.IsMissing = True, "N/A", Sum(Fields!.CertainField.Value))
This should just put the text "N/A" into the textfield if the specific field is missing from the dataset in the report.
I had put this in the following TextFields:-
The group's detail and the table Footer.
The result that I had gotten show an empty textfield in the group's detail but showed "N/A" in the page footer. So I know that the expression I had used is correct since it showed up in the table footer. What baffles me is that it didn't give me a "#Error" in the textfield, but an empty string (which essentially is correct, since this particular field in the dataset is missing to begin with).
The question I am asking is, does this have anything to do with the scope or is it something else, and how do I resolve this problem ?
Sincerely,
Bernard Ong
View 7 Replies
View Related
Feb 24, 2000
Hi
I have two database called test1 and test2 both are of same size(both device and log). I run the following statements to take the backup of both the databases using the tape device 'tapedevice1'. dump database test1 to 'tapedevice1'
dump database test2 to 'tapedevice1'
After this I run the load statement
load database test2 from 'tapedevice1'.
It just restore the database test2 with test1 data which is added first to the tape with a warning.
Is there any restriction that in a single tape backup of more than one database is not possible.
Your valuable suggestion appreciated
Jiji
View 1 Replies
View Related
Dec 23, 2007
this is my problem in stored procedure
1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE
2) I need to see only the employee than in the table v_un
3) i see all the employee
4) problem in this line ( [new date] = @mydate2,[new_shift2] = )
i see 2 rows of result from fields [new date] + ,[new_shift]
how to see only 1 field for each field
new date new_shift new date empid name
---------------------------------------------------------------
2007-12-01 99 2007-12-02 99 2568947 aaa
2007-12-01 99 2007-12-02 99 2845209 bbbb
2007-12-01 99 2007-12-02 99 4807756 ccc
2007-12-01 99 2007-12-02 99 9819590 ddd
2007-12-01 99 2007-12-02 99 10055648 eee
2007-12-01 99 2007-12-02 99 10815413 ffff
2007-12-01 99 2007-12-02 99 11070042 gggg
2007-12-01 99 2007-12-02 99 11162047 hhh
-------------------------------------------------------------------------------
i need to see only one for each field
new date new_shift id name
---------------------------------------------------------------
2007-12-01 99 2568947 aaa
2007-12-01 99 2845209 bbbb
2007-12-01 99 4807756 ccc
2007-12-01 99 9819590 ddd
2007-12-02 99 10055648 eee
2007-12-02 99 10815413 ffff
2007-12-02 99 11070042 gggg
2007-12-02 99 11162047 hhh
Code Block
DECLARE @yeara [varchar](4)
DECLARE @month1 [varchar](2)
DECLARE @day1 [varchar](2)
DECLARE @day2 [varchar](2)
DECLARE @day3 [varchar](2)
DECLARE @mydate1 [datetime]
DECLARE @mydate2 [datetime]
set @yeara ='2007'
SET @month1 ='12'
Set @day1 ='1'
Set @day2 ='2'
set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103)
set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103)
SELECT
SilokE
CASE
WHEN (empid IN (SELECT empid FROM SilokEWHERE (shift = 51 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 11 )))
THEN 2
else 99
END,
[new date] = @mydate2,[new_shift] =
CASE
WHEN (empid IN (SELECT empid FROM v_un
WHERE (shift = 11 )))
THEN 1
WHEN
(empid IN (SELECT empid FROM v_un WHERE (shift = 12 )))
THEN 2
else 99
END,
SilokE.empid, SilokE.Fname
FROM SilokE
View 1 Replies
View Related
Feb 11, 2004
declare @l decimal(38,2)
select @l = 24.35
if @l - convert(int,@l) = 0
select floor (@l)
else
select @l
select case
when @l - convert(int, @l) = 0 then floor (@l)
else @l
end
The if statement is giving correct result, but the case statement is not. I am fed up why it is so. Please advise.
View 14 Replies
View Related
Jul 20, 2005
It seens that a simple script:Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8)where name like 'ILLINOIS%'Will replace the SQL2000 domain name correctly in sysxlogins:ILLINOISJonesP becomes AA001JonesPBut for some strange reason via ILLINOISJonesP can still logon viaQueryAnalyzer although he is no longer in the sysxlogins tableanymore? SQL has been stop/started, server even rebooted, yet BOTHthe new and old logins seem to both allow QA login, any thought howthe old one is getting thru SQL security?thanks in advance for any help...
View 2 Replies
View Related
Nov 5, 2007
Hi,
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
thanks,
View 2 Replies
View Related
May 1, 2008
Ok maybe someone smarter than me (not difficult) can help me out :)
Two queries:
#1:
select
a.load_id,
b.attribute_name,
a.attribute_loc,
b.attribute_loc
from
PCI_Template_NR_Map b
left outer join
PCI_Master a
on a.attribute_name=b.attribute_name and
a.load_id in (select distinct top 53 load_id from PCI_Load')
#2:
select
a.load_id,
b.attribute_name,
a.attribute_loc,
b.attribute_loc
from
PCI_Template_NR_Map b
left outer join
PCI_Master a
on a.attribute_name=b.attribute_name and
a.load_id in (select distinct top 54 load_id from PCI_Load')
#1 Produces a correct left outer join, any values in PCI_Template_NR_Map that are not in PCI_Master show null. This works for any number of load_id values in the subselect up to 53.
#2 Is the exact same query, except I am no longer limiting it to 53, when i get to 54 (or if I take away the top altogether) it returns rows as if it were a normal inner join, instead of a left outer join (i.e. it only shows rows that are a match between PCI_Master and PCI_Template_NR_Map).
Can anyone explain to me what is happening here, and how to get around this issue? I need to be able to filter this for as many load_ids as I need (usually aobut 200). Thanks in advance,
Brad
View 20 Replies
View Related
Apr 23, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I got a problem with the records returned by inner join query. It is weird as in table a I with records r1, and in table b with records r2 (r1>r2), but the returned record number r3 is greater than r1? That is so strange. As the reasonable records returned should be less than r1?(inner join on attribute a). What probably is the cause of the problem? I am looking forward to hearing from you and thank you very much.
With best regards,
Yours sincerely,
View 5 Replies
View Related
Jun 29, 2007
I have encountered an annoying problem which causes the Merge Joins to lose records in the dataflow. The problem is caused by 2 unusual behavoirs.
1/ Sort of SSIS is not sorting the same as ORDER BY in SQL
example:
Code Snippet
CREATE TABLE [dbo].[table_2](
[test] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
With data as following:
Code Snippet1000
1-1.00.00.00
2000
When select this data with an order by like: select test from table_2 order by test
The result will be:
Code Snippet
test
1-1.00.00.00
1000
2000
If you sort the data by the SORT block of the SSIS the result will be:
Code Snippet
test
1000
1-1.00.00.00
2000This is annoying and dangerous, because it causes the next bug.
2/ Two datasources sorted by ORDER BY clause can give problems in a Merge Join.
If you have 2 data sources both correctly sorted by an order by in the query. When you join these 2 datasources with a Merge Join, you can lose some records in the dataflow.
This happens with larger datasets than examples above.
http://img340.imageshack.us/my.php?image=strangebehavior2tw1.png
When I join the datasources (see image ) inside SQL I will get a correct result of 15271 records.
Is this a bug which I should report? or is there a flaw in my logic?
View 6 Replies
View Related
Jul 17, 2007
While running query below on SQL Server 2005 (Build 3790: Service Pack 2):
SELECT DISTINCT pkg.PrimaryBarcode
FROM dbo.Package AS pkg (NOLOCK)
JOIN dbo.PackageCycle AS pc (NOLOCK)
ON pkg.PackageKey = pc.PackageKey
WHERE (pkg.BillCycleDateKey >= 20061201) OR
(pkg.BillStatusKey = 1)
I received a partial result set followed by
An error occurred while executing batch. Error message is: Couldn't replace text
I suspect this is a memory issue, but cannot find any reference to this particular msg on the Microsoft forums or the other 3rd party forums. PrimaryBarcode is a varchar(50)
I am not sure where to go from here. I would appreciate any ideas. Thanks in advance.
Cheers,
Mike Byrd
View 2 Replies
View Related
Feb 21, 2007
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
View 7 Replies
View Related
Aug 19, 2006
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
View 5 Replies
View Related
Sep 3, 2004
I don't know if it's Friday or what, but I can't for the life of me come up with an easy way to do this:
I have 3 tables I want to join:
Sale Table:
Sale_No Cus_No Sale_Qty
1 Joe01 250
Order Table:
Ord_No Sale_No Order_Qty ShipToCode
1 1 20 DestA
2 1 20 DestA
3 1 20 DestA
4 1 20 DestB
5 1 20 DestB
ShipTo Table:
Cus_No ShipToCode ShipToName
Joe01 DestA Philadelphia
Joe01 DestB Chicago
Bob01 DestA Boston
A sale for say 100 tons would have 5 orders (each for 20 tons) associated with it by Sale_No. Each of those orders can go to a different ShipTo destination. Since only the ShipTo Code is stored in the Orders table, I need to get the ShipToName. However, As demonstrated in the example table above, the key in the ShipTo table is both Cus_No AND ShipToCode.
I want a list of Sales and Orders, which is an inner join on Sale_No, piece of cake. However, I then need to use the ShipTo table to go from the ShipToCode to the ShipToName. Unfortunately, Cus_No is not in the Orders table, it is back in the Sales table (proper normalization is a pain sometimes).
What I came up with is this, but is this correct?:
FROM Sales INNER JOIN
Orders ON Sales.sale_no = Orders.sale_no INNER JOIN
ShipTo ON Orders.ShipToCode = ShipTo.ShipToCode AND
Sales.cus_no = ShipTo.cus_no
View 12 Replies
View Related
Jun 16, 2015
I have a right join query where i have a table where all employees attendance entry are registered and another table where pay master table where al employees details.
I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
means for selected date i need all the employees details suppose 10 employees who punched or not with empty values even he resigned.
I used right or left join it gives all dates value but when i select particular date i get only the punched details.
View 5 Replies
View Related
Sep 20, 2005
Hi Guys,
I have the following table called VMailMessages:
PHP Code:
MessageNum MailboxNum State
========================
1 100 1
2 101 1
3 101 1
4 102 0
Which is of messages in a mailbox system, the MessageNum is my primary key, MailboxNum indicates which mailbox it is for and State indicates whether it is 'New' (value = 1) or 'Saved' (value = 0).
What I want to do is write a query to obtain a list of mailboxes, along with how many New and how many Saved messages they have, producing a result table like this:
PHP Code:
MailboxNum NewCount SavedCount
===========================
100 1 0
101 2 0
102 0 1
My problem is I cannot seem to get my SQL right, so far I've got:
PHP Code:
SELECT NewQuery.MailboxNum, NewQuery.NewCount, SavedQuery.SavedCount
FROM (SELECT MailboxNum, COUNT(1) AS NewCount
FROM VMailMessages
WHERE (State = '1')
GROUP BY MailboxNum) NewQuery
FULL OUTER JOIN
(SELECT MailboxNum, COUNT(1) AS SavedCount
FROM VMailMessages
WHERE (State = '0')
GROUP BY MailboxNum) SavedQuery
ON NewQuery.MailboxNum = SavedQuery.MailboxNum
WHich works only if the mailbox has New messages as well as Saved messages. For mailboxes with only Saved messages, the count appears but, the MailboxNum is NULL. The opposite occurs if I change my SELECT clause to ask for SavedQuery.MailboxNum, but I really want both.
Can anyone help me?
Thanks
Richard
View 1 Replies
View Related
Mar 28, 2007
I feel like this is an easy question, but I can't describe it well enough to find the answer I need by searching. Anyway, in my spare time (I'm definitely not a SQL Query pro) I'm putting together a small app for our local Little League to help with scheduling.
I have 2 tables I need to join:
T1 = Schedule
has the following fields:
ID
hTeamID (ID of Home team)
vTeamID (ID of Vistor team)
Time
Date
T2 = Teams
ID
Name
Other info...
I want to be able to do a SELECT statement on the schedule table and JOIN the team names for both home and visitor. I've tried a bunch of different ways but keep getting errors. I can think of 100's of reasons to join the same table more than once, but I still don't know how to and can't find the answer online.
Thanks in advance.
View 2 Replies
View Related
Aug 6, 2014
I have listed two tables table 1 has some data. I have to update table 2 [reports] column from table 1 [reports] using self join..I should get as table 2 after updating
View 1 Replies
View Related
Feb 28, 2007
ive seen so many ways to do this, including some using cursors (strange i know)
but i have tableA and tableB i want to show fields from tableA which don't apear in tableB
what is the MOST efficient way to do this
View 2 Replies
View Related
Jul 23, 2005
Hello group!i have a table "group_code" wich relates the names ofnt-(domain)-groups to codes. now i want use the stored procedurexp_logininfo (asking for the group-membership of the current user) tojoin the result to "group_code". then i must use the new result (thecode) to join against other tables.i know now, that i cant join results of SPs against tables. may be thata UDF with a table result is the correct approach. but i have no ideahow to wrap the xp_logininfo in a UDF.a other way can be to do in a UDF the same thing like the xp_logininfo.then this UDF should deliver at least the nt-(domain)-groups in wichthe current-user is a member.Is there anybody who can give me the code for that?many thanx in advance.Karl
View 1 Replies
View Related
Jul 23, 2005
I'm a quantitative securities analyst working with Compustat data(company fiscal reports and pricing feeds).My coworker came across a problem that we fixed, but I'd like tounderstand 'why' it was happening and just don't get it yet.Here's the starting query (reduced to simple prefixes):----INITIAL-----declare @mthDate datetimeset @mthDate = (select max(datadate) from t)declare @wkDate datetimeset @wkDate = (select max(datadate) from z)Select...fromzleft join a on a.idA = z.idA and a.idB = z.idBand a.datadate = z.datadateleft join b on b.idA = z.idA and b.idB = z.idBand b.datadate = @mthDateleft join c on c.idA = z.idA and c.idB = z.idBand c.datadate = @mthDateleft join d on d.idA = z.idA and d.idB = z.idBand d.datadate = z.datadateleft join e on e.idA = z.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = z.idAleft join k on k.ticker = z.tickerleft join m on m.idA = z.idA and m.idB=z.idBwherez.datadate = @wkDate<..some other expression filters...>and k.ticker is null----END INITIAL-----------As you can see 'z' is the main table that things are linked to viaouter joins (our security master). Table 'k' has a list of securitiesthat we wish not to have results for.There are 77 entries in table k and 4933 in table z for that giventime. We'd expect 4856 to be in this, but no. it's 4400, and then thenext time you run it (no changes whatsover) it's 2312, and so on.Every time you execute you get a different record count.My thought/and fix was to move the (k.ticker) predicate out of thewhere clause and get a differenced set from z using NOT EXISTS:-----AMENDED---------------from(z where not exists(select * from k where k.ticker=y.ticker)) yleft join a on a.idA = y.idA and a.idB = y.idBand a.datadate = y.datadateleft join b on b.idA = y.idA and b.idB = y.idBand b.datadate = @mthDateleft join c on c.idA = y.idA and c.idB = y.idBand c.datadate = @mthDateleft join d on d.idA = y.idA and d.idB = y.idBand d.datadate = y.datadateleft join e on e.idA = y.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = y.idAleft join k on k.ticker = y.tickerleft join m on m.idA = y.idA and m.idB=y.idBwherey.datadate = @wkDate<..some other expression filters...>------------------------And this works. It's stable now.I'm hoping someone here can help me up the wisdom curve by explainingto me 'why' the recordset kept changing before.My guess is that the cost-based optimizer was resorting the outer joinsand handing back different sets as a result, but i want to understand,and thought i'd come to this group for help.I appreciate your time and look forward to replies.Greg McIntire
View 7 Replies
View Related
May 4, 2015
Required Output
Country Category
Internet Sales Amount
Internet Order Count
[code]....
I need to perform a SQL kind of Cross join to get the Total Count as a column along side as there are .net code and json structures defined that expects output in a certain format.
View 2 Replies
View Related
Sep 14, 2006
In a single table I have 2 columns. Date | Number2006/09/01 - 2352006/09/03 - 2452009/08/01 - 230 I want to write a query that will return the AVG number between two dates. I am assuming this will require a JOIN but I'm having problems implementing my solution. I think about it, it's probably not a join but a sub query...I was trying the following: SELECT Date, AVG(Number) as AVERAGE_NUMBER
FROM test.Table
WHERE ( Date>='09/01/2006' AND DATE<='09/04/2006' ) But I keep getting date is invalid in the select list because it is not contained in either an aggregate funtion or a group by clause. Thanks in advance for your help.
View 2 Replies
View Related
Jan 19, 1999
I've got a simple ( I think) question on views. I've got a view that has a table join in it.
With this view, we want to be able to perform updates, inserts, and deletes. At this time
we can do the updates and inserts, but not deletes. I've checked the permissions and
the users have SELECT, INSERT, UPDATE, and DELETE. Am I missing something or are
deletes just not possible in a view with a join?
CREATE VIEW update_bd_view
AS select
D.BD_ID, D.BD_DESC, T.BT_TYPE_TID, T.BT_TYPE_FID, T.BT_JOB_FID
FROM BILLING_DESC D JOIN BILLING_TIME T ON D.BD_ID=T.BT_ID
GO
thank you for your time!
Toni Eibner
View 1 Replies
View Related
Sep 4, 2006
Hi Everyone,
I have a simple question regarding outer join.
Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
View 3 Replies
View Related
May 31, 2007
Ok here is the situation. I have 2 tables.
Movies
MovieID Title Year
21 A Beautiful Mind 2002
22 Forrest Gump 1994
23 The English Patient 1999
Actors
ActorID MovieID Name
1 22 Tom Hanks
2 21 Russell Crowe
3 23 Ralph Fiennes
4 NULL Nachiket Mehta
Here is the SQL Query.
SELECT ActorID, Name, Title, Year FROM Actors LEFT OUTER JOIN Movies ON Actors.MovieID = Movies.MovieID
Now, I only want to show movies made in 1990's and display all 4 actors. If I put
WHERE Year < 2000
it won't show the fourth actor because he doesn't have any movies. I need to show all 4 actors here and NULL for movies if they don't have any.
Hope this makes sense. Thank you.
Nachiket
View 3 Replies
View Related
Apr 25, 2006
Hi,We are in the process of buying a new server to run mssql. Howeverbefore this as a tempory fix to using a msaccess backend i believethrough odbc i need to address the following issue:SELECT ai.entry_date as CallTime,ai.agent_login as AgentsLogin,ai.campaign as MarketingCampaign,ai.agent_input2 as ProductsSold,ai.first_name as Cust_FirstName,ai.last_name as Cust_LastName,ai.agent_input1 as Cust_PersonalNumber,ai.street_address as Cust_AddressStreet,ai.city as Cust_AddressCity,ai.state as Cust_AddressState,ai.zip as Cust_AddressZIP,rec.file_name as AgreementRecordingFileFROM agent_input ai, leads l, recordings recWHERE ai.whole_phone_number = l.whole_phone_number ANDl.call_status = 1110 ANDrec.whole_phone_number = l.whole_phone_number ANDrec.last_name = l.last_name ANDrec.agent = ai.agent_login ANDrec.campaign = l.campaign ANDlast_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#ORDER BY ai.agent_login, ai.entry_dateI want to make the recordings entry optional so the same results comeout whether it matches a recording or not. If it does i want it topopulate the AgreementRecordingFile column above, if not just put a ''as you would with '' as AgreementRecordFile.Does anyone know how you can do this, in a access based database systemusing SQL through i believe ODBC?ThanksDavid
View 1 Replies
View Related
Sep 11, 2007
Two tables:FruitfruitID, fruitNameBasketbuyerID, fruitID(ie. we can see which buyer has what fruit in their basket)I simply want to display all available fruit and whether or not it'sin a specific persons' basket.SELECT Fruit.fruitID, Fruit.fruitName, IsNull(buyerID, 0)FROM Fruit INNER JOIN Basket ON Fruit.fruitID = Basket.fruitIDWHERE Basket.buyerID = 12but this just gives me what's in buyer 12s' basket.What am I doing wrong? Am I a basket case...
View 2 Replies
View Related
