Help me please with this prob - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Help me please with this prob

Author

Topic

eldan
Starting Member

2 Posts

Posted - 2008-11-04 : 16:34:00

I have this 2 tables (actually I have more but it's the same problem so it doesn't matter):

tblAnimes
ID --> KEY int extra auto_increment
name --> Text
pic --> Text
genere --> Text
info --> Text

tblEpisodes
ID --> KEY int extra auto_increment
idAnime --> int
name --> Text
download --> Text

of course there's a connection between them of 1 to many so the values in the field ID are unique and it can't hold a value more than once and the field idAnime can hold the same value as many times as necessary.

now what I want to do is a query that will give me the fields ID and name from tblAnimes buttttttt only where the value in ID exists on the other table in idAnime.

for an example if I have on tblAnimes
ID - 1
name - aaaa
...

ID - 2
name - bbb
...

and on tblEpisode
ID - 1
idAnime - 1
...

ID - 2
idAnime - 1
...

now I only have the value 1 on idAnime and I don't have the value 2
so the query will only show me

ID - 1 name - aaa

and will only show me that once although there's 2 records where idAnime = 1

the point is that it will show me the fields on tblAnime only if a value from tblAnime exists on tblEpisodes

so please if you know the way hellllppp

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-11-04 : 16:49:11

Use a subquery.

SELECT A.ID, A.name
FROM tblAnime A
WHERE A.ID IN (SELECT idAnime FROM tblEpisode)

---------------------------
EmeraldCityDomains.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-04 : 16:51:06

I prefer to write them with a join:

SELECT A.ID, A.name
FROM tblAnime A
INNER JOIN tblEpisode E
ON A.ID = E.idAnime

Or so that someone doesn't say here's another way:
SELECT A.ID, A.name
FROM tblAnime A
JOIN tblEpisode E
ON A.ID = E.idAnime

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-11-04 : 17:11:45

Tara, I prefer joins most of the time, too, but doesn't that get you multiple listings of the same A record if there are multiple E records? You could throw in a DISTINCT, but I like those even less than subqueries.

---------------------------
EmeraldCityDomains.com

eldan
Starting Member

2 Posts

Posted - 2008-11-05 : 13:54:45

Thank you both very much it works perfectly now.
I actually like subqueries :D but one way or another I decided to use IN because it's more simple for me anyway, (and I don't remember What JOIN does.. ;p lol)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-05 : 18:56:01

quote:
Originally posted by eldan

Thank you both very much it works perfectly now.
I actually like subqueries :D but one way or another I decided to use IN because it's more simple for me anyway, (and I don't remember What JOIN does.. ;p lol)

Then you should be reading about joins.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources