Discussion between Mr. Bear and Marshall Mathers - 2014-11-20

last day (14 days later) »

1:02 PM

0

Q: Companies and addresses MySQL

I've got a database with 2 tables. companies addresses These are my tables. Adresses adress_id adress_adress adress_adressnumber adress_zipcode adress_city adress_postbus adress_country adress_building adress_unit Companies company_id company_name company_adress_id company_postadress_id...

Marshall Mathers

If you are looking to get both Addresses i.e. Company PostAddress and Company Address, you could do it by doing union or simple OR in the join using union

SELECT  	* FROM 	Companies AS c JOIN Adresses AS a ON (c.company_adress_id = a.adress_id)   union  SELECT 	* FROM 	Companies AS c JOIN Adresses AS a ON   (c.company_postadress_id = a.adress_id)

using simple OR in JOIN

SELECT 	* FROM 	Companies AS c JOIN Adresses AS a ON (c.company_adress_id = a.adress_id) or (c.company_postadress_id = a.adress_id)

Thanks @MarshallMathers but it now says that i've got an error in my MySQL query. SELECT * FROM Companies AS c JOIN Adresses AS a ON (c.company_adress_id = a.adress_id) union SELECT * FROM Companies AS c JOIN Adresses AS a ON (c.company_postadress_id = a.adress_id) using simple OR in JOIN SELECT * FROM Companies AS c JOIN Adresses AS a ON (c.company_adress_id = a.adress_id) or (c.company_postadress_id = a.adress_id)

Marshall Mathers

could you please do the table structure dump on those two tables? i did test it using the CREATE TABLE Adresses` ( adress_id int(11) NOT NULL AUTO_INCREMENT, memeh varchar(255) DEFAULT NULL, PRIMARY KEY (adress_id) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;` and CREATE TABLE Companies` ( company_id int(10) unsigned NOT NULL AUTO_INCREMENT, company_adress_id int(11) DEFAULT NULL, company_postadress_id int(11) unsigned DEFAULT NULL, PRIMARY KEY (company_id) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; ` it worked with these structure

Here's a dump for these tables. pastebin.com/qq68nWNK

Marshall Mathers

it was to do with case of the table names, changing the case sorts the syntax problem.

SELECT 	* FROM 	companies AS c JOIN adresses AS a ON ( 	c.company_adress_id = a.adress_id ) UNION 	SELECT 		* 	FROM 		companies AS c 	JOIN adresses AS a ON ( 		c.company_postadress_id = a.adress_id 	)

OR

SELECT 	* FROM 	companies AS c JOIN adresses AS a ON (c.company_adress_id = a.adress_id) or (c.company_postadress_id = a.adress_id)

1:02 PM

I've got a result now yes. But its not how i want i yet. Because i want the 2 results combined in 1 result. pastebin.com/cxyffrfF

I've moved it to a chat.

Marshall Mathers

Hello Mr. Bear, do you have any dataset to test for ?

Yes. Here it is.
http://pastebin.com/cxyffrfF

Want i want is a combined result with postal and normal

Maby, i dont know, its possible to add a postal_..

Im not a fully pro with mysql and its results

Marshall Mathers

if you not looking for really efficient query . there is an easy way.

SELECT
company_id,
company_name,
(
SELECT
adress_adress
FROM
adresses a1
WHERE
a1.adress_id = c.company_adress_id
) AS CompanyAddress,
(
SELECT
adress_adress
FROM
adresses a2
WHERE
a2.adress_id = c.company_postadress_id
) AS CompanyPostalAddress

FROM
companies c

that does what you need.

if you think thats correct i will add that as an answer

1:18 PM

Okay. It does work but i miss alot of info. Like street and stuff

Let me test a little

When i try to add more fields it doesn't work

But maby im doing it wrong?

Marshall Mathers

it wont ..

you might want to concat the fields

I have a company and and this company can have 2 diffrent addresses

So iput it all in a diffrent table

the addresses table

And now i want to fetch the company + the 2 addresses

Maby you have a better solution for me

Marshall Mathers

SELECT
company_id,
company_name,
(
SELECT
concat_ws(', ',adress_adress, adress_adressnumber, adress_city)
FROM
adresses a1
WHERE
a1.adress_id = c.company_adress_id
) AS CompanyAddress,
(
SELECT
concat_ws(', ',adress_adress, adress_adressnumber, adress_city)
FROM
adresses a2
WHERE
a2.adress_id = c.company_postadress_id
) AS CompanyPostalAddress

FROM
companies c

It does work yes

But its not the ideal situation that i like haha

Is there another way of storing everything in the database?

And not the way i do it

Marshall Mathers

yes sure , hold on i ll write you a table structure that will support it ..

1:30 PM

Okay awesome. Because i think i have a wrong approach.

And how long are you doing MySQL? Because you're like a pro

Marshall Mathers

CREATE TABLE `companies` (
`company_id` int(11) NOT NULL AUTO_INCREMENT,
`company_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL
PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `adresses` (
`adress_id` int(11) NOT NULL AUTO_INCREMENT,
`adress_adress` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`adress_adressnumber` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`adress_zipcode` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`adress_city` varchar(30) COLLATE utf8_unicode_ci NOT NULL,

check that out ..

i have rmeoved the keys

but you can add yourself on need

select * from companies c
INNER JOIN adresses a1 on c.company_id = a1.company_id and a1.addresss_role = 1
INNER JOIN adresses a2 on c.company_id = a2.company_id and a2.addresss_role = 2

/*
Navicat MySQL Data Transfer

Source Server : 59 _ Root
Source Server Version : 50620
Source Host : localhost:3306
Source Database : variant

Target Server Type : MYSQL
Target Server Version : 50620
File Encoding : 65001

Date: 2014-11-20 13:36:39
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for adresses
-- ----------------------------
DROP TABLE IF EXISTS `adresses`;
CREATE TABLE `adresses` (
`adress_id` int(11) NOT NULL AUTO_INCREMENT,

Okay so only remove the foreign_keys?

Marshall Mathers

no no .... change them to suit you .. like adresses.company_id could be FK to companies

Okay i've run the whole sql

wait and let me check it

Can you give me the full sql?

I threw away my DB because it complained about all things

Or is it the last one

Okay it works now. But... it just says: error and doesn't give me any records back

I think its about the AND and a1.addresss_role = 1 part

Where do you get the 1 and 2 from? Because i don't know the id's yet

I do have your database setup now

Marshall Mathers

2:04 PM

if you look on the the table column

`addresss_role` tinyint(4) DEFAULT NULL COMMENT 'Role 1 for company may be and 2 for comapny postal, feel free',

Ah i see it yes. Still it doesn't work. But ill try and make it work.

For now thanks for the help!

Marshall Mathers

:)

This is my full database.
I hope its correct now

pastebin.com/fKmhgfja

Marshall Mathers

if you have used the last sql i sent , this should give you what you wanted in the beginning..

select * from companies c
INNER JOIN adresses a1 on c.company_id = a1.company_id and a1.addresss_role = 1
INNER JOIN adresses a2 on c.company_id = a2.company_id and a2.addresss_role = 2

Strange because it doen't :(

Marshall Mathers

2:20 PM

haha hold on .. i ran the same query it worked for me . i m checking your paste bin

i see now why it was not working for you .. its because the adresses table did not have any company id

use this instead :-

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for adresses
-- ----------------------------
DROP TABLE IF EXISTS `adresses`;
CREATE TABLE `adresses` (
`adress_id` int(11) NOT NULL,
`adress_adress` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`adress_adressnumber` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`adress_zipcode` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`adress_city` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`adress_postbus` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,

Okay i get a result now

[0] => stdClass Object
(
[company_id] => 1
[company_name] => LEMON Internetbureau
[company_adress_id] => 1
[company_postadress_id] => 3
[adress_id] => 3
[adress_adress] => Busken Huetpad
[adress_adressnumber] => 2
[adress_zipcode] => 2624 XC
[adress_city] => Delft
[adress_postbus] =>
[adress_country] => Nederland
[adress_building] =>
[adress_unit] =>
[addresss_role] => 2
)

This is my result

Marshall Mathers

you not using this sql ?

select * from companies c
INNER JOIN adresses a1 on c.company_id = a1.company_id and a1.addresss_role = 1
INNER JOIN adresses a2 on c.company_id = a2.company_id and a2.addresss_role = 2 ?

Yes i need to join 1 more i think dont i?

Marshall Mathers

yes you do ..with a1.addresss_role = 1 means 1 type of address and a2.addresss_role = 2 means another type of address

Kay

Ill try to make the last join myself

3:09 PM

I dont know whats going wrong but he overwrites the data

QUERY: select * from companies c
INNER JOIN adresses a1 on c.company_id = a1.company_id and a1.addresss_role = 1
INNER JOIN adresses a2 on c.company_id = a2.company_id and a2.addresss_role = 2

RESULT:
[0] => stdClass Object
(
[company_id] => 1
[company_name] => LEMON Internetbureau
[company_adress_id] => 1
[company_postadress_id] => 3
[adress_id] => 3
[adress_adress] => Busken Huetpad
[adress_adressnumber] => 2
[adress_zipcode] => 2624 XC
[adress_city] => Delft
[adress_postbus] =>
[adress_country] => Nederland

QUERY: select * from companies c
INNER JOIN adresses a1 on c.company_id = a1.company_id and a1.addresss_role = 1

RESULT:
[0] => stdClass Object
(
[company_id] => 1
[company_name] => LEMON Internetbureau
[company_adress_id] => 1
[company_postadress_id] => 3
[adress_id] => 1
[adress_adress] => Prof. Henketstraat
[adress_adressnumber] => 67
[adress_zipcode] => 2628 KL
[adress_city] => Delft
[adress_postbus] =>
[adress_country] => Nederland
[adress_building] =>
[adress_unit] =>
[addresss_role] => 1

I really don't know what to do

Marshall Mathers

Mate, send me your whole DB dump . i will add the select query at the end

Okay

pastebin.com/A8X3WciU

Thats my complete DB

Marshall Mathers

with the same database and same query i m getting the complete resultset, i think its your way of extracting data from php or to simply use specific column names like below:-

SELECT
c.*,
a1.adress_adress AS company_adress_adress,
a2.adress_adress AS postal_adress_adress,

a1.adress_adressnumber as company_adress_adressnumber,
a2.adress_adressnumber as postal_adress_adressnumber,

a1.adress_adressnumber as company_adress_adressnumber,
a2.adress_adressnumber as postal_adress_adressnumber

FROM

Strange

I use mysqli::fetch_object

Marshall Mathers

because the 2 column names would be same thats why i think ...just have named column in the select instead of select *****

select *

3:25 PM

In phpmyadmin i get the good values

But in PHP i dont get the correct results

Marshall Mathers

yes .. i understand :) ... write the SQL for all fields in both tables as

a1.adress_adress AS company_adress_adress,
a2.adress_adress AS postal_adress_adress,

a1.adress_adressnumber as company_adress_adressnumber,
a2.adress_adressnumber as postal_adress_adressnumber,

a1.adress_adressnumber as company_adress_adressnumber,
a2.adress_adressnumber as postal_adress_adressnumber

Okay now it works i think

But this way, is a little inefficient isn't it?

Marshall Mathers

no, its the best way ..

if you think of performance add index :-

Okay. Well extremely thank

* thanks

I wouldn't have get it without you. But i do understand whats going on with all this

Marshall Mathers

ALTER TABLE `adresses`
ADD INDEX `ind_company_role` (`company_id`, `addresss_role`) USING BTREE

that will speeeeeeeed your query

if you understand whats going then thats good :)

3:32 PM

Okay but that does that last one mean?

Because i dont know that command

The query broke my database now... it doesn't show anything anymore in the rsults now

Nah sorry it was my fault

It works. I forgot a comma...

last day (14 days later) »

Transcript for

Nov '1420

Discussion between Mr. Bear and Marsh…

Imported from a comment discussion on dba.stackexchange.com/qu...

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile