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
USERIDNAMEPHONE
1 Robert 1232
2 Sonia 4564
3 Mike 8798
TABLE USERINFO
USERIDCOLNUMINFO
110Tennis
120The Hulk
130Pizza
210Football
220Titanic
230Fish
310Tennis
320Hunger Games
330Hamburger
RESULT
USERIDNAMEPHONEFav.Sport colnum 10Fav.Food colnum 30
1Robert 1232 Tennis Pizza
2Sonia 4564 Football Fish
3Mike 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: 3163 More actions · Answer 1

Please post the link to Screenshot again.

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

kristofmegatof SSC Rookie Points: 25 More actions · Answer 2

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: 11649 More actions · Answer 3

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: 286590 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: 286590 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