Niko Niko - 1 year ago 63
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!!!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source

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
...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download