Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with query

Author

Topic

Cat84
Starting Member

4 Posts

Posted - 2011-11-30 : 07:19:41

Hello everyone,
I have a question and i was hoping you could help me
I have 2 tables, User: UserId, UserName, and Images: ImageId, UserId. Each user can have multiple images (like in facebook), and there are users that don't have any image at all. I need to return those, but not by using NOT IN or EXISTS, but with some kind of join.
For whatever reason i can't resolve this..
Thanks in advance:)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 07:27:44

Why do you have to use a join?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sureshkk
Starting Member

21 Posts

Posted - 2011-11-30 : 07:35:54

DECLARE @Users TABLE
(
UserID INT,
UserName VARCHAR(100)
)

DECLARE @Images TABLE
(
ImageID INT,
UserID INT
)

INSERT INTO @Users
SELECT 1,'User1'
UNION ALL
SELECT 2,'User2'
UNION ALL
SELECT 3,'User3'
UNION ALL
SELECT 4,'USer4'

INSERT INTO @Images
SELECT 1,1
UNION ALL
SELECT 2,1
UNION ALL
SELECT 3,2
UNION ALL
SELECT 4,4

SELECT * FROM @Users

SELECT * FROM @Images

SELECT u.* FROM @Users u
LEFT JOIN @Images i
ON u.UserID=i.UserID
WHERE i.UserID IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-30 : 10:51:48

quote:
Originally posted by Cat84

Hello everyone,
I have a question and i was hoping you could help me
I have 2 tables, User: UserId, UserName, and Images: ImageId, UserId. Each user can have multiple images (like in facebook), and there are users that don't have any image at all. I need to return those, but not by using NOT IN or EXISTS, but with some kind of join.
For whatever reason i can't resolve this..
Thanks in advance:)

why is it that you dont want to use NOT IN or EXISTS?
is it a special instruction from your teacher? or is the concepts yet to be covered?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Cat84
Starting Member

4 Posts

Posted - 2011-11-30 : 11:29:31

I was asked this question by an interviewer over the phone, and immediately answered NOT IN, but he wanted other ways, and specifically join.
sureshkk, thank you so much! That's the solution.. and i told him left join, and he asked "and what else?.." and i just froze, i forgot the "is null" part..
Looks like i'm not going to get the interview but at least now i know:)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 11:57:36

You can also use EXCEPT which is closer to relational theory - that could have been what they were looking for.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-30 : 13:11:09

What About UNION?

SELECT User_Id FROM User
UNION ALL
SELECT User_Id FROM Images
GROUP BY User_Id
HAVING COUNT(*) = 1

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-30 : 13:11:44

quote:
Originally posted by nigelrivett

You can also use EXCEPT which is closer to relational theory - that could have been what they were looking for.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

But isn't that in reality a join under the covers?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Cat84
Starting Member

4 Posts

Posted - 2011-11-30 : 13:35:43

X002548, your query doesn't work. I didn't understand what are you counting? It returns all users
I guess there are a lot of ways to solve this, but he particularly asked for join

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 13:50:01

S.b.

select User_ID
from
(
SELECT User_Id FROM User
UNION ALL
SELECT User_Id FROM Images
) a
GROUP BY User_Id
HAVING COUNT(*) = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-30 : 13:51:09

quote:
Originally posted by X002548

quote:
Originally posted by nigelrivett

You can also use EXCEPT which is closer to relational theory - that could have been what they were looking for.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

But isn't that in reality a join under the covers?

It's a type of join and that is what is wanted.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-30 : 14:40:30

quote:
Originally posted by nigelrivett

S.b.

select User_ID
from
(
SELECT User_Id FROM User
UNION ALL
SELECT User_Id FROM Images
) a
GROUP BY User_Id
HAVING COUNT(*) = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

That's me just being sloppy...I just used this to tests a count of rows loaded to table against a single row loaded control file

I believe this might be the fastest method though

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources