CODEWITHSUNDEEP
mysqljoin
jribeiro
jribeiro

Reputation: 3463

mySql Join two tables on non unique columns

Hi have the following tables:

table districts:

id   DD   text   
---|----|-----
1  | 01 | texas     
2  | 02 | ny     
5  | 03 | washington

table councils:

id   DD   CC   text
---|----|----|-----
1  | 01 | 01 | text1 
2  | 02 | 01 | text2
5  | 02 | 02 | text3
3  | 02 | 03 | text4
4  | 03 | 01 | text5
5  | 03 | 02 | text6
6  | 01 | 02 | text7

table person:

id   name    DD   CC
---|-------|----|----
1  | john  | 02 | 03 
2  | mike  | 03 | 02
3  | julia | 01 | 02

I want to make a query so I get the following result:

result:

 name     District    Council
-------|------------|-------
 john  | ny         | text4 
 mike  | washington | text6
 julia | texas      | text7

So far I have the following query:

select p.name,d.text as district,c.text as council
    from  person p 
        inner join districts d on p.DD=d.DD
        inner join councils c on p.DD=c.DD and p.CC=c.CC
where 1;

I think that the logic should be correct but somehow I'm getting an error... Can anyone help understand this or point me in the right direction??

If I do this it returns results but not the expected of course:

select p.name,d.text as district,c.text as council
    from  person p 
        inner join districts d on p.DD=d.DD
        inner join councils c on p.CC=c.id
where 1;

Thanks

EDIT: It was a collation problem... My advice if you find this is to not use query browser 'cause errors are not very verbose... There might be an option in it but I dont't know!

Thank you all

Upvotes: 2

Views: 4438

Answers (2)

DRapp
DRapp

Reputation: 48139

Your first query SHOULD have worked... only remove the WHERE 1... you don't need the where clause at all if you are returning all records from the "person" table. Your table alias use is correct, and the first query properly joins to the councils on both district and council, otherwise your second sample query would return multiple rows for a person since there are duplicate "council" codes only.

The "WHERE 1" is failing... as 1 is not a logical expression... If used, you would have to do "WHERE 1=1"

Upvotes: 0

josh.trow
josh.trow

Reputation: 4901

I had no idea of your schema, but here's what I did (and it works fine!)

CREATE TABLE `districts` (
    `id` INT(10) unsigned NOT NULL,
    `DD` CHAR(20) NOT NULL,
    `text` CHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `councils` (
    `id` INT(10) unsigned NOT NULL,
    `DD` CHAR(20) NOT NULL,
    `CC` CHAR(20) NOT NULL,
    `text` CHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `person` (
    `id` INT(10) unsigned NOT NULL,
    `DD` CHAR(20) NOT NULL,
    `CC` CHAR(20) NOT NULL,
    `name` CHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO districts (`id`, `DD`, `text`) VALUES ('1','01','texas');   
INSERT INTO districts (`id`, `DD`, `text`) VALUES ('2','02','ny');   
INSERT INTO districts (`id`, `DD`, `text`) VALUES ('5','03','washington');

INSERT INTO councils (`id`, `DD`, `CC`, `text`) VALUES ('1','01','01','text1');
INSERT INTO councils (`id`, `DD`, `CC`, `text`) VALUES ('2','02','01','text2');
INSERT INTO councils (`id`, `DD`, `CC`, `text`) VALUES ('3','02','03','text4');
INSERT INTO councils (`id`, `DD`, `CC`, `text`) VALUES ('4','03','01','text5');
INSERT INTO councils (`id`, `DD`, `CC`, `text`) VALUES ('5','03','02','text6');
INSERT INTO councils (`id`, `DD`, `CC`, `text`) VALUES ('6','01','02','text7');

INSERT INTO person (`id`, `name`, `DD`, `CC`) VALUES ('1','john','02','03');
INSERT INTO person (`id`, `name`, `DD`, `CC`) VALUES ('2','mike','03','02');
INSERT INTO person (`id`, `name`, `DD`, `CC`) VALUES ('3','julia','01','02');

SELECT p.name, d.text AS district, c.text AS council
FROM person p
INNER JOIN districts d ON p.DD = d.DD
INNER JOIN councils c ON p.DD = c.DD
AND p.CC = c.CC
WHERE 1

Upvotes: 3

Related Questions