[RESOLVED] Need help with a querry

Hi guys,

I need help with a querry. (Access DB 2003)

I have 2 Tables

Table1.....Field
===========
..............GuestID
..............GuestName

The Field GuestID is Unique and starts either with the letter 'F' or 'P' depending if the guest is a private person or a company


Table2.....Field
===========
.............BookingID
.............GuestID1
.............GuestID2
.............Arrival
.............Departure

The Fields GuestID1 and GuestID2 are the link to the Table1.GuestID
GuestID1 has alltimes a value GuestID2 could have an entry. This is
depending if on the Invoice has the private and company name on it.


So what I need is a querry which solves the following
Result should look like this

Table2.BookingID
Table1.GuestName - Related to Table2.GuestID1
Table1.GuestName - Related to Table2.GuestID2 (keep in mind this
...................................................................field could be NULL)

the rest I think i can figure out . hopefully ;)

Hope you can help me with this.

many thx in advance and a peacefull xmas season to you all.

Design the query using Access' query designer and test.

Quote:

---

Originally Posted by shakti5385

Design the query using Access' query designer and test.

---

thx shakti5383 - this was my first approche but what ever i tried i could not making work. :mad:

so back to pure SQL.

Anyone else has a solution for this?

I came up sofare with this querry

SELECT Table2.BookingID, T1.Name, T2.Name, Arraival, Derparture
FROM (Table2 INNER JOIN Table1 AS T1 ON Table2.GuestID1 = T1.GuestID) INNER JOIN Table1 AS T2 ON Table2.GuestID2 = T2.GuestID

I only get records
But i still do not get records where on Table2.GuestID2 the Value = NULL

Code:

---

SELECT Table2.BookingID, T1.Name, T2.Name, Arraival, Derparture
FROM (Table2 LEFT JOIN Table1 AS T1 ON Table2.GuestID1 = T1.GuestID) LEFT JOIN Table1 AS T2 ON Table2.GuestID2 = T2.GuestID

---

Use Left Join :wave:

This should work (use ALIAS syntax)

Code:

---

Select TA.BookingId
                ,TA.GuestID1
                ,T1.GuestName as GuestName1
                ,TA.GuestID2
                ,T2.GuestName as GuestName2
                ,TA.Arrival
                ,TA.Departure
        From Table2 TA
        Left Join Table1 T1 on T1.GuestId=TA.GuestID1
        Left Join Table1 T2 on T2.GuestId=TA.GuestID2

---

This would work in MS SQL - you can join the same table as many times as you want as long as you give them a unique alias name.

Quote:

---

Originally Posted by szlamany

This should work (use ALIAS syntax)

Code:

---

Select TA.BookingId
                ,TA.GuestID1
                ,T1.GuestName as GuestName1
                ,TA.GuestID2
                ,T2.GuestName as GuestName2
                ,TA.Arrival
                ,TA.Departure
        From Table2 TA
        Left Join Table1 T1 on T1.GuestId=TA.GuestID1
        Left Join Table1 T2 on T2.GuestId=TA.GuestID2

---

This would work in MS SQL - you can join the same table as many times as you want as long as you give them a unique alias name.

---

@shakti5385 - thx

@szlamany
thx as well but I still get an error which says roughly translatet into english

Syntaxerror (missing operator) on query 'T1.GuestID=TA.GuestID1 ON (Left Join Table1 T2 on T2.GuestId=TA.GuestID2)'

What else I am doing wrong?

:D Its Working :D

Many thanks to you guys and to all a merry xmas and a good 2007