Niko Niko - 5 months ago 11
PHP Question

SQL query to combine tables (n:n)

I have these tables:




CATEGORIES
1. id
2. name







KIBE_TRAEGER
1. id
2. name







COUNTIES
1. id
2. region_id
3. country_id
4. county_code
5. county_de







REGIONS
1. id
2. country
3. region_de







CONTACTS:
1. id
2. firstname
3. surname
4. company
5. zipcode
6. city
7. region --> =regions.id -->
should show regions.region_de
8. county --> =counties.id --> should
show counties.county_de
9. tel1
10. tel2
11. mobile
12. fax
13. email1
14. email2
15. homepage
16. categories --> =categories.id **BUT** in table "contacts" it's written
with ;catnr; (e.g. an entry can have more than 1 entry:
";6;;7;;8;;16;") --> should show categories.name
17. notes
18. active
19. pernr







KIBE_CONTACTS
1. id
2. openinghours
3. costs
4. groups
5. handicapplaces
6. freeplaces
7. traeger --> =kibe_traeger.id same as categories ;traegerId; (e.g.";1;;5;") --> should show kibe_traeger.name




and i want to combine all of them to export it to csv.
Btw. i tried my poor queries so far directly in phpmyadmin with leftjoin and innerjoin but i'm relly poor in sql-stuff and wont learn it anymore :P ...

My code so far (edited according to Matt Cremeen's answers):

working:





SELECT
contacts.id, contacts.firstname, contacts.surname, contacts.company, contacts.zipcode, contacts.city, contacts.country, contacts.region,
contacts.county, contacts.tel1, contacts.tel2, contacts.mobile, contacts.fax, contacts.email1, contacts.email2, contacts.homepage,
contacts.active, contacts.pernr, kibe_contacts.id as contacts_id, kibe_contacts.openinghours, kibe_contacts.costs, kibe_contacts.groups,
kibe_contacts.handicapplaces, kibe_contacts.freeplaces, kibe_contacts.traeger, kibe_traeger.id as traeger_id, kibe_traeger.name,
counties.id as counties_id, counties.region_id as region_id, counties.country_id as country_id, counties.county_code, counties.county_de,
regions.id as regions_id, regions.country as regions_country, regions.region_de
FROM contacts
INNER JOIN
kibe_contacts
ON contacts.id = kibe_contacts.contact_id
INNER JOIN
regions
on contacts.region = regions.id
INNER JOIN
counties
on contacts.county = counties.id




not working:



INNER JOIN
kibe_traeger
on kibe_contacts.traeger = kibe_traeger.id
INNER JOIN
categories
on contacts.categories = categories.id;





getting a "Null-Result" i think i know why i get this error. because in the collumn "kibe_contacts.traeger" and "contacts.categories" is more then one value which are separated with ";" e.g. an entry can have more than 1 entry: ";6;;7;;8;;16;" but in categories.id there is always just a single id e.g. 4.

Is there any solution to query this out?

Last Edit: Solved the second problem by myself: i exported the list and did it with search & replace.

Thanks again to all the helpers!!!

Answer

Assuming that the id field corresponds to each table, you are going to want to join each table on that field. I won't write all of the code here, but it'll start like this

SELECT contacts.id, contacts.firstname, contacts.surname, ...
FROM contacts
INNER JOIN
kibe_contacts
ON contacts.id = kibe_contacts.id
INNER JOIN
regions
on contacts.region_id = regions.id
...