Combining Tables

Question

Post reply

Combining Tables

kristofmegatof

SSC Rookie

Points: 25
More actions
April 14, 2015 at 6:47 am

#296886

Hi all,

I'm new to the forum and SQL and hoping that someone could help me out.

Goal combining two tables with some restrictions.

TABLE USER

USERID NAME PHONE

1 Robert 1232

2 Sonia 4564

3 Mike 8798

TABLE USERINFO

USERID COLNUM INFO

1 10 Tennis

1 20 The Hulk

1 30 Pizza

2 10 Football

2 20 Titanic

2 30 Fish

3 10 Tennis

3 20 Hunger Games

3 30 Hamburger

RESULT

USERID NAME PHONE Fav.Sport colnum 10 Fav.Food colnum 30

1 Robert 1232 Tennis Pizza

2 Sonia 4564 Football Fish

3 Mike 8798 Tennis Hamburger

The idea:

- Mixed tables User + USERINFO = result

- info of one person on one row

- no intrest in colnum 20 = movie

Solution or tips like what should I look for on the web to find the solution are more then welcome.

thanks for help, eager to learn 🙂 grtz

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

manub22 Hall of Fame Points: 3133 More actions · Answer 1

manub22

Hall of Fame

Points: 3133

April 14, 2015 at 6:55 am

#1790779

Please post the link to Screenshot again.

~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

kristofmegatof SSC Rookie Points: 25 More actions · Answer 2

kristofmegatof

SSC Rookie

Points: 25

April 14, 2015 at 6:59 am

#1790780

sorry forum didn't accept the imagehost site I was using, so messed up a bit... I tried to adapt my post with info in it. Instead of the screen.

Y.B. SSChampion Points: 11572 More actions · Answer 3

Y.B.

SSChampion

Points: 11572

April 14, 2015 at 7:35 am

#1790790

Try this:

SELECT



u.USERID,

u.NAME,

u.PHONE,

ui1.INFO AS Sport,

ui2.INFO AS Food

FROM

[User] u

JOIN UserInfo ui1 ON u.USERID = ui1.USERID AND ui1.COLUMN = 10

JOIN UserInfo ui2 ON u.USERID = ui2.USERID AND ui2.COLUMN = 30

SELECT quote FROM brain WHERE original = 1
0 rows returned

Sean Lange SSC Guru Points: 286562 More actions · Answer 4

yb751 (4/14/2015)

Try this:

SELECT
u.USERID, u.NAME, u.PHONE, ui1.INFO AS Sport, ui2.INFO AS Food FROM [User] u JOIN UserInfo ui1 ON u.USERID = ui1.USERID AND ui1.COLUMN = 10
JOIN UserInfo ui2 ON u.USERID = ui2.USERID AND ui2.COLUMN = 30

This works but isn't very efficient as you have to read from UserInfo repeatedly.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

Here is another way of handling this type of thing. This is using a cross tab.

Please notice how I created tables and sample data to work with. This is something you should do on future posts but I did it for you this time since you are new around here so you can see how this should be done.



if OBJECT_ID('tempdb..#User') is not null

	drop table #User

create table #User

(

	USERID int,

	NAME varchar(20),

	PHONE int

)

insert #User

select 1, 'Robert', 1232 union all

select 2, 'Sonia', 4564 union all

select 3, 'Mike', 8798

if OBJECT_ID('tempdb..#UserInfo') is not null

	drop table #UserInfo

create table #UserInfo

(

	USERID int

	, COLNUM int

	, INFO varchar(50)

)

insert #UserInfo

select 1, 10, 'Tennis' union all

select 1, 20, 'The Hulk' union all

select 1, 30, 'Pizza' union all

select 2, 10, 'Football' union all

select 2, 20, 'Titanic' union all

select 2, 30, 'Fish' union all

select 3, 10, 'Tennis' union all

select 3, 20, 'Hunger Games' union all

select 3, 30, 'Hamburger'

select u.USERID

	, u.Name

	, u.PHONE

	, MAX(case when ui.COLNUM = 10 then INFO end) as ColNum10

	, MAX(case when ui.COLNUM = 30 then INFO end) as ColNum30

from #User u

join #UserInfo ui on ui.USERID = u.USERID

group by u.USERID

	, u.Name

	, u.PHONE

You can read more about cross tabs by following the links in my signature about converting rows to columns.