MySQL

New to joins - Need help with query...

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2007

Posts: 16

Reputation: echobase is an unknown quantity at this point

Solved Threads: 1

echobase

Offline

Newbie Poster

New to joins - Need help with query...

Feb 5th, 2008

Hello-

I have these two tables:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
CREATE TABLE ampref (
    ampref_id SERIAL NOT NULL,
    amp_id INTEGER NOT NULL,
    code CHARACTER VARYING(255) NOT NULL,   
    description TEXT    
);
CREATE TABLE amp (
    amp_id SERIAL NOT NULL,
    name CHARACTER VARYING(255) NOT NULL,
    description CHARACTER VARYING(255),    
    url CHARACTER VARYING(255)
);
CREATE TABLE ampref (
    ampref_id serial NOT NULL,
    amp_id integer NOT NULL,
    code character varying(255) NOT NULL,   
    description text    
);
CREATE TABLE amp (
    amp_id serial NOT NULL,
    name character varying(255) NOT NULL,
    description character varying(255),    
    url character varying(255)
);

...and I'm trying to figure out how to run this query: For each ampref.code, if its amp.name = 'LUNA' find its url. I would appreciate any help on this thanks.

Last edited by peter_budo; Feb 5th, 2008 at 5:59 pm. Reason: Please use [code] tags

•

Join Date: Dec 2004

Posts: 4,212

Reputation: peter_budo has much to be proud of

Solved Threads: 489

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: New to joins - Need help with query...

Feb 5th, 2008

Something like this

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT url FROM amp WHERE name='LUNA';
SELECT url FROM amp WHERE name='LUNA';

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group

•

Join Date: Oct 2005

Posts: 523

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

Re: New to joins - Need help with query...

Feb 5th, 2008

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
 
SELECT ar.url
FROM amp a
JOIN ampref ar on a.amp_id = ar.amp_id
WHERE a.name LIKE 'luna'
select ar.url
from amp a
join ampref ar on a.amp_id = ar.amp_id
where a.name like 'luna'