3

Table Name : x

id name id is primary key

1   xxxx
2   yyyy

Table name : y
sno primary key id foreign key class

1 1 first 
2 1  second

OUTPUT 

id 
xxxx first second

I do not want name to repeat for each row

SELECT x.name, y.class from x,y  WHERE x.id = y.id

This query output as xxxx first and xxxx second

Improve this question

3 Answers 3

Reset to default
4

Add in GROUP BY y.id then to avoid duplicate.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

:Tried it but it gives only one row,there are two rows with id.
If you want two rows from tablex, use LEFT JOIN to join the table and use group by.
SELECT x.name, y.class from x LEFT JOIN y ON x.id = y.id GROUP BY y.id
:I want only the name from first table and class from second table but name should not come twice,those column are just for example.
SELECT x.name, group_concat(y.class separator ', ' ) from x INNER JOIN y ON x.id = y.id GROUP BY y.id if i understand your reqd correctly.
|
1 
SELECT x.name, y.class 
FROM x
     LEFT JOIN (SELECT DISTINCT id, class FROM y) y 
        ON x.id = y.id;
Improve this answer

1 Comment

:How can you select y.class from x table,it won't exist.
0

try this:

set @yclass:='';
select  a.name,max(class) as class
from
(select @xname:=name as name,@yclass:=concat(@yclass,' ',y.class)  as class
from x join y
on x.id=y.id)a
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.