CODEWITHSUNDEEP
javamysql
hawkeye
hawkeye

Reputation: 45

how to get value from two table in mysql with from one table distinct and from other table multiple value?

i have two table in database like:

table_one with fields like: class , day_of_week and id(pk);
table_two with fields like: id(fk) , subjects

and what i tried is:

select distinct day_of_week ,subjects from table_one as tbl_one ,table_two as tbl_two where tbl_one.id =tbl_two.id and class_name='One'[?];

and my result comes out for my report like:
day of week: ** ** subjects
Sunday A
Sunday B
Monday A
Monday B

but it should come like this:
day of week subjects
Sunday A
B
Monday A
B

Upvotes: 0

Views: 58

Answers (1)

Thrash Bean
Thrash Bean

Reputation: 658

Sir? WOW, thank you.

I'm not sure I understand very well what you pretend, but as I see your result is correct. Your edited code is impossible since you could get 

Sunday, A, B
Monday, A, B

but not 

Sunday, A
      , B

What you need is agregate function. InMySQL this is GROUP_CONCAT. In your case the query may look like this:

SELECT day_of_week, GROUP_CONCAT(subjects) AS subjects 
FROM ...
WHERE ...
GROUP_BY subjects

Also take a look at User defined functions.

I hope I understood your question and this answer helps you. If not, please correct me and ask again.

Upvotes: 0

Related Questions