CODEWITHSUNDEEP
c#sql-server-2005
user287745
user287745

Reputation: 3171

How to make a SQL query like this?

Given 2 base tables, 1 table which stores the relation between them with a few extra attributes; taking a few extra attribute values as user input, based on that extracting relation 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 you gave the records which found are this person has this animal with him.

    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

Upvotes: 0

Views: 92

Answers (1)

Mark Byers
Mark Byers

Reputation: 838226

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

Upvotes: 1

Related Questions