0

I have three tables which contains parent,child,mark

In parent table:

Id int(primarykey),
Firstname varchar(50),
Email varchar(50)

In the Child Table:

Mid int(primarykey),
Mark1 int,
Mark2 int,
Id int(foreignkey)

In the Mark Table:

Uid int(primarykey),
Mark3 int,
Id int(foreignkey)

By using this query,I'm getting result.

select Id,
MAX(Firstname)Firstname,
MAX(Mark1) Mark1,
MAX(Mark2) Mark2,
MAX(Mark3) Mark3
from
(
select Id,Firstname,Null as Mark1,Null as Mark2,Null as Mark3 from Parent
union
select Id,Null as Firstname,Mark1,Mark2,Null as Mark3 from Child 
union
select Id,Null as Firstname,Null as Mark1,Null as Mark2,Mark3 from Mark
) t
group by Id

Here in this query, I have used group by function for Id but I wanna use group by function for rest of the column names. So is that possible to do without using join query?

Thanks in advance.

Pete Rennard-Cumming
  • 1,588
  • 10
  • 19
Sridhar
  • 149
  • 2
  • 15
  • Edit your question and provide sample data and desired result. Your explanation is not clear. – Gordon Linoff Aug 18 '17 at 10:48
  • You mentioned union and group by, but I think you just need a series of joins. Problem: I have no idea how to join your tables. – Tim Biegeleisen Aug 18 '17 at 10:51
  • @Sridhar: Joins are your friends. :) And this example seems to be screaming happily for them. Here is a great resource of the many different ways to join things: https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join – abraxascarab Aug 18 '17 at 13:44
  • @abraxascarab: Can't we do without using join query? – Sridhar Aug 18 '17 at 13:48
  • @Sridhar: It depends on how your data is set up. If you have multidimensional data, it's not going to work well. You would only be grabbing data that happened to be the Max. And that would leave you blind to other data you have out there. With proper use of joins, you can grab all your data in a meaningful way. But... if you can assure that there is only one record in each table for each `Id`, you could possibly do it without joins. However, the way your tables are designed, they suggest you probably have multidimensional data (or one-to-many relationships). – abraxascarab Aug 18 '17 at 14:46

0 Answers0