Page 1 of 1

MYSQL Query for selecting only one row from 2 tables

Posted: Wed Oct 08, 2008 9:02 am
by panprasath
Hi,

I have two tables.One in which I have unique values and the other one where there are multiple images(values) corresponding to each element in the first table.
Now , I need to write a query in such a way that it takes each element in the 1st table and gets only the 1st corresponding value for that particular element leaving out the others.
so that effectively I will have one element pointing to only one value.

For example,If I have the following Table structure

Table 1

country_id name country
1 hari India
2 John Russia

Table 2

Image_id Country_id Image
1 1 Image1
2 1 Image2
3 2 Image3
4 2 Image4
5 1 Image5

I need to write a query which will give me the following results

country_id name country image
1 hari India Image1
2 John Russia Image3

I need some help on this.
Thanks.

Re: MYSQL Query for selecting only one row from 2 tables

Posted: Wed Oct 08, 2008 9:18 am
by VladSun
Please, lets take a look your query, so we can fix it ;)

Re: MYSQL Query for selecting only one row from 2 tables

Posted: Wed Oct 08, 2008 1:11 pm
by califdon
There is an underlying risk in doing exactly what you said you want to do: "I need to write a query in such a way that it takes each element in the 1st table and gets only the 1st corresponding value for that particular element leaving out the others".

The risk is that relational databases never guarantee any particular order of the rows of a table. While it may appear that "the first" value that occurs is the earliest, or whatever, such operations as record deletions or database backups may rearrange the rows in a table without notice. You should never rely on the order of rows in a database table. Always use an ORDER BY clause and then you can safely use a LIMIT clause to limit the number of rows returned.