How to form a single SQL statement in a datalist for a challenging and convoluted problem

Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem. I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message.

FROM | SUBJECT
| User Photo | |
| | Subject |
| User Name | |

Here is the list of the relevant 4 tables of my database and the relevant fields....

aspnet_Users table
UserId (used to link to Member table)
UserName

Member table
memberId (int - primary key)
UserId (guid - used to link to aspnet_Users table)
primaryPhotoId (int - used to link to Photo table)

Photo table
photoId (int - primary key)
photoUrl (string - path to file on local drive)

Message table
messageId (int - primary key)
fromMember (int - connects with memberId from Member table)
toMember (int - connects with memberId from Member table)
subject (varchar(max))

So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far....

SELECT aspnet_Users.UserName, Message.subject
FROM aspnet_Users, Member, Message
WHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember

Note that I'm grabbing the logged in user info from Profile.UserName. So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.

And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained.... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol.

This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.

S2kDriver:

SELECT aspnet_Users.UserName, Message.subject
FROM aspnet_Users, Member, Message
WHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember

- Close...

You could simply add a subselect:

SELECT
aspnet_Users.UserName,
Message.subject,
(SELECT photoUrl FROM Photo WHERE photoID = Member.PrimaryPhotoID) AS MemberPhotoURL
FROM
aspnet_Users, Member, Message
WHERE
aspnet_Users.UserName = Profile.UserName
AND aspnet_Users.UserID = Member.UserID
AND Member.memberId = Message.toMember

You should read up on JOINS:http://www.singingeels.com/Articles/Understanding_SQL_SELECT_The_Data_You_Want.aspx

There's a few good SQL articles there... I think they're good because I wrote them.

|||

Thanks for your reply, Tim. And thanks for the article btw. I'm not familiar with JOINS and I'll try to get up to speed on them. My mind is mush right now from thinking too hard hehe.

However, I must apologize for making a mistake on my original post. I've edited and corrected it (not the SQL statement, but the explanations right after it). That SQL statement I have, only gets data based on the logged in user (aka the Message.toMember). The aspnet_Users.UserName I have in the SELECT statement returns the username of the logged in user (aka RECEIVER), NOT the SENDER that I had hoped for to use for the FROM column of my datalist. I somehow need to have the Message.toMember somewhere in that SQL statement which does the rest of what I hope for as written in my original post.

To recap in summary, I need to get the list of all messages sent to the logged in user (aka where Message.toMember = the logged in user). However, through all the smoke and clouds, I somehow need to get the senders' data as well (their username and photo, not the logged in user's, but the senders'). Seems like anything I try to do to add in that in extra functionality, I affect the original results. This seems like a "2-tier" or "2-pronged" search that needs to occur at the same time (maybe I'm not making sense and may seem like I'm talking out of my ass lol) and whatever is implemented, has to sync them together to arrive at the expected results.

Thanks in advance.

|||

Well, just to let you know... you've been doing a "JOIN" (of sorts) in your query...

Select * from table1, table2 <-- that's a "cross join"... that means that if there are 1000 records in table 1, and 1000 records in table 2... you'll get 1,000,000 records returned! (a combination of every row in table one and every row in table two multipled together).

Then if you add a "WHERE" clause to filter them out... that's basically like doing an INNER JOIN with an "ON" clause (actually, it's 100% the same):

Example: SELECT * FROM table1, table2 WHERE table1.SomeID = table2.RefID

IS THE EXACT SAME THING AS: SELECT * FROM table1 INNER JOIN table2 ON table1.SomeID = table2.RefID

(it just helps you to visually group together your "filter" with your table relationships).

OK... now on to your query... (actually, I'm going to post this and then write another reply because I don't have your query in front of me :P)

|||

S2kDriver:

aspnet_Users table
UserId (used to link to Member table)
UserName

Member table
memberId (int - primary key)
UserId (guid - used to link to aspnet_Users table)
primaryPhotoId (int - used to link to Photo table)

Photo table
photoId (int - primary key)
photoUrl (string - path to file on local drive)

Message table
messageId (int - primary key)
fromMember (int - connects with memberId from Member table)
toMember (int - connects with memberId from Member table)
subject (varchar(max))

- OK, let's see what we can do here :)

SELECT
Message.messageID,
Message.subject,
aspnet_Users.UserName AS SenderUserName,
Photo.photoUrl AS SenderPrimaryPhotoUrl
FROM
Message INNER JOIN aspnet_Users
ON Message.fromMember = aspnet_Users.UserId
INNER JOIN Member
ON aspnet_Users.UserId = Member.UserId
INNER JOIN Photo
ON Member.primaryPhotoId = Photo.photoId
WHERE
Message.toMember = @.DahUser

There... now, do you know how to add a paramter to you're query? (I called it "DahUser" to be silly of course)

|||

Hey Tim, thanks for your response and in helping me out with this.

Does the AS just rename a field? If so, I don't think I'll be needing that as I don't show the actual field names in my datalist that is shown to the user.

Yes, haha I got the DahUser. The parameter that I need to add is the Profile.UserName variable that automatically gets the username of the logged in user. So in that case, I would think the WHERE clause would need to be modified to be

WHERE aspnet_Users.UserName = @.DahUser (where DahUser equals Profile.UserName) AND blah blah blah (as shown below)

Also, regarding your first INNER JOIN, maybe I'm just ignorant, but I don't think that would work, sinceaspnet_Users.UserId ties in with Member.UserId andMember.memberId ties in with Message.fromMember. Not only would the values would not make sense to compare, but the former compares guids whereas the latter compares ints. So aMessage.fromMember = aspnet_Users.UserId would not work I think.

Here's my crack at it....

SELECT
Photo.photoUrl, aspnet_Users.UserName, Message.subject
FROM Message
INNER JOIN Member
ON Message.fromMember = Member.memberId
INNER JOIN aspnet_Users
ON Member.UserId = aspnet_Users.UserId
(I'm confused at this point.... can I join a table that isn't directly related to the preceding table? e.g. can I do an inner join with the Photo table right after this point, without joining again with the Member table?)
INNER JOIN Photo
ON Member.primaryPhotoId = Photo.photoId
WHERE
aspnet_Users.UserName = @.DahUser AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember

Tell me what you think and if there are flaws in my reasoning. Also, I noticed that you put inMessage.messageId in the SELECT clause. Just curious but did you put it in for a reason? I guess I may need it to further my options in the datalist, but for now and the sake of this example, I may not need it.

Important - note that there are two instances ofaspnet_Users.UserName - one in the SELECT clause and one in the WHERE clause - and both of them are NOT referring to the same person. The one in the SELECT clause is trying to get the username of the sender and the one in the WHERE clause is trying to determine whose inbox this is (aka the receiver of the message via theProfile.UserName object). Likewise, theaspnet_Users.UserId, theMember.UserId, and theMember.memberId values that are used in both the FROM and WHERE clauses are referring to different people in the same manner. I hope this won't confuse the SQL engine and get results I'm not expecting hehe. Whew, that was brain racking lol.

Thanks again.

|||

Btw, this INNER JOIN stuff makes visualizing what you want easier, like you said. The WHERE clause pinpoints ONLY what you're really looking for and most of the work is done in the FROM clause. Nice! I've also heard of like 5 other JOIN variations. Do you think they apply to my situation?

**UPDATE - This still does not work. I used the SQL query which I modified from yours. Theaspnet_Users.UserName in the SELECT still returns the username of the logged in user, NOT the sender. Just out of curiosity, I performed a test. There are 5 records in the Message table with toMember = logged in user and fromMember representing other users. Let's say the logged in user's toMember is '1'. I ran my modified SQL query and got NO results. It should have returned 5. Next, I changed 2 of those records, so that the fromMember equals '1' (e.g. the logged in user sent messages to himself - yea that doesn't make sense in reality but for sake of example and testing I did this). NOW, what happened was, I got 2 records in the result set. So what seems like is happening is, the SQL query seems to think that all the instances of variables such asaspnet_Users.UserName,aspnet_Users.UserId,Member.UserId, andMember.memberId are referring to the same person (e.g. the logged in user) and CANNOT differentiate between the sender and the logged in user. We probably need to put further logic in this right? Do you think we need a more sophisticated query or perhaps another set of instances of Table names to differentiate the both kinds of users (sender and receiver)? I have no clue lol. Need to get some aspirin.

|||

Ok, this is what I have so far. I tried creating another instance of the aspnet_Users and Member tables to try and differentiate the receiver (logged in user) and the sender. The '1' suffix means the sender and the '2' suffix means the receiver (logged in user). I did not write it with JOINS since I wasn't sure how the declaration of table instances is written.

SELECT
Photo.photoUrl, a1.UserName, Message.subject
FROM
aspnet_Users a1, aspnet_Users a2, Member m1, Member m2, Message, Photo
WHERE
a2.UserName = @.UserName AND a2.UserId = m2.UserId AND m2.memberId = Message.toMember AND (this part takes care of the receiver aka the logged in user)
Message.fromMember = m1.memberId AND m1.primaryPhotoId = Photo.photoId AND m1.UserId = a1.UserId (this part takes care of the sender)

Unfortunately, I'd just tried this statement in my application and it still does not work. It still seems the SQL statement can't differentiate between both types of users. Hmmm, what to do? I don't even know if I'm on the right track or not.

|||

Your JOINs are incorrect. Use ANSI standard joins as Tim suggested. If you have 3 tables T1, T2, T3 your SQL should look like this:

SELECT T1.col1, T2.col2, T3.col5FROM T1INNERJOIN T2ON T1.somecolumn = T2.someothercolumnINNERJOIN T3ON T2.somenewcolumn = T3.somedamncolumnWHERE T1.col4 = @.DahUser

On the other hand, if you wrote the old way, it is very easy to miss a join and not spot it.|||

Thanks for writing, Dinakar. Which one of my attempts are you referring to? For Tim's JOIN which I modified slightly, I was following his convention. Or are you referring to the one right above your post? Sorry, I'm not sure I follow.

Btw, how do you quote somebody here? Sometimes, it's difficult to know who someone is replying to.

Thanks.

|||

S2kDriver:

Ok, this is what I have so far. I tried creating another instance of the aspnet_Users and Member tables to try and differentiate the receiver (logged in user) and the sender. The '1' suffix means the sender and the '2' suffix means the receiver (logged in user). I did not write it with JOINS since I wasn't sure how the declaration of table instances is written.

SELECT
Photo.photoUrl, a1.UserName, Message.subject
FROM
aspnet_Users a1, aspnet_Users a2, Member m1, Member m2, Message, Photo
WHERE
a2.UserName = @.UserName AND a2.UserId = m2.UserId AND m2.memberId = Message.toMember AND (this part takes care of the receiver aka the logged in user)
Message.fromMember = m1.memberId AND m1.primaryPhotoId = Photo.photoId AND m1.UserId = a1.UserId (this part takes care of the sender)

Unfortunately, I'd just tried this statement in my application and it still does not work. It still seems the SQL statement can't differentiate between both types of users. Hmmm, what to do? I don't even know if I'm on the right track or not.

I was referring to this post.

When you click on reply, you can see a "Quote" button in the post that you are replying to.

|||

Ahh I see, thanks - can't believe I missed that quote button. On other BB forums I go to, the quote button is usually shown on the main post page before I hit reply, so I was looking in the wrong place.

As for that SQL statement I wrote... are you referring to the fact that I'm using 2 instances of some tables? The reason I tried that method was the SQL statement needs to fetch data about 2 different users and I was trying to differentiate them throughout the statement. I've already tried the previous examples and was getting incorrect results (SQL engine thought I was referring to one person only and I was getting the logged in user's info for the sender information when I should have been getting the actual senders' information). So I got a bit desperate and tried this multiple instance approach. I looked at that statement again and it seems to tie things together, the linking table between both the receiver and sender being the Message table. What am I missing here?

|||

Post some sample data from each of the tables and expected output. It will make it easier for us to understand what the requirement is.

|||

Sure will do that right now.

aspnet_Users
UserId UserName
800f1c5a-d6e6-448d-94e7-8b2a22a8a108 S2kDriver
4f92dc4e-0ce7-4cdb-81d2-78d8a8850052 SillyJoanna

Member
memberId UserId primaryPhotoId
1 800f1c5a-d6e6-448d-94e7-8b2a22a8a108 5
2 4f92dc4e-0ce7-4cdb-81d2-78d8a8850052 12

Message
messageId fromMember toMember subject
1 2 1 subject test
2 2 1 hahaha
3 1 2 testing testing
4 1 2 i have a headache

Photo
photoId photoUrl
5 ~/photos/s2kdriver.jpg
12 ~/photos/sillyjoanna.jpg

Ok, let's say I log in with "S2kDriver" credentials. I go to my INBOX. I expect to see a datalist presenting me with a list of all messages that were sent to me (aka Message.toMember = 1). As far as the presentation goes for each item in the datalist, just to recap from my very first post of this thread....

___________________________________________
| FROM | SUBJECT |
| Photo.photoUrl | |
| | Message.subject |
| aspnet_Users.UserName | |

So, what I expect to see is....

====================================
~/photos/sillyjoanna.jpg |
| subject test
SillyJoanna |
====================================
~/photos/sillyjoanna.jpg |
| hahaha
SillyJoanna |
====================================

I should be seeing 2 items show up in my datalist. Note that this is S2kDriver's INBOX, so all the listed messages have to be where Message.toMember = 1. However, I think the tricky part is where we have to now go switch and grab the senders' information (aka the senders' main photo and the senders' user name).

Thanks in advance, I appreciate the help.

|||

See if this helps:

--Create temp tables and sample dataDeclare @.aspnet_Userstable (UserIduniqueidentifier, UserNamevarchar(50))insert into @.aspnet_Usersselect'800f1c5a-d6e6-448d-94e7-8b2a22a8a108' ,'S2kDriver'unionallselect'4f92dc4e-0ce7-4cdb-81d2-78d8a8850052' ,'SillyJoanna'--select * from @.aspnet_UsersDeclare @.Membertable (memberIdint, UserIduniqueidentifier, primaryPhotoIdint)insert into @.Memberselect 1 ,'800f1c5a-d6e6-448d-94e7-8b2a22a8a108' ,5unionallselect 2 ,'4f92dc4e-0ce7-4cdb-81d2-78d8a8850052' ,12--select * from @.MemberDeclare @.Messagetable (messageIdint, fromMemberint, toMemberint, subjectvarchar(100))insert into @.Messageselect 1 , 2 , 1 ,'subject test'unionallselect 2 , 2 , 1 ,'hahaha'unionallselect 3 , 1 , 2 ,'testing testing'unionallselect 4 , 1 , 2 ,'i have a headache'--select * from @.MessageDeclare @.Phototable (photoIdint, photoUrlvarchar(100))insert into @.Photoselect 5 ,'~/photos/s2kdriver.jpg'unionallselect 12 ,'~/photos/sillyjoanna.jpg'--select * from @.Photo--Write the query to get the expected resultselect Au.UserName, P.photoUrl, Ms.subjectfrom @.Message MsJoin @.Member Mon Ms.fromMember = M.memberIdJoin @.aspnet_Users AUON AU.UserId = M.UserIdJoin @.Photo PON P.photoId = M.primaryPhotoIdWhere Ms.toMember = 1

How to for date is not null or nothing condition

How can i write a condition for like isdate(Fields!DueDate.value)

I tried isdate but i am getting an error message. what is the equivalent for isdate in sql server reporting services formula fields or calculated fields expression.

Thank you very much for the information.

*********************************************

=IIF(isdate(Fields!DueDate.Value), ................

********************************************

Reddy,

Try

=IIf(IsDate(Fields!DueDate.value),"True","false")

This should work for you.

Ham

|||

Sorry should have read it all.

=IIf(Fields!DueDate.value is nothing,"False",IsDate(Fields!DueDate.value),"True","false"))

Ham

|||

There was a program error, I left out the next IIF statement

IIf(Fields!DueDate.value is nothing,"False",IIf(IsDate(Fields!DueDate.value),"True","false"))

How to fire a trigger without changing table data

I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try:

update MyTable
set
Col1 = 'x'
where
1 = 2

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John Smith" <nobody@.nowhere.com> wrote in message
news:1143510748_10105@.sp6iad.superfeed.net...
I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
> Try:
> update MyTable
> set
> Col1 = 'x'
> where
> 1 = 2

Thanks, but it doesn't work.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||> Thanks, but it doesn't work.

The script Tom posted works for me: Please expand on what you mean by 'it
doesn't work'.

CREATE TABLE MyTable(Col1 int)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE AS
PRINT 'Trigger fired'
GO

UPDATE MyTable
SET Col1 = 'x'
WHERE 1 = 2
GO

DROP TABLE MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Smith" <nobody@.nowhere.com> wrote in message
news:1143512881_10135@.sp6iad.superfeed.net...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:SL0Wf.1028$m35.96044@.news20.bellglobal.com...
>> Try:
>>
>> update MyTable
>> set
>> Col1 = 'x'
>> where
>> 1 = 2
> Thanks, but it doesn't work.
>
> --== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
> News==--
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:_s1Wf.9738$tN3.2012@.newssvr27.news.prodigy.ne t...
>> Thanks, but it doesn't work.
> The script Tom posted works for me: Please expand on what you mean by 'it
> doesn't work'.

Thanks for the help. The problem was due to NULL values in some columns.

The trigger was firing but not changing data.

--== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||>>The trigger was firing but not changing data.

Thats what your question says

Madhivanan

How to find gaps in sequential key

Is it possible to write an SQL query to find gaps in a sequential key field?
Key Field
7
8
10
11
14
I would like the query to return the gaps
9
12
13
Or better yet, the range of the gaps
9,9
12,13
Any suggestions?Yes, use a numbers table.
http://www.aspfaq.com/2516
"D Babin" <DBabin@.discussions.microsoft.com> wrote in message
news:485C3250-0E8C-4217-9984-92D86B7C19DA@.microsoft.com...
> Is it possible to write an SQL query to find gaps in a sequential key
> field?
> Key Field
> 7
> 8
> 10
> 11
> 14
> I would like the query to return the gaps
> 9
> 12
> 13
> Or better yet, the range of the gaps
> 9,9
> 12,13
> Any suggestions?|||Here's one method:
SELECT T1.keycol+1, MIN(T2.keycol)-1
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.keycol < T2.keycol
GROUP BY T1.keycol
HAVING T1.keycol+1 < MIN(T2.keycol)
David Portas
SQL Server MVP
--|||Do:
SELECT t1.col + 1 AS "start",
MIN( t2.col ) - 1 AS "end"
FROM tbl t1
INNER JOIN tbl t2
ON t1.col < t2.col
GROUP BY t1.col
HAVING MIN( t2.col ) - t1.col > 1 ;
Anith|||Equivalently in SQL-92 syntax
select t1.field + 1 as "begin", t2.field - 1 as "end"
from testa t1
inner join testa t2 on t1.field < t2.field
and t2.field - t1.field > 1
and
not exists (select a.field from testa a
where a.field > t1.field and a.field < t2.field)
"D Babin" wrote:

> Is it possible to write an SQL query to find gaps in a sequential key fiel
d?
> Key Field
> 7
> 8
> 10
> 11
> 14
> I would like the query to return the gaps
> 9
> 12
> 13
> Or better yet, the range of the gaps
> 9,9
> 12,13
> Any suggestions?|||Hi D Babin,
create table YourTable(keycol int)
insert into YourTable values(7)
--insert into YourTable values(8 )
--insert into YourTable values(9 )
insert into YourTable values(11 )
insert into YourTable values(14 )
-- The following query give wrong results when 8 and 9 values are not
there
SELECT T1.keycol+1, MIN(T2.keycol)-1
FROM YourTable AS T1
JOIN YourTable AS T2
ON T1.keycol < T2.keycol
GROUP BY T1.keycol
HAVING T1.keycol+1 < MIN(T2.keycol)
-- If you are looking for a single column Then I go with Aaron
Bertrand's Solution
-- Here sequence is an auxiallry table having numbers from 1-99999
select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
from YourTable) S2
where S1.seq between S2.M1 and S2.M2
and S1.SEQ NOT IN (SELECT keycol from YourTable)
drop table yourtable
With warm regards
Jatinder Singh
D Babin wrote:
> Is it possible to write an SQL query to find gaps in a sequential key fiel
d?
> Key Field
> 7
> 8
> 10
> 11
> 14
> I would like the query to return the gaps
> 9
> 12
> 13
> Or better yet, the range of the gaps
> 9,9
> 12,13
> Any suggestions?|||Jatinder Singh, I read your previous post and i have some questions for you
about your post

> -- The following query give wrong results when 8 and 9 values are not
> there
> SELECT T1.keycol+1, MIN(T2.keycol)-1
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.keycol < T2.keycol
> GROUP BY T1.keycol
> HAVING T1.keycol+1 < MIN(T2.keycol)
>
I tested this query by David Portas and it works fine even when the 8 and 9
values are not there.

> -- Here sequence is an auxiallry table having numbers from 1-99999
> select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
> from YourTable) S2
> where S1.seq between S2.M1 and S2.M2
> and S1.SEQ NOT IN (SELECT keycol from YourTable)
Your query result set does not show that the beginning and end of the range
gaps.
Also, why are you creating an auxiliary sequence table from 1 to 99999? It
just uses more memory in SQL Server cache that could be used by some more
important table. Thank you.
"jsfromynr" wrote:

> Hi D Babin,
> create table YourTable(keycol int)
> insert into YourTable values(7)
> --insert into YourTable values(8 )
> --insert into YourTable values(9 )
> insert into YourTable values(11 )
> insert into YourTable values(14 )
> -- The following query give wrong results when 8 and 9 values are not
> there
> SELECT T1.keycol+1, MIN(T2.keycol)-1
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.keycol < T2.keycol
> GROUP BY T1.keycol
> HAVING T1.keycol+1 < MIN(T2.keycol)
> -- If you are looking for a single column Then I go with Aaron
> Bertrand's Solution
> -- Here sequence is an auxiallry table having numbers from 1-99999
> select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
> from YourTable) S2
> where S1.seq between S2.M1 and S2.M2
> and S1.SEQ NOT IN (SELECT keycol from YourTable)
> drop table yourtable
> With warm regards
> Jatinder Singh|||> Also, why are you creating an auxiliary sequence table from 1 to 99999? It
> just uses more memory in SQL Server cache that could be used by some more
> important table.
A numbers table is probably more useful, and less of a burden
performance-wise, than you seem to think.
http://www.aspfaq.com/2516|||Aaron Bertrand, Thank you for your reply. I am trying to identify what the
benefits of the numbers table technique are using Jatinder Singh's select
statement
select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
from YourTable) S2
where S1.seq between S2.M1 and S2.M2
and S1.SEQ NOT IN (SELECT keycol from YourTable)
if we use
CREATE TABLE SEQUENCE
(
SEQ INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
the Sql server execution plan show that a Clustered Index S is used for
"where S1.seq between S2.M1 and S2.M2".
if we use
CREATE TABLE SEQUENCE
(
SEQ INT IDENTITY(1,1)
)
the SQL Server Execution Plan shows that a table scan is used for
"where S1.seq between S2.M1 and S2.M2".
So, it appears that the primary clustered index on the number tables improve
s
the SQL Server execution plan. But how can I see the real benefits of the
numbered
table approach? Maybe, SQL Profiler can reveal them. Again, I don't have the
actual data set that you experimented with on your blog but I am curious how
many rows were in it. Could you please identifity the specific benefits of
the numbered table
approach in this type application(i.e. finding gaps)? Thank you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> A numbers table is probably more useful, and less of a burden
> performance-wise, than you seem to think.
> http://www.aspfaq.com/2516
>
>|||Jatinder Singh, I modified your query (using Aaron Bertrand's suggestion) to
select the beginning and end of the range gaps (in sequential order):
select number from numberstest S1,
(select t1.keycol M1, min (t2.keycol) M2
from testing t1, testing t2
where t1.keycol + 1 < t2.keycol
group by t1.keycol
) S2 -- S2 is an derived table which avoids having to use temporary table
where S1.number between S2.M1 and S2.M2
and NOT EXISTS (SELECT keycol from testing where keycol = S1.number)
Please tell me this if this runs faster than my original query from yesterda
y:
select t1.keycol + 1 as "begin", t2.keycol - 1 as "end"
from testing t1
inner join testing t2 on t1.keycol < t2.keycol
and t2.keycol - t1.keycol > 1
and
not exists (select a.keycol from testing a
where a.keycol > t1.keycol and a.keycol < t2.keycol)
Thank you.
"jsfromynr" wrote:

> Hi D Babin,
> create table YourTable(keycol int)
> insert into YourTable values(7)
> --insert into YourTable values(8 )
> --insert into YourTable values(9 )
> insert into YourTable values(11 )
> insert into YourTable values(14 )
> -- The following query give wrong results when 8 and 9 values are not
> there
> SELECT T1.keycol+1, MIN(T2.keycol)-1
> FROM YourTable AS T1
> JOIN YourTable AS T2
> ON T1.keycol < T2.keycol
> GROUP BY T1.keycol
> HAVING T1.keycol+1 < MIN(T2.keycol)
> -- If you are looking for a single column Then I go with Aaron
> Bertrand's Solution
> -- Here sequence is an auxiallry table having numbers from 1-99999
> select seq from sequence S1,(select min(keycol) M1,max (keycol) M2
> from YourTable) S2
> where S1.seq between S2.M1 and S2.M2
> and S1.SEQ NOT IN (SELECT keycol from YourTable)
> drop table yourtable
> With warm regards
> Jatinder Singh
> D Babin wrote:
>

How To Find a Mismatch between tables

What is the best way to write a query to find a mismatch between 2 tables?
For instance, suppose you had 2 tables, each in a different database, but yo
u wanted to make sure the addresses in each table were in sync.
Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
What is the syntax to find where a difference occurs between the 2 tables ?
I know how to write the query to join them and find difference if Address1 i
s different, where I am struggling is with Address1 or Address2 or Address3
or City or State or Zip is different.
I am thinking this has to be a subquery of some sort, but am totally drawing
a blank.
Any suggestions will be greatly appreciated.
jlsIs that going to be a fun query :) How about...
SELECT MIN(tname) as TNAME, ShipToNumber, Name, Address1, Address2, Address3
, City, State, Zip
FROM
(SELECT 'TABLE1' as tname, table1.ShipToNumber, table1.Name, table1.Address1
, table1.Address2, table1.Address3, table1.City, table1.State, table1.Zip FR
OM table1
UNION ALL
SELECT 'TABLE2' as tname, table2.ShipToNumber, table2.Name, table2.Address2,
table2.Address2, table2.Address3, table2.City, table2.State, table2.Zip FRO
M table2
)TMPTBL GROUP BY ShipToNumber, Name, Address1, Address2, Address3, City, Sta
te, Zip HAVING COUNT(*)=1 ORDER BY ShipToNumber
Hope this helps
--
Cheers,
JP (Just a programmer;)
----
A program is a device used to convert,
data into error messages
----
"JLS" <jlshoop@.hotmail.com> wrote in message news:e8wAsEZqFHA.3920@.TK2MSFTNG
P09.phx.gbl...
What is the best way to write a query to find a mismatch between 2 tables?
For instance, suppose you had 2 tables, each in a different database, but yo
u wanted to make sure the addresses in each table were in sync.
Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
What is the syntax to find where a difference occurs between the 2 tables ?
I know how to write the query to join them and find difference if Address1 i
s different, where I am struggling is with Address1 or Address2 or Address3
or City or State or Zip is different.
I am thinking this has to be a subquery of some sort, but am totally drawing
a blank.
Any suggestions will be greatly appreciated.
jls|||You may find the CHECKSUM function useful for this task.
select *
from (select *, checksum(*) as cst1 from t1) as d1
join (select *, checksum(*) as cst2 from t2) as d2
on d1.keycol = d2.keycol
and cst1 <> cst2
You will probably find most mismatches with this code. Just keep in mind tha
t it's not 100% guaranteed that you will get all of them.
The CHECKSUM function might produce the same values for different inputs.
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"JLS" <jlshoop@.hotmail.com> wrote in message news:e8wAsEZqFHA.3920@.TK2MSFTNG
P09.phx.gbl...
What is the best way to write a query to find a mismatch between 2 tables?
For instance, suppose you had 2 tables, each in a different database, but yo
u wanted to make sure the addresses in each table were in sync.
Table 1 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
Table 2 = Ship To Number (PK), Name, Address1, Address2, Address3, City, Sta
te, Zip
What is the syntax to find where a difference occurs between the 2 tables ?
I know how to write the query to join them and find difference if Address1 i
s different, where I am struggling is with Address1 or Address2 or Address3
or City or State or Zip is different.
I am thinking this has to be a subquery of some sort, but am totally drawing
a blank.
Any suggestions will be greatly appreciated.
jls

How to filter this?

I Have 2 tables

Tables

Cat. ID

Category 1

2

Cat. Id Brand ID

Brand 1 129007

2 129999

And i had try to write the MDX as follow

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

But result is displayed all the result, How should i filter to only display Cat.ID =1 ?

1 2 3 4

VOP PPU CDC FOP

129007 90.8 99.9 78.9 77.3

129999 3.3 2.2 1.1 2.2 >This Rows should be filtered..

Thanks In advance

Hi tsohtan,

just add a WHERE-clause like the following:

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1])

Michael.

|||

Hi Michael,

Thanks for you answer.

How about filter more than one condition?

is that like this?

where condition1 & condition2?

Because i not sure is my data problem, it come out different result it should generate.

Thanks again.

|||

Hi tsohtan,

it depends on wether you want to filter by the same dimension but several members or by a member of another dimension.

Case 1: Filtering by more than one dimension

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE {[Dim Category].[Dim Category].&[1], [Dim Category].[Dim Category].&[2]}

Case 2: Filtering by a member of another dimension

Code Snippet

[Dim Category].[Dim Category].&[1]

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1], [Dim Other Dimension].[Dim Other Dimension].&[42])

You can also combine this. You can read in BOL about the MDX basics in the MDX Reference chapter. The basic elements of MDX (member, tuple and set) are explained there.

Michael.

|||

Hi Michael,

I not sure what is the "Where" mean here, a bit confuse.

Because i set the WHERE (Dim Category].[Dim Category].&1) and without the "WHERE" condition.

The total counts is same.

Any idea?

Thanks again

|||

Hi tsohtan,

maybe your Category dimension is not properly joined with your measure group?

Michael.

|||

yes you are right. and i get the right result.

thank you very much michael

How to filter this?

I Have 2 tables

Tables

Cat. ID

Category 1

2

Cat. Id Brand ID

Brand 1 129007

2 129999

And i had try to write the MDX as follow

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

But result is displayed all the result, How should i filter to only display Cat.ID =1 ?

1 2 3 4

VOP PPU CDC FOP

129007 90.8 99.9 78.9 77.3

129999 3.3 2.2 1.1 2.2 >This Rows should be filtered..

Thanks In advance

Hi tsohtan,

just add a WHERE-clause like the following:

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1])

Michael.

|||

Hi Michael,

Thanks for you answer.

How about filter more than one condition?

is that like this?

where condition1 & condition2?

Because i not sure is my data problem, it come out different result it should generate.

Thanks again.

|||

Hi tsohtan,

it depends on wether you want to filter by the same dimension but several members or by a member of another dimension.

Case 1: Filtering by more than one dimension

Code Snippet

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE {[Dim Category].[Dim Category].&[1], [Dim Category].[Dim Category].&[2]}

Case 2: Filtering by a member of another dimension

Code Snippet

[Dim Category].[Dim Category].&[1]

select

{[Dim Time].[Dim Time].&[1]:[Dim Time].[Dim Time].&[3]} *

{

[Measures].[Volume Of Purchase],

[Measures].[Price Per Unit],

[Measures].[Customer Distinct Count],

[Measures].[Frequency Of Purchase],

[Measures].[Total Sales]} on 0,

non empty { [Dim Brand].[Dim Brand].[Dim Brand] } on 1

FROM [CS DW Cube]

WHERE ([Dim Category].[Dim Category].&[1], [Dim Other Dimension].[Dim Other Dimension].&[42])

You can also combine this. You can read in BOL about the MDX basics in the MDX Reference chapter. The basic elements of MDX (member, tuple and set) are explained there.

Michael.

|||

Hi Michael,

I not sure what is the "Where" mean here, a bit confuse.

Because i set the WHERE (Dim Category].[Dim Category].&1) and without the "WHERE" condition.

The total counts is same.

Any idea?

Thanks again

|||

Hi tsohtan,

maybe your Category dimension is not properly joined with your measure group?

Michael.

|||

yes you are right. and i get the right result.

thank you very much michael