Join Problem

[Guest]

Hello
I am sure this question is already answered, but so far I havent found the cure for this problem. I have two tables lemme call them tbl1 and tbl2. tbl1 has 2 fields, RoomNo and Type. tbl2 has many fields (including RoomNo and Type
Depending upon the type, I want to select all the results from tbl1 minus tbl2. To Further illustrate, here is sample data

tbl
RoomNo Typ
100 LuxurySuit
101 LuxurySuit
102 DoubleBe
103 DoubleBe
104 SingleBe
105 SingleBe

tbl
RoomNo Typ
102 DoubleBe

The Inner Join Query result provides me with 102 Doublebed as the result, instead I want RoomNo 100, 101, 103, 104 105 as the result. Any help will be appreciated. (Sorry for the long post.

Linwohtoha

 

[Guest]

Try this:

SELECT [Table1].[ID]
FROM Table1 LEFT JOIN Table2 ON [Table1].[ID] = [Table2].
[ID]
WHERE ([Table2].[ID] Is Null);

-----Original Message-----
Hello!
I am sure this question is already answered, but so far

I havent found the cure for this problem. I have two
tables lemme call them tbl1 and tbl2. tbl1 has 2 fields,
RoomNo and Type. tbl2 has many fields (including RoomNo
and Type )

Depending upon the type, I want to select all the results

from tbl1 minus tbl2. To Further illustrate, here is
sample data.

tbl1
RoomNo Type
100 LuxurySuite
101 LuxurySuite
102 DoubleBed
103 DoubleBed
104 SingleBed
105 SingleBed

tbl2
RoomNo Type
102 DoubleBed

The Inner Join Query result provides me with 102

Doublebed as the result, instead I want RoomNo 100, 101,
103, 104 105 as the result. Any help will be
appreciated. (Sorry for the long post.)

 

[Lynn Trapp]

From what you have described, the following should work:

SELECT Tbl1.RoomNo, Tbl1.Type
FROM Tbl1 INNER JOIN Tbl2 ON (Tbl1.RoomNo <> Tbl2.RoomNo);

However, I suspect you may be wanting something a bit different. Feel free
to post back if you have further questions or want to clarify your process a
bit.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hello!
I am sure this question is already answered, but so far I havent found

the cure for this problem. I have two tables lemme call them tbl1 and tbl2.
tbl1 has 2 fields, RoomNo and Type. tbl2 has many fields (including RoomNo
and Type )

Depending upon the type, I want to select all the results from tbl1 minus

tbl2. To Further illustrate, here is sample data.

tbl1
RoomNo Type
100 LuxurySuite
101 LuxurySuite
102 DoubleBed
103 DoubleBed
104 SingleBed
105 SingleBed

tbl2
RoomNo Type
102 DoubleBed

The Inner Join Query result provides me with 102 Doublebed as the result,

instead I want RoomNo 100, 101, 103, 104 105 as the result. Any help will
be appreciated. (Sorry for the long post.)

 

[Guest]

Thanks for the reply, Solution 1 and 2, gives me the same result. Lemme clarify it more. Table1 has all the hotel rooms in it with its descriptions. Table2 has all the room currently checked out. Hence doing a Join query to get the NON Checked out rooms from the Table1. Using Solution1 and 2, I get all the rooms which are checked out (as of yet). I hope I had explained it before. Thanks for all the help

Linwohtohai

 

[Guest]

Hello
My mistake, I didnt exhaust the Solution1 before posting back here. I have it working. Now I have all the NON Checked out rooms result and the corresponding second field shows as null, created by the join. i.e.
tbl1.roomNo tbl2.roomN
101 Nul
103 Nul
104 Nul
105 Nul
106 Nul

Anyway to remove that second field so I can out this sql statement as rowsource for a combobox

Thank
Linwohtohai

 

[Michel Walsh]

Hi,


Just don't include it in the SELECT clause.... ?



Hoping it may help,
Vanderghast, Access MVP

Hello!
My mistake, I didnt exhaust the Solution1 before posting back here. I

have it working. Now I have all the NON Checked out rooms result and the
corresponding second field shows as null, created by the join. i.e.

 

[Guest]

Thanks, it works. Thanks to all the people who helped

Linwohtohai

 

[John Vinson]

The Inner Join Query result provides me with 102 Doublebed as the result, instead I want RoomNo 100, 101, 103, 104 105 as the result. Any help will be appreciated. (Sorry for the long post.)

Change the query from an Inner Join to a Left Outer Join; select
*only* the RoomNo field from Tbl2, and use a criterion on it of

IS NULL

The SQL would be something like

SELECT tbl1.*
FROM tbl1 LEFT JOIN tbl2
ON tbl1.RoomNo = tbl2.RoomNo
AND tbl1.Type = tbl2.Type
WHERE tbl2.RoomNo IS NULL;