Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

selecting from one table when foreign key is in second table 1

Status
Not open for further replies.
Jul 28, 2005
358
FR
Hi,

I have a table where the primary key is cid. I want to display all records in that table only if cid is also in a second table as a foreign key but am not totally sure how to do it.

Can anyone help?

Thanks

Rich
 
what version of mysql are you using?

if 4.1 or higher you can use a subquery.


select * from table1 where cid IN (select cid from table 2)


cheers

QatQat

Life is what happens when you are making other plans.
 
Hi QatQat,

I have just been playing around using subqueries and couldn't for the life of me work out why they wouldn't work. Checked my local mysql version and it was older than 4.1. Thought about upgrading but my host also has a 4.0... version so I would rather keep the sam eas them.

So, need to work out a join (i think). Any tips?

Thanks,

Rich
 
Right, got it now

Code:
SELECT *
FROM country
INNER JOIN villas ON villas.cid = country.cid LIMIT 0 , 30

Rich
 
What difference would it make? Is it just a case of clarifying what is in the selet or is there a more critical reason why that might be needed?

Thanks,

Rich
 
you said you wanted "all records in that table"

when you use SELECT * in a join query, it returns all columns from both tables

r937.com | rudy.ca
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top