Page 1 of 1

MySQL query

Posted: Mon Jun 09, 2003 8:56 pm
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

Posted: Mon Jun 09, 2003 9:22 pm
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.

Posted: Tue Jun 10, 2003 9:52 am
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

Posted: Tue Jun 10, 2003 10:14 am
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

read

Posted: Tue Jun 10, 2003 10:17 am
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.

Posted: Tue Jun 10, 2003 10:57 am
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.