Select Statement - Two Tables

[ljmyers]

My apologies if the answer to this is here somewhere. I have searched for days and tried writing it 500 different ways trying to figure this out but cannot get it to work just as i want it to. I have two tables, prefix_users and prefix_recipes. I need for the statement to look at and compare the id (user's id) from table prefix_users with the user_id and id (recipe's id) from the table prefix_recipes and if they are the same, show the recipe's name on the page. It is the user's profile page that I am trying to include the recipes that the user has submitted on their specific page. Any help would be great.

table: prefix_users
id (user's id)

table: prefix_recipes
id (recipe's id)
user_id
name (recipe's name)

Smiles,
Lana

[Fou-Lu]

This is actually an SQL question, not a PHP one.
I don't understand what you're trying to do with this though. From the sounds of you're rules, you're looking to lookup WHERE users.id = recipes.id = recipes.user_id?
Anyway, simple where clauses for those:

SELECT `name` FROM `prefix_recipes` WHERE `id` = {id} AND `user_id` = {id}

[ljmyers]

Thanks so much for the time you took Fou-Lu. I knew it was something simple and I have little knowledge with sql statements and for some reason, could not wrap my brain around it.

[Fou-Lu]

np, is that what you're looking for though? The logic of you're table schema seems to apply the same foreign key to the values of two seemingly unrelated fields, which will likely provide inaccurate results. I'm thinking you're only wanting to compare on a user id, so you can fetch all recipes owned by the specified user. This would indicate a surrogate key for the value of you're recipe id (a surrogate is a key that is automatically generated for you, so you're field for recipe id would be an auto_increment int).
Another design approach would be to use a composite key between the recipe id and the user id. They are still unrelated (in that you only want the user id), but are unique and can be used as an easy count between each user. This would have data like so:
recipe id, userid, name
1, 1, 'Cake'
2, 1, 'Cheese Cake'
1, 2, 'Shortbread'
etc

Both are equally usable, though the surrogate approach does provide easier extension into additional tables for you're recipes (the latter approach would require both the recipe id and the user id to lookup a single recipe, while the former would require only the recipe id).

[bazz]

should it not be

select
  pu.name
, pr.recipe_name
from prefix_users pu
inner join
prefix_recipes pr
on pr.user_id = pu.id

table structure should be (I reckon anyway)

table: prefix_users
user_id not null auto_increment primary key

table: prefix_recipes
user_id int not null
name (recipe's name) varchar(99)

bazz