1

I don't know if this joining construct is allowed. I searched the Internet but I haven't found any information about it.

I want to build a SQL query and I want to join two tables to the main table in the from clause. Example:

SELECT*
FROM OrderData OD
Inner JOIN Customer C, Address A 
        ON C.ID = OD.ID 
       AND A.ID = OD.ID

Is there any chance this might work?

philipxy
  • 14,867
  • 6
  • 39
  • 83
aks69cw
  • 11
  • 3
  • 1
    Syntax error. Stick to explicit `JOIN`. – jarlh Apr 17 '18 at 09:31
  • `select ... from t1 join t2 ON ... join t3 ON ...` – jarlh Apr 17 '18 at 09:32
  • it should be two joins, not one – ADyson Apr 17 '18 at 09:33
  • OK, Thank you all. I'll stick to the proven construct. – aks69cw Apr 17 '18 at 09:40
  • Hi. Why are you writing it if it doesn't look like anything you have ever seen that you are allowed to write? (Rhetorical.) PS One *can* mix comma with explicit `join`; comma has lower precedence. But this does not mix them according to any presentation of the syntax that you have ever seen. Also when you ask about SQL say/tag *which one*, your DBMS. – philipxy Apr 18 '18 at 03:37
  • I was thinking this might work because the tables and the ID' s can be joined in the proven way and this way is also known. I just wanted to write my query faster and simpler – aks69cw Apr 19 '18 at 13:56
  • You have not said what you *want*, you have only given a query that is wrong. Please be clear. Please clarify by editing your post, not in comments. If you know "the proven construct", what exactly is your question? (Presumably you mean, each INNER gets an ON.) What does "this way" mean? Eg what alternate syntax & semantics were you seeking/suspecting? What *sort* of different approach are you hypothesizing? As I said, why have you written something unlike any documentation shows? Do you know of USING & of NATURAL JOIN? Or proper use of comma, with WHERE? – philipxy Apr 21 '18 at 03:35
  • @ADyson That *is* (an attempt at) two joins--an implicit & explict. – philipxy Apr 21 '18 at 03:53
  • @philipxy True, I suppose I meant it would be better expressed as two explicit joins (and the clauses in the ON separated into the correct places) – ADyson Apr 21 '18 at 06:04

2 Answers2

1

NO..MySql/Sql Server doesn't support such implicit joins..rather use separate joins for both tables

select *
FROM OrderData OD
JOIN Customer C ON C.ID = OD.ID 
JOIN Address A ON A.ID = OD.ID
WorksOnMyLocal
  • 1,617
  • 3
  • 23
  • 44
  • Your join conditions look suspect - `ID` has the same 'meaning' in tables whose names imply they should be different. If they did then MySql *does* support such 'implied' joins via `NATURAL JOIN`. – onedaywhen Apr 17 '18 at 10:27
  • @onedaywhen I suspect they mean "implicit" join (ie comma), not "implied" join. Also I suspect the answerer is trying to say something about particular mixes of implicit & explicit vs just explicit, but it's not clear what. Re ID, that's from the question, so why criticize this answer? – philipxy Apr 21 '18 at 04:09
0

I infer from thr way you formatted your pseudo query that you want to see all your 'join predcates' together. If so, try something like this (I've guessed your key names but you should hopefully get the idea):

SELECT *
  FROM OrderData OD, Customer C, Address A
  WHERE C.CustomerID = OD.CustomerID
    AND A.AddressID = OD.AddressID

Be warned, folk on SO call this join syntax 'deprecated' and/or 'dangerous'. The fact is, it's simply a style and one that is still supported a by the current SQL standard and is still popular in more 'academic' books and articles.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • I also tried this style. It works but not in a way as I hoped. Because of the equal sign in the where clause does it work as some kind of INNER JOIN. It was nice to try this, though – aks69cw Apr 19 '18 at 14:04
  • 1
    @aks69cw: I'm intrigued but I don't exactly follow what you are asking for. It would help greatly if you posted sample data for your tables (just a few rows) and the expected result set. – onedaywhen Apr 20 '18 at 08:12
  • @aks69cw What do you mean, "it works but not in a way as I hoped"? Or by "way", or "it works as"? How is it this "works" yet is only a "nice try"? It's the same as to `FROM OrderData OD JOIN Customer C ON C.CustomerID = OD.CustomerID JOIN Address A ON A.AddressID = OD.AddressID`. Comma is CROSS JOIN is JOIN ON true, but with lower precedence. [JOIN ON is the same as a cross join then WHERE.](https://stackoverflow.com/a/25957600/3404097) They will also all be executed the same. Are you able to articulate just what you really want to know? It seems you have some misunderstandings/misconceptions. – philipxy Apr 21 '18 at 04:00