sql query like this please help me to correctly form it thank you

Question

    select DISTINCT table0.person_name, table5.animal_name 
      from table1

 INNER JOIN table0, table5 
        on table1.person_id=table0.person_id
            and
        table1.animal_id=table5.animal_id

where table1.aa=input1 
       and table1.bb=input2 
       and table1.cc=input3 
       and table1.dd=input4

2 base tables, 1 table which stores the relation between them with a fex extra attributes

taking fex extra attributes value as user input, based on that extracting raletion from relation table.

this information has the id of the main values (person and animal) not the names

i want to display the names on screen

like

according to the input u gave the records which found are this person has this animal with him

help

thankyou

Answer 1

You have at least three errors.

• The WHERE clause should come after the JOIN .. ON clause, not before it.
• You cannot refer to columns in table5 because it doesn't appear in the FROM list.
• You shouldn't write ON xxx AND ON yyy. Just write ON xxx AND yyy.

Other points to consider:

• Are you sure that you meant FULL OUTER JOIN and not INNER JOIN?
• Why do you add the distinct? If a person owns two animals with the same name do you really want to return only one row?
• Where do the values input1, ..., input4 come from?
• I think table0 should be renamed to person, table5 to animal, and table1 to person_animal to make it easier to understand the purpose of each table.

My best guess as to what you meant is this:

SELECT table0.person_name, table5.animal_name 
FROM table1 
JOIN table0 ON table1.person_id = table0.person_id
JOIN table5 ON table1.animal_id = table5.animal_id
WHERE table1.aa = input1 
  AND table1.bb = input2 
  AND table1.cc = input3 
  AND table1.dd = input4