10 Replies - 380 Views - Last Post: 06 March 2018 - 01:28 AM

#1 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

How do you make this work?

Posted 05 March 2018 - 11:23 AM

Posted Image

How can you make this query work?
Basically, red color letters are empty in table test1 and they get those info from table hobby.

I am totally lost on this.
Is This A Good Question/Topic? 0
  • +

Replies To: How do you make this work?

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2853
  • View blog
  • Posts: 11,182
  • Joined: 03-December 12

Re: How do you make this work?

Posted 05 March 2018 - 11:38 AM

I am totally lost as well. But, I can't see the image either.

Try http://sqlfiddle.com/
Was This Post Helpful? 0
  • +
  • -

#3 jon.kiparsky   User is online

  • Beginner
  • member icon


Reputation: 11540
  • View blog
  • Posts: 19,626
  • Joined: 19-March 11

Re: How do you make this work?

Posted 05 March 2018 - 12:09 PM

Please use text instead of pictures
Was This Post Helpful? 0
  • +
  • -

#4 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: How do you make this work?

Posted 05 March 2018 - 12:16 PM

Table Test1

Username===Salary===hobby#====hobby1=====hobby2=======hobby3
Lee========30000====3====soccer====basketball====baseball
Chris======40000====2====videogame====reading====null
Tina=======50000====null====null====null====null


Table Hobby

username=====hobby_seq=====hobby
Lee============1===========soccer
Lee============2===========basketball
Lee============3===========baseball
Chris==========1===========videogame
Chris==========2===========reading
Tina==========null=========null

This post has been edited by shin777: 05 March 2018 - 12:22 PM

Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2853
  • View blog
  • Posts: 11,182
  • Joined: 03-December 12

Re: How do you make this work?

Posted 05 March 2018 - 12:32 PM

And your question is how? By using a Join clause.
Was This Post Helpful? 0
  • +
  • -

#6 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: How do you make this work?

Posted 05 March 2018 - 12:38 PM

If I just join them.. like

select a.username, a.salary, b.hobby_no, b.hobby1, b.hobby2, b.hobby3
from test1 a, hobby b
where a.username = b.username;

I don't get the result I want.. it only joins table and it won't list it to side.. I looked up all over and none of join method would work if I just join them. I am new and lost. help.
Was This Post Helpful? 0
  • +
  • -

#7 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2853
  • View blog
  • Posts: 11,182
  • Joined: 03-December 12

Re: How do you make this work?

Posted 05 March 2018 - 12:41 PM

Based on the results, it is doing a join and unions to get three bodies on the same line, but that is a very bad practice. If someone has 4, it ignores it
Was This Post Helpful? 0
  • +
  • -

#8 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: How do you make this work?

Posted 05 March 2018 - 12:48 PM

It is bad practice but that's what they want and I don't know how to do it. >.<
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2853
  • View blog
  • Posts: 11,182
  • Joined: 03-December 12

Re: How do you make this work?

Posted 05 March 2018 - 12:54 PM

Union
Join
Was This Post Helpful? 0
  • +
  • -

#10 shin777   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 78
  • Joined: 19-June 13

Re: How do you make this work?

Posted 05 March 2018 - 02:23 PM

union doesn't let me do. It would only let me join it if there's 1 of same row only on each table. since this example has 3 rows for Lee, it wouldn't do it. :(
Was This Post Helpful? 0
  • +
  • -

#11 Dormilich   User is offline

  • 痛覚残留
  • member icon

Reputation: 4228
  • View blog
  • Posts: 13,401
  • Joined: 08-June 10

Re: How do you make this work?

Posted 06 March 2018 - 01:28 AM

Also note that indexed column names are a certain sign for a failed database design.

-- using SQLite

CREATE TABE user (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  salary NUMERIC
)

CREATE TABLE hobby (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  user INTEGER NOT NULL REFERENCES user (id) ON DELETE CASCADE
)



Depending on which programming language you use, you might get a more or less easy to process result:
// using PHP

$sql = <<<SQL
  SELECT
    u.name,
    h.name
  FROM
    user u
  LEFT JOIN
    hobby h
    ON u.id = h.user
SQL;
$data = $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_COLUMN);


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1