Joining two tables

gvanaco

I would creating a table with this result

Countryname graadtech graadboul
Belgium France Sportclimbing France Sportclimbing
France France Sportclimbing Fontainbleau Boldering
Spain France Sportclimbing
....
USA United states Sportclimbing United States, Hueco, Boldering
...

When I use my query the second table is an exact copy of graadtech
Can someone help me

Thanks

$queryselection = "landen.land_id, landen.land_naam ,Cot.Cot_opm ,Cot.Cot_opm ";

$query = "SELECT $queryselection FROM landen LEFT JOIN Cot ON landen.graadtech = Cot.Cot_id or landen.graadboul = Cot.Cot_id";

Table structure for table Cot

Cot_id int(5) NOT NULL auto_increment,
cot_naam varchar
Cot_opm varchar
PRIMARY KEY (Cot_id)

(1, 'Cot_FR', 'France Sportclimbing');
(2, 'Cot_US', 'United states Sportclimbing');
(3, 'Cot_ZA', 'Zuid-afrika Sportclimbing');
(4, 'Cot_UIAA', 'Internationaal Sportclimbing');
(5, 'Cot_BH', 'United States, Hueco, Boldering');
(6, 'Cot_BF', 'Fontainbleau Boldering');
(7, 'Cot_BP', 'Great Brittan, Peak, Boldering');
(8, 'Cot_AU', 'Australia Sportclimbing');
(9, 'Cot_GB', 'Great Brittan Sportclimbing');

Table structure for table landen

land_id int(11) NOT NULL auto_increment,
land_naam varchar(255) NOT NULL default '',
graadtech tinyint(4) NOT NULL default '0',
graadboul tinyint(4) NOT NULL default '0',
PRIMARY KEY (land_id)

(1, 'Belgium', 1, 1);
(2, 'France', 1, 6);
(3, 'Spain', 1, 0);
(4, 'Madagascar', 1, 0);
(5, 'Greece', 1, 0);
(6, 'Germany', 4, 0);
(7, 'Italy', 1, 0);
(8, 'Sweden', 0, 0);
(9, 'Poland', 0, 0);
(10, 'Japan', 0, 0);
(11, 'USA', 2, 5);
(12, 'Switzerland', 1, 0);
(13, 'South Africa', 3, 0);
(14, 'Austria', 0, 0);
(15, 'Australia', 8, 0);
(16, 'Chile', 0, 0);

Erkilite

i like to use where instead of join.

$query = "SELECT $queryselection FROM landen WHERE landen.graadtech = Cot.Cot_id AND landen.graadboul = Cot.Cot_id";

gvanaco

it could be a solution, but I nead to show a list of all the country's in my database. You have to see when there is not a known system. In the original page you can than select and sent the right system to the webmaster.

But I have to show the system that every country use.

That means that it would be join or something.

Th thing is that I use 2 time's "Cot.Cot_opm ,Cot.Cot_opm" in the select thing, but the value is dfferent of that colum, becouse
landen.graadboul = Cot.Cot_id for the second time, but I do something wrong, and

Countryname graadtech graadboul
Belgium France Sportclimbing France Sportclimbing
France France Sportclimbing Fontainbleau Boldering
Spain France Sportclimbing unkown
....
USA United states SportclimbingUnited States, Hueco, Boldering
...

thanks in advances

Erkilite

landen.land_id as land_id, landen.land_naam as naam ,Cot.Cot_opm as opm1 ,Cot.Cot_opm as opm2

try using the AS keyword, it may be that you are asking to return the same column twice. you need to differentiate between the two.

gvanaco

Erkilite I think on the same way. I have tried your solution, I show you the result. It's not Wat I mean.

France see you to time's

It should be the second france in het first colum and the first france in the seond colum.
Now he is showing to row's and I will have only One row.

and for greece, italy, germany,.... should the second colum (boulder grades) be emty, but it's not

Thanks for your help

Erkilite

try using this (check the table and column names)

select t1.land_id as land_id, t1.land_naam as land_naam, t2.cot_naam as cot_naam, t2.Cot_opm as cot_opm, t3.cot_naam as cot_naam2, t3.Cot_opm as cot_opm2 from landen as t1, Cot as t2, Cot as t3 WHERE t1.graadtech = t2.Cot_id AND t1.graadboul = t3.Cot_id;

gvanaco

It's showing now only the country's that have the two systems.

I a country have one system it's not show'n.

I would have all the country's even when I not nowing the system.

ultraslacker

Originally posted by Erkilite
try using this (check the table and column names)

select t1.land_id as land_id, t1.land_naam as land_naam, t2.cot_naam as cot_naam, t2.Cot_opm as cot_opm, t3.cot_naam as cot_naam2, t3.Cot_opm as cot_opm2 from landen as t1, Cot as t2, Cot as t3 WHERE t1.graadtech = t2.Cot_id AND t1.graadboul = t3.Cot_id;

That almost has it but you have to allow unmatched rows, the nulls caused by a cot_id of 0. It is clearer when you use explicit JOIN syntax.