join for between multiple tables without common fields

Question

table E : id, title, datetime
table L : id, place, xpos, ypos
table M : id, title, datetime, contact, agenda
table ER : parentid, childid, parenttype, child type

data in ER : e.id, l.id, e, l -> E object linked to L object
             m.id, l.id, m, l -> M object linked to L object

I want to select all objects from table E and M such that they have a connection to object in L (in table ER) and that object has value of xpos>25 (arbitrary)

select * from L where xpos > 5
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid

I feel I'm missing the basic working of joins and am constructing a wrong query (approach here). Can it be combined in one query? Or do I have to use multiple queries, one for E and one for M to get the results from each of them?

Answer 1

Which make of SQL are you using? I am most familiar with Microsoft SQL Server. Syntactically, you need to move the WHERE after all of the JOIN statements. (I appended the tablename L to its references for consistency.)

select * from L
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid
where L.xpos > 5

This is legal syntax. The next question is what results you want. I think possibly so, although you did not show a sample of results. Perhaps you can play with the data a bit to decide what you need.

CREATE table E (id INT, title varchar(50), [datetime] DateTime);
CREATE table L (id INT, place varchar(50), xpos float, ypos float);
CREATE table M (id INT, title varchar(50), [datetime] DateTime, contact varchar(50), agenda varchar(50));
CREATE table ER (parentid INT, childid INT, parenttype INT, childtype INT);

INSERT INTO L VALUES (1, 'Boston',123.234, 23.234);
INSERT INTO L VALUES (3, 'Miami', 456.234, 78.234);
INSERT INTO ER VALUES (100,1, 5, 4);
INSERT INTO ER VALUES (200,2, 5, 4);
INSERT INTO ER VALUES (200,3, 5, 4);
INSERT INTO ER VALUES (100,4, 5, 4);
INSERT INTO E VALUES (100, 'Able','2014/01/02');
INSERT INTO E VALUES (2, 'Baker','2014/01/11');
INSERT INTO M VALUES (100, 'Love', '2014/04/04', 'George', 'None');
INSERT INTO M VALUES (200, 'Fury', '2014/05/05', 'Bernard', 'Something big');


SELECT * FROM L 
    LEFT OUTER JOIN ER on ER.childid = L.id
    LEFT OUTER JOIN E on E.id= ER.parentid
    LEFT OUTER JOIN M on M.id= ER.parentid
    WHERE L.xpos > 5

DROP TABLE E
DROP TABLE L
DROP TABLE M
DROP TABLE ER

Answer 2

SELECT L.*, E.*, M.*

FROM L 

  JOIN ER AS ER1
    ON  ER1.childid = L.id
    AND ER1.childtype ='l'
  JOIN E
    ON  ER1.parentid = E.id
    AND ER1.parentype = 'e'

  JOIN ER AS ER2
    ON  ER2.childid = L.id
    AND ER2.childtype = 'l'
  JOIN M
    ON ER2.parentid = M.id
    AND ER2.parentype = 'm' 

WHERE L.xpos > 5 ;