CODEWITHSUNDEEP
sql
locoboy
locoboy

Reputation: 38920

Is it possible to use the same table twice in a select query?

Hi I have the following query, and I'm wondering what it means:

SELECT c1.id as sender, c2.id as replier
   FROM contacts c1, contacts c2;

How can you use the same table twice?

Upvotes: 15

Views: 98602

Answers (5)

Pearl
Pearl

Reputation: 941

Yes, you can use the same table twice by giving different aliases to the table. I think studying about self joins will help you understand.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

Yes, you can use the same table more than once within the same SELECT query.

Note that you only need to use table correlation names (colloquially 'aliases') when the table appears more than once within the same scope. For example, the following SELECT query uses the same table twice but, because each is within a distinct scope (each separated by the UNION keyword), no table correlation name is required:

SELECT id, 'Sender' AS contact_narrative
  FROM contacts
 WHERE something = 1
UNION
SELECT id, 'Replier' AS contact_narrative
  FROM contacts
 WHERE something = 2;

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753605

You use a single table twice in a query by giving it two names, like that.

The aliases are often introduced with the keyword AS. You also normally specify a join condition (for without it, you get the Cartesian Product of the table joined with itself). For preference you use the explicit JOIN notation.

SELECT c1.id AS sender, c2.id AS replier
  FROM contacts AS c1
  JOIN contacts AS c2 ON c1.xxx = c2.yyy;

It is not clear which columns might be used for the join in this example; we don't have any information to help resolve that.

Normally, there'd be another table to act as intermediary, such as a Messages table:

SELECT c1.id AS sender,  c1.email AS sender_email,
       c2.id AS replier, c2.email AS replier_email,
       m.date_time
  FROM messages AS m
  JOIN contacts AS c1 ON m.sender_id  = c1.id
  JOIN contacts AS c2 ON m.replier_id = c2.id;

Upvotes: 18

Ray Toal
Ray Toal

Reputation: 88378

This query creates a table containing all possible pairs of contact ids.

For example, if your contact ids were 1, 2, and 3 you would get, as a result

1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

Upvotes: 16

gion_13
gion_13

Reputation: 41533

It's a simple answer : use your query listed in the example. It should work just fine. Although this is probably a bad idea, if you want to use the same table twice, be aware that you have to join "those" tables as they were different : 

SELECT c1.id as sender, c2.id as replier
FROM contacts c1, contacts c2
WHERE sender.id = replier.id

Upvotes: 2

Related Questions