help building a JOIN, possibly?

[9three]

Hey,

 

I'm going to be implementing an auto complete so what I'm doing is grabbing all the IDs from one table and checking a 2nd table for the ID so that I can grab the persons name.

 

What I'm having issue with is how I should do this. Meaning, should I use a JOIN or two SELECTs. I would like to use a JOIN if possible.

 

table: vip

columns

--------

v_id - vid1(FK) - vid2(FK) - vconfirmed(enum[y, n])

 

table: customers

columns

------------------

c_id - fname - lname

 

c_id is the primary key which is used in the vip table as foreign keys.

 

The reason I need both tables is because if they change their names I want it to update dynamically. Otherwise I would just store the names in the vip table.

 

So what I'm doing is as follow:

 

SELECT vid2
FROM vip
WHERE vconfirmed = "Y"
AND vid1 = 70

 

But for me to have the names I would need the 2nd table. So something like this is what I'm looking for:

 

 

SELECT vip.vid2, customers.id, customers.fname, customers.lname
FROM vip
INNER JOIN customers
ON vip.vid2 = customers.id
WHERE vip.vconfirmed = "Y"
AND vip.vid2 = 70

 

If I'm user 70, I want to grab all the IDs that match me. Take those IDs and join them with customers table that match vid2.

 

I would also need to select only the names that are matched (or close to it)

 

SELECT vip.vid2, customers.id, customers.fname, customers.lname
FROM vip
INNER JOIN customers
ON vip.vid2 = customers.id
WHERE vip.vconfirmed = "Y"
AND customer.fname LIKE "a%"
AND vip.vid2 = 70

 

Assuming "a" was the letter that was asked for.

 

I'm not sure if I'm doing it correctly? Can someone lend a hand? Thanks

[Alt_F4]

looks fine to me, are you getting the results you expect from the database?