MySQL query with exchange of reference to IDs

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
headbull
Forum Newbie
Posts: 10
Joined: Thu Jun 07, 2012 7:18 am

MySQL query with exchange of reference to IDs

Post by headbull »

Hi everyone,

I'm new to this board, and registered to try to solve an issue I have with some SQL queries. I have a database, that has one table for users, one table for shops and another table for services.

When listing the services table, who has references to both the shops and the user table (to their IDs).. I wish to exchange or add fields from the shops and user database. Lets say that my service table looks like this:
id | creator | shopid | comment
I wish for every line it reads from the servicetable, it adds two fields or exchanges creator and shopid with name from the user and the shop table. I guess its possible with the SQL query, but how ?

Hope that someone understand my question, and are able to help me.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: MySQL query with exchange of reference to IDs

Post by Celauran »

Sounds like you want a JOIN.

Something like this:

Code: Select all

SELECT srv.id, srv.comment, u.name AS user, sh.name AS shop_name
FROM services AS srv
JOIN users AS u ON u.id = srv.creator
JOIN shops AS sh ON sh.id = srv.shopid
WHERE whatever
headbull
Forum Newbie
Posts: 10
Joined: Thu Jun 07, 2012 7:18 am

Re: MySQL query with exchange of reference to IDs

Post by headbull »

Do somebody have some easier walkthrough on this usage of JOIN in SQL ?

I'm really struggeling to understand/try to do this.

Did'nt see ur reply before now in regards to the example. I'll work with that, and give u a feedback :)
headbull
Forum Newbie
Posts: 10
Joined: Thu Jun 07, 2012 7:18 am

Re: MySQL query with exchange of reference to IDs

Post by headbull »

I managed to solve this! Thank you very much for your help!

I'm new to making the SQL database doing the work for me, but I defintly see how much time you can save on it now and not least reduce the code!
Post Reply