1

I have these 2 tables:

master (major, SID, fee)

Biology, 13578, $500
Physics, 12460, $400

student (major, SID, fname, lname, startDate, lastDate)

Science, 73589, Steven, Archer, 2010-09-02, null
Biology, 13578, Stacy, Diaz, 2009-09-02, null
Sociology, 21749, Gavin, Wall, 2011-01-05, null
Physics, 12460, Pat, Dunn, 2012-09-02, null

I have to make a query where it shows the information of all master students grouped by their major and their fee.

I tried it this way:

select * from from master, student 
where master.SID=student.SID AND master.major=student.major 
group by student.SID;

But this will make SID and major info to show twice:

Biology, 13578, Stacy, Diaz, 2009-09-02, null, Biology, 13578
Physics, 12460, Pat, Dunn, 2012-09-02, null, Physics, 12460

so now I have this:

select distinct student.sid, student.eid, fname, lname, startDate, lastDate, fee
from from master, student 
where master.SID=student.SID AND master.major=student.major 
group by student.SID;

which will give:

Biology, 13578, Stacy, Diaz, 2009-09-02, null
Physics, 12460, Pat, Dunn, 2012-09-02, null

But I'm wondering if there's a way to not specify which SID or major to select. I want it to select all but it deletes duplicates.

Sorry if this seems easy, I'm still a beginner in mysql

user977151
  • 495
  • 5
  • 9
  • 18

2 Answers2

1

If I understand your question, you want to select *, but have redundant columns removed.

This isn't possible, but you can take a shortcut by returning all the columns from student, and then add the additional column from master.

select s.*, m.fee
from
  master m
  join student s 
    on s.sid = m.sid
    and s.major = m.major

Notice that I made a few other changes to your query:

  1. Replaced the implicit join (student, master) with an explicit join (master join student).
  2. Removed the group by and distinct... I don't believe that they are necessary for what you're trying to do.
  3. Added table aliases (m and s).

One final suggestion... it is widely considered bad practice to use select * for a variety of reasons... so even though there's a shortcut for what you're trying to do, it would be best to list out all of your columns for other reasons.

Community
  • 1
  • 1
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
0

No, you have to specify which table to pull from if there are duplicate column names, or you'll get an error. You can specify both, of course: SELECT master.SID, student.SID.

Melanie
  • 3,021
  • 6
  • 38
  • 56