Search code examples
mysqlsqlworkbench

SQL Union with differing columns

Hi I am not sure of the best way to ask this, but I have successfully run two SQL queries that separately retrieve the results I am searching for. However, i'd like to append/ concatenate basically the two results together but as i'm fairly new to SQL i'm not sure of what method to use. I've tried Union but that does not work as both tables require the same amount of columns. Also tried left join but that gives me a general syntax error (may be on my behalf, again i'm new to this).

1st QUERY

SELECT prac.healthPracID, prac.firstName, prac.surname
FROM healthpractitioners as prac

2nd QUERY

select count(treatmentrecords.nickname) as patients
from treatmentrecords
group by treatmentrecords.healthpracID;

Alternatively, could someone maybe help me rewrite these statements to achieve the same result in one query. I had tried something like that before and had done the following (but it did not produce the correct output - seemed to have the same number of patients and all the first name and surnames were just the first one from the health practitioner table, but repeated):

SELECT prac.healthPracID, prac.firstName, prac.surname, 
count(treatmentrecords.nickname) as patients
FROM healthpractitioners as prac, treatmentrecords
group by treatmentrecords.healthpracID;

Thanks in advance, sorry if this has already been posted before, I'm quite confused with this and wasn't sure how best to search for it.

PS Im running MySQL Workbench on Windows if that makes any difference. Sam.

Solution

  • Your second attempt is on the right track, but it is missing a join condition, and also you should be grouping by healthpractioners#healthPracID.

    SELECT
    p.healthPracID,
    p.firstName,
    p.surname,
    COUNT(t.healthPracID) AS num_patients
FROM healthpractioners p
LEFT JOIN treatmentrecords t
    ON p.healthPracID = t.healthPracID
GROUP BY
    p.healthPracID;

    This answer assumes that healthPracID is either a primary key in healthpractioners or that it has a unique index. In that case, we can just group by healthPracID. If not, then we would have to use the following GROUP BY:

    GROUP BY
    p.healthPracID,
    p.firstName,
    p.surname