Inner Join: One Resultset for one Customer...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Join: One Resultset for one Customer...

Author

Topic

mastaer
Starting Member

3 Posts

Posted - 2014-11-14 : 04:13:23

Hello Community,

I have a database (MSSQL). To demonstrate the problem let me show a fictive Tablestructure. I don't want to discuss about how to save the data differntly, because the structure is fix and I can't change it.

I have three tables:

The result I want to get:

To get this result I would do a sql query with a lot of joins like that:


SELECT firstname, lastname, email.value, phone.value
FROM Customer
INNER JOIN
     (
     SELECT Customer_Properties.id, Customer_Properties.value
     FROM Customer_Properties
     INNER JOIN Properties
       ON (Customer_Properties.Id_Properties = Properties.id AND
           Properties.nameofproperty LIKE 'email')
     ) email
     ON (email.id = Customer.id)
INNER JOIN
     (
     SELECT Customer_Properties.id, Customer_Properties.value
     FROM Customer_Properties
     INNER JOIN Properties
       ON (Customer_Properties.Id_Properties = Properties.id AND
           Properties.nameofproperty LIKE 'phone')
     ) phone
     ON (phone.id = Customer.id)

I don't think that this is really performant and the SQL-Queries get very complicated. Give it a other methode for that?

I hope you can help me with that. Thank you so much

mastaer

P. S.: Please notice again, that is just an example, I can't change the data structure.

P. S.2: Sorry for the subject name, I don't know to call it

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-14 : 05:49:59

If you know the properties you want you can just PIVOT cusotmer_properties and then join to customers.

If you do not know the properties then you will have to do a dynamic pivot. (Google.)

If you post test data in consumable format, someone might even give you some outline code.

eg CREATE TABLE #Customers (Id ...)
INSERT INTO #Customers ...
etc

mastaer
Starting Member

3 Posts

Posted - 2014-11-14 : 07:05:24

Hey IFor,

thank you for your help. The Keyword Pivot was that was I searched


CREATE TABLE #Customer (
    id INT NOT NULL,
	LastName varchar(255),
	FirstName varchar(255),
    PRIMARY KEY (id)
)
CREATE TABLE #Properties (
    id INT NOT NULL,
	nameofproperty varchar(255),
    PRIMARY KEY (id)
)
CREATE TABLE #Customer_Properties (
    id INT NOT NULL,
	StringValue varchar(255),
	IntValue INT,
	id_customer INT,
	FOREIGN KEY (id_customer) 
        REFERENCES #Customer(id)
        ON DELETE CASCADE,
	id_properties INT,
	FOREIGN KEY (id_properties) 
        REFERENCES #Properties(id)
        ON DELETE CASCADE,
    PRIMARY KEY (id)
)

INSERT INTO #Customer (id, LastName, FirstName)
SELECT id, LastName, FirstName
FROM (
		VALUES
			(0,'Klaus',     'Hagebuttentee'  )
			,(1,'Peter', 'Sandmühle'   )
			,(2,'Joachim',    'Allesfresser')
      ) val (id, LastName, FirstName)

INSERT INTO #Properties (id, nameofproperty)
SELECT id, nameofproperty
FROM (
		VALUES
			(0,'email'  )
			,(1,'phone'   )
      ) val (id, nameofproperty)


INSERT INTO #Customer_Properties (id, StringValue, IntValue,id_customer,id_properties)
SELECT id, StringValue, IntValue,id_customer,id_properties
FROM (
		VALUES
			(0,'klaus.hagebuttentee@irgendwas.com',0,0,0 )
			,(1,'',5123413,0,1 )
			,(2,'peter.sandmühle@irgendwas.com',0,1,0 )
			,(3,'joachim.allesfresser@irgendwas.com',0,2,0 )
			,(4,'',51235151,2,1 )
      ) val (id, StringValue, IntValue,id_customer,id_properties)


SELECT * FROM #Properties

mastaer
Starting Member

3 Posts

Posted - 2014-11-14 : 07:41:13

The Tables are a bit different. But how ever i did something like that:


SELECT pvt.id_customer, MAX(help.[0]), MAX(pvt.[1])
FROM #Customer_Properties cp
PIVOT
(
	MAX(IntValue)
	FOR id_properties IN ([1]) -- all INT values
) as pvt
INNER JOIN
(
	SELECT *
	FROM #Customer_Properties cp
	PIVOT
	(
		MAX(StringValue)
		FOR id_properties IN ([0]) -- all STRING values
	) as pvt
)help
on (pvt.id = help.id)
GROUP BY pvt.id_customer

It works fine for me. But when somebody have a better idea, let me know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources