[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]

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.

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]

Originally Posted by 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.

@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