[RESOLVED] Need help with a querry

[Bongo]

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.

[shakti5385]

Design the query using Access' query designer and test.

[Bongo]

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.

so back to pure SQL.

Anyone else has a solution for this?

[Bongo]

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

[shakti5385]

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

[szlamany]

This should work (use ALIAS syntax)

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.

[Bongo]

This should work (use ALIAS syntax)

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?

[Bongo]

Its Working

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