Help! - 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!

Author

Topic

pikosan
Starting Member

5 Posts

Posted - 2010-12-09 : 18:19:20

Hi all, just started to study SQL, could anyone pls help me.
I got 3 tables: Animals (with animal_ID (PK), and animal_name), Adopters(adopter_ID (PK), first_name and last_name), and Records that shows animals and their adopters (record_ID, animal_ID (FK) and adopter_ID (FK). I need to make a query that would show animal name and its adopters, but using names, not just primary keys.
Thx!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 18:24:08

Show us what you have so far so that we can see you made an effort. We don't answer homework questions, but we'll certainly help if you show some effort.

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

Subscribe to my blog

pikosan
Starting Member

5 Posts

Posted - 2010-12-09 : 18:31:00

cheers!
ok i have created 3 tables:
1. Animals here is my code:
CREATE TABLE Animals (
animal_ID number(5) NOT NULL,
animal_name varchar2(100) NOT NULL,
PRIMARY KEY (animal_ID));
2. Adopters
CREATE TABLE Adopters (
adopter_ID number(6),
first_name varchar2(50),
last_name varchar2(50),
address varchar2(150),
post_code varchar2(10),
tel_num number(30),
PRIMARY KEY (adopter_ID));
3. Records
CREATE TABLE Records (
record_ID number(6) NOT NULL,
animal_ID number(5) NOT NULL,
adopter_ID number(6) NOT NULL,
general_date date NOT NULL,
comments varchar2(255) NOT NULL,
start_date date NOT NULL,
end_date date NOT NULL,
PRIMARY KEY (record_ID),
FOREIGN KEY(animal_ID) REFERENCES Animals(animal_ID),
FOREIGN KEY(adopter_ID) REFERENCES Adopters(adopter_ID));

i need to create a view (not sure) so each animal would be displayed with its adopter. could use Select animal_ID, adopter_ID from Records but that will give me just ids. i just can't think of how to get the names

maybe you could give me a hint pls! thx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 18:39:18

HINT: You'll need to join to get the names.

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

Subscribe to my blog

pikosan
Starting Member

5 Posts

Posted - 2010-12-09 : 18:55:52

i have tried that
SELECT Records.animal_ID, Records.adopter_ID
FROM Records
FULL JOIN Animals
ON Records.animal_ID=Animals.animal_ID

it gives me ids (as i say it to do)
so i am trying to join Animals.animal_name and Adopters.last_name, Adopters.first_name but i keep getting all kinds of errors and i think the problem is in the last part but i don't know how to say it. i am trying to say that animal_name must match animal_ID from Records table but this is wrong

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-09 : 19:09:06

quote:
Originally posted by pikosan

i have tried that
SELECT Records.animal_ID, Records.adopter_ID
FROM Records
FULL JOIN Animals
ON Records.animal_ID=Animals.animal_ID

it gives me ids (as i say it to do)
so i am trying to join Animals.animal_name and Adopters.last_name, Adopters.first_name but i keep getting all kinds of errors and i think the problem is in the last part but i don't know how to say it. i am trying to say that animal_name must match animal_ID from Records table but this is wrong

I don't see Adopters as a table there..but you are trying to get a column from it

SELECT Records.animal_ID,
Records.adopter_ID,
Animals.Animal_Name,
Adopters.last_name,
Adopters.first_name
FROM Records
JOIN Animals
ON Records.animal_ID=Animals.animal_ID
JOIN Adopters
ON Records.Adopter_ID = Adopters.Adopters_ID

Poor planning on your part does not constitute an emergency on my part.

pikosan
Starting Member

5 Posts

Posted - 2010-12-09 : 19:38:22

Thanks! it worked!! (didn't know you can do 2 joins at one query, but ehh.. study it 2nd month)
Thanks a lot!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-09 : 19:40:38

Stay away from a FULL JOIN until you understand what it does. Use INNER JOIN, which is just simply JOIN, for now. Then figure out OUTER JOINs.

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

Subscribe to my blog

pikosan
Starting Member

5 Posts

Posted - 2010-12-09 : 19:49:27

Thanks a lot for help and advise! Will study more! Sorry for troubles!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources