SQL SELECT QUERY 1

[Tech718]

i have two tables users and posts. users has fields named, id, name and telephone. Posts has fields named id, posting and date. i would like to write a query that returns the name of the user based on the id they entered into the posts table
i tried the following......and it works however it returns all the names of the users in the users table for me to select from. Is there a way i can get it to return only the id of the current row/record? (Dlook up wont work as this is not a form but a table/query)

SELECT users.name, posts. id
FROM users, posts
WHERE (((users.id)=posts.id));

 

[ck1999]

Not sure what is going on this should work

try

SELECT *
FROM users, post
where users.id = posts.id;

ck1999

 

[Tech718]

hmmm...tried that before...only issue with that is that i still have to manually enter the users id...i want it to be automated

 

[ck1999]

Is there a field in the users table labeled ID? if so it should not ask you for an ID.

ck1999

 

[Tech718]

yeah both tables have a field called id...what i am really trying to do is to get a field to populate based on information in another field...is there a way i can get this done....eg...once they enter there user id when making there posts there username will automatically go to the username field.

 

[HarleyQuinn]

Are they entering their posts onto a front end form?


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[ck1999]

Tech718 are the entering the data into a table? or a form?

i would like to write a query that returns the name of the user based on the id they entered into the posts table

I was understanding you were trying to make a query?

I do not believe if you enter a value in 1 field of a table that you can get another field of that table to automatically update. You can if you are using a form.

Why is someone entering data through a table or query? That is what forms are for!

ck1999

 

[Tech718]

i am using this in a web page...the information is entered into a form in the web page which sends it to the database.

 

[lespaul]

so somewhere on your webpage do you have the userid (or can you capture it) to include as part of your query so there's a SPECIFIC user id, not all the ones in the user table.

WHERE posts.userid = ValueFromWebPage

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Tech718]

hey,
i am using frontpage and access. the form on the webpage submits the userid as a hidden form field to the access database table so the person posting the info doesn't have to input there user id it.
the code is
<input type="hidden" name="uid" value="<%=session("uid")%>">

all i want is when the user submits their posts to the posts table it will retreive the name of the person from the users table based on the uid that was inserted

 

[lespaul]

are you saying you have the persons name in BOTH tables?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Tech718]

no the common field is the user id field...

 

[ck1999]

I am not familiar with using frontpage with access so I do not know if this can be done or not, but can you use a dlookup from frontpage? if not can you call a procedure that would go through the users table and updates any names that are blank by a dlookup in the posts table.

If neither of the other 2 are possible can you run the above procedure from inside of access when you open it up

ck1999

 

[lespaul]

Ok, so you have a webpage where users can enter posts.

You have a table USERS that contains the UserID and UserName and telephone number.
You have a table POSTS that contains UserID, Posting, PostDate (you really should not use key words like DATE as a field name).

Now you say:

returns the name of the user based on the id they entered into the posts table

How did you enter the userid into the POSTS table? Did the user enter their UserID somewhere?

If you have the userid you HAVE to include that in the query if you want the specific name that belongs to THAT userID:

SELECT UserName FROM USERS WHERE USERID = UserIDthatwasentered

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Tech718]

pardon my ignorance with access as for frontpage
dont think you can do dlook up in frontpage...can i use dlookup in a table or is it just for forms?....how would i write the dlook up statement to get my results....i did it in a form in access and it worked perfectly but dont know how and if it is even possible to get the info from the form back to the table.
i used this in a form

DLookup("Username", "Users", "UID = " & Forms![Users]!UID)

 

[Tech718]

the userid gets entered automatically once the person has logged on to the site. Once they login and post it captures there login information in a hidden form field on the post form.

in the possible code you gave me

SELECT UserName FROM USERS WHERE USERID = UserIDthatwasentered

should i enter "UserIdthatwasentered"...or are you showing me to enter the actual user id that was entered...? because remeber the user id is sent to the form automatically.

what happens is that once they post their posting others are able to search through posts. I want there real names to show up in the search results instead of their user id.
thanks for your help

 

[lespaul]

the userid gets entered automatically once the person has logged on to the site

Then you need to store that information so that you can use it in your query:

SELECT P.*, UserName FROM Posts P
INNER JOIN Users U ON U.UserID = P.UserID
WHERE UserID = WhateverVariableYouHaveStoredTheUserIDinWhenTheyLoggedOn

 

[tperri]

lespaul,

star for you for the Best Variable Name of the Day

 

[Tech718]

Thanks!!!! will try it now...one question....i am A newbie to sql...do i enter the query the exact way you gAve it to me or am i substituting the P and U for anything?

 

[lespaul]

P and U are aliases for the table names just so you don't have to type out the entire name when specifying which fields to get. For example, if you wrote this query:

SELECT UserID FROM Posts
INNER JOIN Users ON Posts.UserID = Users.UserID

you would get an error when trying to run the query because the field in the SELECT clause is in BOTH tables and the query generator doesn't know which one to get, so you have to tell it:

SELECT Posts.UserID FROM Posts
INNER JOIN Users ON Posts.UserID = Users.UserID

Now, depending on the query, it can get rather long depending on your table and field names, so you can assign an alias to the table and use that instead:

SELECT P.UserID FROM Posts P
INNER JOIN Users U ON P.UserID = U.UserID

so it's really just a kind of shorthand to identify the tables.

Thanks for the star tperri! After reviewing the query above I did notice that I was missing the table reference in the WHERE clause. You'll need to fix this:

SELECT P.*, UserName FROM Posts P
INNER JOIN Users U ON U.UserID = P.UserID
WHERE [b]P.[/b]UserID = WhateverVariableYouHaveStoredTheUserIDinWhenTheyLoggedOn

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com