MS Access / SQL Query Help

[RockManFree]

I need a SQL query to return ALL fields of Table1 and any fields from Table2 where "Slot" is equal.

Table1 has one field named "Slot" and it has 10 rows of sample data (The data = 01,02,...,10)

Table2 has three fields named "Slot" "Contents" and "Status" with the following sample data (it only has three records):
Slot=1 Contents=Red Status=New
Slot=6 Contents=Blue Status=New
Slot=8 Contents=Green Status=Old

The final outcome should look like this: (I need the SQL code that would produce this)

SLOT CONTENTS STATUS
1 Red New
2
3
4
5
6 Blue New
7
8 Green Old
9
10

Please note that all slots 1-10 are reported from Table1 even tho there is no records in Table2 for some of the Slots.

Thank you!

 

[r937]

classic left outer join

SELECT slots.slot
     , data.contents
     , data.status
  FROM table1 AS slots
LEFT OUTER
  JOIN table2 AS data
    ON data.slot = slots.slot

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[RockManFree]

Thanks but this did not work in MS Access 2002. I'm a newbee so have mercy on me, but in my example I did not have a table called "slots" nor a table called "data". Just a table called "table1" and a table called "table2". Any further clarification would be highly appreciated.

 

[markros]

In the above sample Slots and Data are the aliases for the Table1 and Table2. The above query should work in Access after you add couple of ().

PluralSight Learning Library

 

[RockManFree]

Ah, now i get it. Works fine. Thanks a lot!