1
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?

2 Answers 2

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
1
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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.