Combining select statements

[Charp]

Hi All. I'm working to advance my understanding of mySQL and could use some pointers.

I'm okay with basic queries such as:

SELECT id, firstname FROM table WHERE firstname = '';

But I'm having a tough time wrapping my head around how I would combine the query above with the following:

SELECT id, lastname FROM table WHERE lastname = '';

Is it possible to combine these queries into one such that I can output a list of IDs where firstname was empty and then a list of IDs where lastname was empty? It seems like GROUP BY should be used, but I'm getting no where with this on my own.

BTW, I'm using PHP if that helps with providing any examples.

Thanks in advance. 

[requinix]

GROUP BY is for grouping multiple rows together. That's not quite what you want to do here, is it?

What you want is possible, and rather easily, but first a question: when you read back the rows from this single query, how are you going to know whether each one is showing you an empty firstname or an empty lastname? (Does the distinction matter?)

And note that there's no point returning the firstname or lastname because you already know what they're all going to say: they'll be empty strings.

[Charp]

@requinix Yes, the distinction does matter. A simplified version of what I'm trying is do is to generate a report indicating which users (by ID) have no first name listed and which users (by ID) have no last name listed. Yes, some users may be in both lists. My thinking on GROUP BY is that the first group of rows would be those missing first names the the second group would be those missing last names.  That thinking, however, may be off the mark.

There's actually a number of checks I wish to perform, but If I can understand a simple example like the one I in my first post, I think I'll be able to extend that understanding to more complex examples.

[requinix]

I'll rephrase what I said: GROUP BY is for grouping multiple rows together into one row. Like, say, "group all of the records together and give me one row that counts how many there were".

So let's say you fix the query so that it returns the ID and which column it was that didn't have the value. And say you get both sets of results combined into one query.
Duplicate IDs are going to show up twice. How would you end up processing those records? You'll show the user once as not having a firstname, then somewhere else later you'll show the same user again as not having a lastname. Wouldn't it be better for the person looking at this page to see the ID and that both fields were empty?
Which means now you should come up with a query that shows the ID and indicates the firstname if it's empty as well as the lastname if it's empty. And the condition for a user to show up in this new list is the same as before but slightly different: the firstname is empty or the lastname is empty.
And you know an easy way to indicate whether the firstname or lastname is empty? By returning the value of the field and literally checking if it's empty.

All that leaves you with

SELECT id, firstname, lastname FROM table WHERE firstname = '' OR lastname = ''

 

[Charp]

I was just looking up GROUP BY and see that it's used with an aggregate function, so I think that's not going to work for me.

Allow me to explain more thoroughly what I'm after. I'm trying to validate data from an uploaded CSV file. Errors could include missing values, like first and last names, or an invalid email address. I'm able to do this:

$strQuery = "SELECT
    COUNT(CASE WHEN `firstname` = '' THEN 1 END) AS no_first,
	COUNT(CASE WHEN `lastname` = '' THEN 1 END) AS no_last,
FROM tablename";

With the results, I can indicate that there are x rows with no first name and y rows with no last name, but it would be more useful for correcting the bad data in the CSV file if the report was something  like this:

No first name: rows 3, 11, 20

No last name: rows 1, 8, 11, 39

In other words, I would like to specify what is missing from any given row rather than just note that some rows are missing something.

[requinix]

I really don't get the impression that you're reading my replies. They both told you that GROUP BY wasn't the answer, and the second one told you not one but actually two different methods to get the information you need (one probably more useful than the other).

[Charp]

LOL, I am reading you replies. Are you reading mine?

I agreed that GROUP BY  is not the answer.

I do see your suggested methods but do not see how they can generate the desired output without checking values twice - once in mySQL (WHERE firstname = '') and then again in PHP ($row['firstname'] = '').

If I'm not following your replies it's not because I can't read. It's because my mySQL skills are rudimentary.

[guymclarenza]

On 1/28/2021 at 6:15 AM, Charp said:

LOL, I am reading you replies. Are you reading mine?

I agreed that GROUP BY  is not the answer.

I do see your suggested methods but do not see how they can generate the desired output without checking values twice - once in mySQL (WHERE firstname = '') and then again in PHP ($row['firstname'] = '').

If I'm not following your replies it's not because I can't read. It's because my mySQL skills are rudimentary.

Why are you checking if they are "" in your PHP? That makes no sense. If the MySQL has given you the answers, use them in your PHP. This is not exactly what you want but it will give you a start in looking for the right answer.

SELECT id, firstname, lastname FROM table WHERE firstname = '' OR lastname = ''

while($row = $query->fetch(PDO::FETCH_ASSOC)) {
		$uid = $row["id"];
		$fname = $row["firstname"];
		$lname = $row["lastname"];

echo $uid." lists first name as $fname and lastname as $lname <br />";

 

[Barand]

On 1/28/2021 at 3:53 AM, Charp said:

No first name: rows 3, 11, 20

No last name: rows 1, 8, 11, 39

If that is how you want the output then GROUP BY may well be the answer

student data...

+-------+-----------+----------+
| regno | firstname | lastname |
+-------+-----------+----------+
|  9738 | Jane      | Jenkins  |
|  9844 | Janet     | Gordon   |
|  9966 | Liz       | Lyle     |
|  9978 | Olivia    | Unwin    |
|  9979 | Curly     | NULL     |
|  9980 | NULL      | Larry    |
|  9981 | NULL      | Mo       |
|  9982 | Fred      |          |
|  9983 | Emily     | NULL     |
+-------+-----------+----------+

query...

SELECT CASE WHEN IFNULL(firstname, '') = '' THEN 'No first name'
            WHEN IFNULL(lastname, '') = '' THEN 'No last name'
            ELSE 'OK' 
            END as category
       , COUNT(*) as Errors     
       , GROUP_CONCAT(regno SEPARATOR ', ') as IDs
FROM student
GROUP BY category
HAVING category <> 'OK';

results...

+---------------+--------+------------------+
| category      | Errors | IDs              |
+---------------+--------+------------------+
| No first name |      2 | 9980, 9981       |
| No last name  |      3 | 9983, 9979, 9982 |
+---------------+--------+------------------+

CAUTION: the default maximum length of GROUP_CONCAT item is 1024 characters so in my example I would only get a max of 170 ids listed.