-1

I have 2 relationships between 2 tables. First table is called Personnel, the second one is Equipment.

Personnel has 2 connections to Equipment, one being the person who rented it and a second one being the person who added the equipment.

Every time I try to write a query about the person who has the equipment and ask for a name and last name it always shows me the person who added it to me.

How can I force it to only use one "connection"?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Blackwolf23
  • 93
  • 1
  • 8
  • 1
    Please add your tables' schema to your question. – reaanb Jan 09 '17 at 16:16
  • ... and some queries and example table values for input, output and desired output. Read about [ask] and [mcve]s. PS Foreign keys ("relationships") have nothing to do with querying. They just tell the DBMS that values for some columns must also be values for other columns. A table (base or query result) represents an application relationship/association. For each table (base and query) give that by giving what a row says about the application by being in the table. – philipxy Jan 09 '17 at 22:24

1 Answers1

0

A relational table (base or query result) represents an application relationship/association. It holds the rows whose values are so related. Ie that make a true proposition (statement) from a characterizing predicate (statement template).

Your base predicates & tables are something like:

-- ID identifies a person named FNAME LNAME and ...
Personnel(id, fname, lname, ...)
-- equipment ID was added by person AID and rented by person RID and ...
Equipment(id, aid, rid, ...)

We build a query result predicate from base predicates, logic operators and conditions. To get the query expression returning rows that satisfy that predicate, we replace base predicates by corresponding base names and logic operators by corresponding relation operators.

Rows where a person with first name NAME added equipment 77:

/* rows where
for some e.ID, ..., p.ID, ...,
    p.FNAME = NAME
and equipment e.ID was added by person e.AID and rented by person e.RID and ...
and p.ID identifies a person named p.FNAME p.LNAME and ...
and p.ID = e.AID
and e.ID = 77
*/
select p.fname as name
from Equipment e join Personnel p on p.id = e.aid
where e.id = 77

Rows where person ID with last name NAME rented equipment added by person AID with last name ANAME:

/* rows where
for some e.ID, ..., r.ID, ..., a.ID, ...,
    r.ID = ID and r.LNAME = NAME and a.ID = AID and a.LNAME = ANAME
and equipment e.ID was added by person e.AID and rented by person e.RID and ...
and r.ID identifies a person named r.FNAME r.LNAME and ...
and r.ID = e.RID
and a.ID identifies a person named a.FNAME a.LNAME and ...
and a.ID = e.AID
*/
select r.id, r.lname as name, a.id as aid, a.lname as aname
from Equipment e
join Personnel r on r.id = e.rid
join Personnel a on a.id = e.aid

The predicate for a base table using columns prefixed by an alias becomes the table name AS that alias. The AND of base predicates becomes JOIN. The AND of a condition becomes ON or WHERE. Equality of result columns to prefixed columns becomes implicit or explicit AS. Is there any rule of thumb to construct SQL query from a human-readable description?

FKs (foreign keys) are commonly but mistakenly called "relationships". You do not need to know the FKs to query. They just tell the DBMS that values for certain columns must also be values for certain other columns. (For integrity and optimization.) FKs actually correspond to instances of a meta relationship/association on tables. (Equivalently, on predicates. Equivalently, on relationships/associations.) As represented by certain DBMS metadata tables with predicates about values for certain columns having to also be values for certain other columns.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83