Another problem with 3 tables - 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

Transact-SQL (2000)

Another problem with 3 tables

Author

Topic

fametown
Starting Member

4 Posts

Posted - 2007-03-19 : 17:30:42

Hi. I'm Eusebio Barriga from Spain.

I have a problem with a query for my website...

I have 4 tables.

Celebs
- Id
- Name
....

Contries
- Id
- Name
....

CelebsJobs
- Id
- IdCeleb (can be multiple)
- IdJob

Jobs
- Id
- Name

I need, in one query, extract the data for 3 tables (celebs, countries, celebsjobs)

For example.

For CELEB ID number 1 (Alyssa Milano)

Celebs
- Id (1)
- Name (Alyssa Milano)
- Country (13)
....

Contries
- Id (13)
- Name (United States of America)
....

CelebsJobs
- Id (1)
- IdCeleb (1)
- IdJob (4)
---- multiple rows for the same celeb
- Id (2)
- IdCeleb (1)
- IdJob (6)

Jobs
- Id (4)
- Name (Actress)
--------
- Id (6)
- Name (Producer)

I need extract....

ALYSSA MILANO (UNITED STATE OF AMERICA), ACTRESS, PRODUCER... and the most important thing... i need to filter the celebs by JOB..

For example... i need the list of ACTRESS (Where CelebsJobs.IdJob = 4)

Any help?

(the result of my test is here: http://www.fametown.com/sp/personajes/index2.aspx)

¡¡¡ THANKS !!!

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-03-21 : 08:25:54

Using inner joins should work with a where clause on celebsjobs.idjob=4.
something like:
Select celeb.Name, countries.name, jobs.name
FROM Celeb C
Inner Join country co on c.country = co.id
Inner Join jobs j on c.id = j.id
Inner Join Celebjobs ce on c.id = ce.id
where ce.idjobs = 4

Raymond Laubert
MCDBA, MCITP:Administration, MCT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources