Get information from a joined table AND a table that isn't joined

Question

This is the example I am working off of

SELECT   *
FROM     table1,
         table2
WHERE    table1.id = table2.id

However, my "table1" needs to be a joined table (lets say from tables "tableA, tableB")

I am not sure how to use two tables when one of them is a joined table. Also, I am not sure how to alias a table that is joined (rename the joined table of tableA/tableB to table1).

Any help would be great

Thanks

what do you mean by joined table? do you mean this is a view? — luksch, Jul 23 '13 at 13:56
So essentially what I need is to get information from three tables: table1, tableA and tableB. I was able to do a join on tableA and tableB to make table2. However, I now need to gather information from table1 and table2(tableA/tableB combined) but they do not share any key value. How would I do this? — dev, Jul 23 '13 at 14:04
if they dont share any key value, then you propably shouldnt be joining them coz you can be sure that your data is valid. If you want to have every record in `table2` be combined with every record in `table1` then you need to do a `CROSS JOIN` — Jafar Kofahi, Jul 23 '13 at 14:06
So explain in business terms how the relation in table1 is related to that in tableA/B — mmmmmm, Jul 23 '13 at 14:07
I understand that it normally isn't logical to do this. In the future these tables will share a key value. However, to progress on what I am working on now, I just need to see all the data from table1 and table2(tableA/tableB combined). I was able to do this using the CROSS JOIN. Thank you Jafar. — dev, Jul 23 '13 at 14:09

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

The syntax you are using is implicit. The ANSI standard syntax uses JOIN:

SELECT columnList
FROM table1 a
JOIN table2 b
  ON b.id = a.id

The letters following the table names are aliases. Depending on the platform, the rules and proper markup may vary.

Further reading

edited May 23 '17 at 12:03

Community

1
1

answered Jul 23 '13 at 13:55

Kermit

33,827
13
85
121

nice hint about the implicit vs explicit join syntax, but I am not convinced if this is what the OP actually asked... – luksch Jul 23 '13 at 14:02
@luksch I agree it's unclear. I did the best with my interpretation and voted to close it. – Kermit Jul 23 '13 at 14:02

score 0 · Answer 2 · answered Jul 23 '13 at 14:05

If you are asking about how to incorporate your joined tables query into this one then you need to include it as a subquery then you would alias that subquery with a name (i.e. table1), here is how it would look like:

SELECT   *
FROM     
     (
       SELECT *
         FROM tableA ta, 
             tableB tb
        WHERE ta.?? = tb.??
     ) table1, <-- table1 here is an alias
         table2
WHERE    table1.id = table2.id

Here is more info about Subqueries and joins

Get information from a joined table AND a table that isn't joined

2 Answers2