MySQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dave9191
Forum Newbie
Posts: 9
Joined: Wed May 28, 2003 11:31 am

MySQL query

Post by Dave9191 »

Ok, Ill just start by telling you about my tables.

I have 2 table, d9_admin and d9_news. In the news table there is a field that stores the admin's ID (int) who wrote it.

Is it possible to make a query in mysql that replaces the admin ID (int) with the admins name after looking it up in the d9_admin table ?

Thx
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post by SBukoski »

Try something like this:

Code: Select all

SELECT a.username, n.* FROM d9_admin a, d9_news n WHERE n.adminid=a.adminid
This assumes of course that there is always a matching adminID in the d9_admin table. If for whatever reason a mnatch isn't found it'll return nothing. If that's the case, which I don't think it is, you need to look into doing a RIGHT JOIN, perhaps.
Dave9191
Forum Newbie
Posts: 9
Joined: Wed May 28, 2003 11:31 am

Post by Dave9191 »

This is perfect :D

But could you possibly tell me what a RIGHT JOIN is? It might be useful for some other quries that I will need later :) Thx
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post by SBukoski »

I hardly understand them myself. Can't wait for 4.1 to come out for MySQL so I can do subselects and not have to worry about right or left joins. You can go to mysql.com and read about them there, although I found it doesn't help much.

Best thing to do is to do a search for them. There are some half decent tutorials out there that can explain them MUCH better than I can. Essentially they just join two or more tables together on a certain condition. But unlike regular joins like the example I gave, it will always make sure that the NEWS row is returned, even if there is not a matching adminid. For example:

Code: Select all

SELECT a.username, n.* FROM d9_news n RIGHT JOIN d9_admin a ON n.adminid=a.adminid
owen
Forum Newbie
Posts: 15
Joined: Fri May 30, 2003 12:40 pm

read

Post by owen »

you need to read a SQL book. Seriously. Anyhow a RIGHT JOIN is used to compare a record in table [A] with one record in table . If the values being compared are the same then you get a single record for each comparision in the output result set. This however is a just a simple round up of a RIGHT JOIN is, there are many other factors which come into play. I can't suggest a book but the database manuals are usually adequate.
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post by SBukoski »

Correct, but it also ensures you get a row back if a record isn't found in Table B. Instead of doing a staright up join which will result in no records being returned.
Post Reply