SQL query - Can I query 2 tables that both have an ID field and retrieve both fields?

Question

I have two tables. Both have fields called "ID".

Table 1 has "ID", "Title", "Shift"
Table 2 has "ID", "Table1ID", "Details"

I would like to query Table 2 and retrieve all it's details based on an "ID" value but also get the values from Table 1 that relate to the "Table1ID" value.

I've tried this... SELECT * FROM Table2 a, Table1 b WHERE a.TableID = b.ID

This works but only retrieves one tables "ID" field.

I've been playing about with UNION ALL but can't get it to work.

Any ideas?

Thanks

Show us sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Mar 15 '17 at 14:36

score 1 · Accepted Answer · edited Mar 15 '17 at 14:37

1

Yes, you can add an alias:

SELECT a.ID AID, a.Title, a.Shift, b.ID BID, b.TableID, b.Details 
FROM Table2 a, Table1 b 
WHERE a.TableID = b.ID

The above will return ID from table A and B as AID and BID in the result.

edited Mar 15 '17 at 14:37

Juan Carlos Oropeza

47,252
12
78
118

answered Mar 15 '17 at 14:34

Cyclonecode

29,115
11
72
93

2

Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. – Juan Carlos Oropeza Mar 15 '17 at 14:37
@Cyclonecode Thanks for the great answer. Is there a way to get the SELECT to get all the fields (without explicitly writing them) and still alias a specific field? My actual Table2 has quite a few fields (I omitted them for clarity with the question)? Thanks again :-) – John T Mar 15 '17 at 14:41
1

@JohnT - Yes, this should work `SELECT A.*, B.*, A.ID AID, B.ID BID FROM Table1 A, Table2 B` – Cyclonecode Mar 15 '17 at 15:00
@JohnT - As others have pointed out this is considered harmful, read http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful – Cyclonecode Mar 15 '17 at 15:23
1

@Cyclonecode yep I had read that. I'm going to re-work a lot of the code (including SQL queries) into classes as part of the bigger project. This is just a bit rough and ready to get something usable up and running. I'll make sure I streamline the queries during the second phase. Thanks again for all your help. It's very much appreciated. :-) – John T Mar 15 '17 at 19:23

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

Is there a way to get the SELECT to get all the fields (without explicitly writing them) and still alias a specific field?

yes its possible but is considered harmful

This is how you would do id

SELECT Table1.*,Table2.* from table1 inner join Table2 on Table1.ID = Table2.Table1ID;

Why I'm saying its harmful see here : Why is SELECT * considered harmful?

Proper select should be :

SELECT Table1.ID,Table1.Title,Table1.Shift,Table2.ID.Table2.Table1ID,Table2.Details.* from table1 inner join Table2 on Table1.ID = Table2.Table1ID;

SQL query - Can I query 2 tables that both have an ID field and retrieve both fields?

2 Answers2