Query same table twice? - 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

Query same table twice?

Author

Topic

lgarcia3
Starting Member

15 Posts

Posted - 2011-04-13 : 18:00:04

I have a view where I am retrieving data from a table which has two FK to the same second table. The data is for employees who run orders. The table "orders" has two FK to the "employees" table. The first FK retrieves the person who works with the client in the store, the second FK retrieves the person who is the cashier and processes the payment. So, my question is how do I retieve data with both FK?
Thanks!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-13 : 18:25:20

FROM MainTable
INNER JOIN LookupTable l1 ON <join criteria>
INNER JOIN LookupTable l2 ON <join criteria>

--
Gail Shaw
SQL Server MVP

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-13 : 22:12:49

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL PRIMARY KEY,
emp_name VARCHAR(25) NOT NULL.
..);

The emp_id has two roles in the Orders table, like this:

CREATE TABLE Orders
(sales_ticket CHAR(15) NOT NULL PRIMARY KEY,
salesman_emp_id CHAR(10) NOT NULL
REFERENCES Personnel(emp_id),
cashier_emp_id CHAR(10) NOT NULL
REFERENCES Personnel(emp_id),
..);

When you do the joins, you need a copy of Personnel for each role:

SELECT Orders.sales_ticket,
Orders.salesman_emp_id, Salesmen.emp_name AS salesman_name,
Orders.cashier_emp_id, Cashiers..emp_name AS cashier_name,
..
AND Orders.emp_id = Cashiers.emp_id;
FROM Orders,
Personnel AS Salesmen,
Personnel AS Cashiers
WHERE Orders.salesman_emp_id = Salesmen.emp_id
AND Orders.cashier_emp_id = Cashiers.emp_id;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources