How to Use The Same Table Twice in a Select Query

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

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

Trouble pulling from the same table twice in one query

I agree, it looks like duplicated data that you have the member id, name and birth date in the demographics table that make multiple rows per. Not cool, but if that's what you have.

If the 'MemberID' is the unique ID and the member's name and birth date will be the same on every row, then you could apply a group by the memberID and just do a MAX() on all fields you want that wont change. Ex: If my birth day is March 5, 2000, and I have 10 records with birth day for me are all March 5, 2020, then MAX(d.DateOfBirth) would still yield March 5. So with that premise in mind, this query might help get what you are looking for.

SELECT 
        d.MemberID,
        max( d.MemberName ) MemberName,
        max( d.DateOfBirth ) DateOfBirth,
        max( each other blah blah fields) blahField1,
        coalesce( max( v.LastVisitDate ), '' ) LastVisitDate,
        coalesce( max( v.NextVisitDate ), '' ) NextVisitDate,
        max( CASE WHEN d.MemberFlag LIKE '%transf%' THEN 'Transferred'
                 ELSE ''
            END) AS Transferred
    from
        Demographic d 
            JOIN Visit v 
                ON d.MemberID = v.MemberID
    group by
        d.MemberID

SQL Query, Using Same Table Twice

In the second query by doing:

INNER JOIN actor a1 ON a1.actor_id = fa.actor_id
INNER JOIN actor a2 ON a2.actor_id = fa.actor_id

The actors a1 and a2 must be the same as they have the same id which is the value of fa.actor_id.

So, then, this actor should also have:

first_name = 'CATE' and last_name = 'MCQUEEN'
and
first_name = 'CUBA' and last_name = 'BIRCH'

at the same time. Which can't happen.

That's why you need to join it twice with film_actor table so that a1 and a2 can be different.

Joining the same table twice in one query

A typical method uses left join and coalesce():

select a.*,
       coalesce(b.col1, c.col1) as col1
from a left join
     b
     on b.ext_id = a._id left join
     c
     on c.ext_id = a._id;

Use the coalesce() for all columns that you want from the two tables.

How to join same table twice on Access

In MS Access, more than one JOIN requires parentheses pairings:

SELECT d.departmentID, d.depName, d.location, c1.memberID, 
       c1.fullName,  c1.reportsTo, c2.fullName
FROM (Contacts as c1
INNER JOIN Departments as d
   ON c1.departmentID = d.departmentID)
INNER JOIN Contacts as c2
   ON c1.reprtsTo = c2.memberID

Joining the same table twice with different values

Basically, you need another join on areas_group; to disambiguate the two references to the same table, you need to use a table alias.

Actually, it is a good practice to use table aliases for all tables that come into play in the query: this makes the query shorter to read and write.

SELECT 
    t.id AS transfer_id, 
    t.name AS transfer_name,  
    t.pickup_areas_group_id AS transfer_pickup_areas_group_id,
    ag1.area_id AS pickup_area_ids,
    t.drop_areas_group_id AS transfer_drop_areas_group_id, 
    ag2.area_id AS drop_area_ids
    tp.vehicle_id AS vehicle_id, 
    tp.date_start AS date_start, 
    tp.date_end AS date_end, 
    tp.price AS price 
FROM transfers t
INNER JOIN transfers_pricing tp ON tp.transfer_id = t.id
INNER JOIN areas_group ag1 ON ag1.id = t.pickup_areas_group_id
INNER JOIN areas_group ag2 ON ag2.id = t.drop_areas_group_id

How to optimize this SQL query which select from the same table twice

    SELECT 
    CONCAT_WS(' ', first_name, last_name) AS fullName
FROM employees 
    JOIN dept_emp ON dept_emp.emp_no=employees.emp_no
    JOIN dept_manager ON dept_manager.dept_no=dept_emp.dept_no
    # added after comment from Rick James
        AND dept_manager.from_date <= emp.end_date 
            AND emp.from_date <= dept_manager.to_date
    JOIN employees managers ON managers.emp_no=dept_manager.emp_no 
        AND managers.last_name=employees.lastname

From the top of my head. didnt test btw.

And add an index:
last_name

ALTER TABLE `employees` 
ADD INDEX `idx_lastName`(`last_name`) USING BTREE;