quote:

---

Originally posted by tkizer
Customer_ID float - really? I wouldn't want approximate data for my customer ids. How about bigint instead?

---

quote:

---

Originally posted by motilok

QUESTION:
How to fill in the blanks of the report from different tables? Table Guests has some info, and table Customers has similar info. If a guest has a customerID, i need my query pull FirstName, LastName and phone from Customers table, if a guest does not have a customerID (ie, has not registered with our company and only has minimal personal info available on our temp database) i need FirstName, LastName and phone to be pulled from Guests table.

DDL INFO:
1) Create table GUESTS
Guest_ID PK, int, not null
Customer_ID FK, float, null
FirstName varchar(50), null
LastName varchar(50), null
Phone nchar(10), null

2) Create Table TripInfo
Trip_ID PK, int, not null
Player_ID FK, float, null
Guest_ID FK, int, not null
OtherInfo1 datetime, not null
OtherInfo2 varchar(50)
OtherInfo3 varchar(50)

3) Create Table CUSTOMERS (this on is from original database, the other two above are from my creation)
Customer_ID PK, int, not null
FirstName varchar(20), not null
LastName varchar(20), not null
Phone varchar(21), null


select g.Guest_ID, t.Trip_ID, t.Customer_ID,
FirstName = case(t.Player_ID)
when null then (select g.FirstName from GUESTS where g.Guest_ID=t.Guest_ID)
when ='0' then (select g.FirstName from GUESTS where g.Guest_ID=t.Guest_ID)
else (select c.FirstName from customers s where c.Customer_ID=t.Customer_ID)
end
From guests g (nolock)
left join tripinfo t (nolock) on g.guest_id=t.guest_id
left join custoemers c (nolock) on c.customer_ID=t.Customer_ID


Hope this clarifies some of the confusion. I can usually write simple code and never done anything as involved as this project. Appologies for not having it all in a proper format. I'm not at work and had to type everything up.

---

SELECT 
COALESCE(c.FirstName, g.FirstName) AS FirstName,
COALESCE(c.LastName , g.LastName ) AS LastName,
COALESCE(c.Phone, g.Phone) AS Phone
FROM Guests g
LEFT JOIN Customer c
ON c.Customer_ID = g.Customer_ID

quote:

---

Originally posted by motilok

quote:

---

Originally posted by tkizer
Customer_ID float - really? I wouldn't want approximate data for my customer ids. How about bigint instead?

---

Tara, the only reason I used float, because I have been told to do so. I'm not good at this type of work as you can see.
In the original database, where that Customer_ID is automatically created, it is int, but for my database, I have been told to make it a float. I am not even sure why, and whenever I have question, the answer is usually "google it".

---