sql query

hykc

hi,

In Trans table, it contain ItemID, TeacherID which reference to Item table and Teacher table

and i need to get the record in Trans table, and it has 1 row at the moment,

when i join these table, it returns more 6 rows(which is 6 copies of the record in Trans table).

select * from Teacher, Item, Trans, Subject where Teacher.TeacherID = 'admin' and Trans.TeacherID = Teacher.TeacherID and Item.ItemID = Trans.ItemID and Subject.SubjectID = 2 and Subject.SubjectID = Item.SubjectID order by checkinDate

what should I do to get only 1row(it only has 1 row in Trans table)
is it I have something wrong in my query?
thx

barand

I suspect it's the link to subjects causing the extra records.

hykc

how can i fix it?
thx

kevinsequeira

try this

select Trans.ItemID, Trans.TeacherID from Teacher, Item, Trans, Subject where Teacher.TeacherID = 'admin' and Trans.TeacherID = Teacher.TeacherID and Item.ItemID = Trans.ItemID and Subject.SubjectID = 2 and Subject.SubjectID = Item.SubjectID order by checkinDate

not sure if this would help though cause like barand says its the extra records i n Subject, but it just might....

reg
kevin

barand

It depends on exactly what you are trying to retrieve but if you have six record on the subject table whose subject_id matches the item record subject_id then you get six records.

If you remove references to subject table then youll get on row with item and teacher details.

hykc

but i need to get the subject detail from Subject table

barand

Question

how can i fix it?

Answer

It depends on exactly what you are trying to retrieve

With "SELECT *" you get evry field from all the tables which isn't too specific.

hykc

Originally posted by barand
Question

Answer

With "SELECT *" you get evry field from all the tables which isn't too specific.

More questions ...

Have you got 6 records in the subject table where subject_id = 2?

Should you have 6 records in the subject table where subject_id = 2?

i have 6 subjects in Subject table.

subject_id subject_name

1 subject_A
2 subject_B
3 subject_C
4 subject_D
5 subject_E
6 subject_F

barand

What records exist in the other tables that would match the keys in your one Trans record?

hykc

Trans table

trans_id
checkoutdate
checkindate
teacherid
itemid
barcode

Item table

itemid
subject_id
location_id
cat_id
description_text

Category table
----------=
catid
catName

Subject table

subjectid
subjectname

Teacher table

teacherid
name
password

hykc

i think i fixed it,

thank you very much!!!