Query

thoand

Hi,
I have two tables: countries and companies

On my page I want to print the companies in this format:

Finland

Finland Company A
Finland Company B
Finland Company C
Finland Company D

Latvia

Latvia Company A
Latvia Company B
Latvia Company C

Spain

Spainsh Company A
Spainsh Company B
Spainsh Company C

and so on,

can anyone please help me with the query please

thanks in advance,
Thomas A

eoghain

Well I have to assume the structure of your database but if it looks like this:

countries
---------
country_id int not null
name varchar(50)

companies
---------
company_id int not null
name varchar(50)
country_id int

Then you could easliy write a query that looks like this:

SELECT cou.name, com.name
  FROM companies com, countries cou
 WHERE com.country_id = cou.country_id
 ORDER BY cou.name

That would give you a list of companies with there countries ordered by the country. Now all you need to do is write the code to display it the way you want.

thoand

Well the result is getting something like this:

Denmark
Mr-Andersen

Finland
BB

Finland
Nissen

Norway
Andersen

Norway
Vin og øl

Sweeden
Spaceinvaders

Sweeden
Hei & hå

I want the companies to get under the country and the country only get displayed one time.

Norway

NOcomp1
NOcomp2
NOcomp3

Sweeden

SEcomp1
SEcomp2
SEcomp3
SEcomp4

Denmark

DKcomp1
DKcomp2
DKcomp3
DKcomp4

See?

eoghain

Yes I see what you want. Did you see this part of my original message?

Now all you need to do is write the code to display it the way you want.

You need to write some PHP code to get the display correct. Unless you want to run it as 2 queries with one nested in the other. Then you can just query for all of your countries and after you display the first one then you can query the companies that fall into that country and print them. Once your companies query has finished printing you can move on to the next line in your countries query.

You can't get the output you want from a single SQL query, it isn't possible. You will have to write some code to display the data the way you want it. My first response assumed that you'd loop over the data and pull out the country and display the companies while you had the same company name (it's a standard pattern for displaying grouped data from a database). Then display the country line again only when it changes (that's what the order by was for, to keep all the companies in the same country together).

thoand

I got it now, thanks alot.

Thomas