my database has a table
PID language ....
1 1 ....
1 2 ......
1 3 ....
2 1 ......
2 2 ...........
etc
i.e. each PID SHOULD have language 1,2,3
The problem is some PID have some language missing i.e. only 1,3 or 1,2 etc.
I want to find out those PID with language missing.
How should I write the SQL?
How do I do this?
Moderator: General Moderators
Re: How do I do this?
one easy alternative is using GROUP_CONCAT() comparing the result with whatever string you like.
http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat
http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat
Re: How do I do this?
mikosiko wrote:one easy alternative is using GROUP_CONCAT() comparing the result with whatever string you like.
http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat
oh...I forget to say...I do not know what is exactly PID is.
They are assigned by the system (for each book), and there are holes between them, i.e.
3, 4, 6, 11, 12, ...., 667, 668, 671,...
I mean, I know at best their range, i.e. starting from 1 to some large number.
I know they are integer, countable, and positive
The problem is my database is somewhat inconsistent now.
Every book (PID) should have three (1,2,3) languages. But apparently some are missing.
I need to find out which one is missing.
Re: How do I do this?
Common solution to find non successive numeric "holes" (though it would be just the first element) in series:
[sql]
SELECT
`table`.`field` + 1 AS `missing_field`
FROM
`table`
LEFT JOIN
`table` AS `missing_field_table` ON
`table`.`fielld` + 1 = `missing_field_table`
WHERE
`missing_field_table`.`field` IS NULL
[/sql]
PostgreSql has a set returning function called generate_series which is very useful in such cases - you can try implement it in MySQL and use it in a similar way as the one show above.
[sql]
SELECT
`table`.`field` + 1 AS `missing_field`
FROM
`table`
LEFT JOIN
`table` AS `missing_field_table` ON
`table`.`fielld` + 1 = `missing_field_table`
WHERE
`missing_field_table`.`field` IS NULL
[/sql]
PostgreSql has a set returning function called generate_series which is very useful in such cases - you can try implement it in MySQL and use it in a similar way as the one show above.
There are 10 types of people in this world, those who understand binary and those who don't
Re: How do I do this?
Thank for this nice method. But this does not solve my problem.VladSun wrote:Common solution to find non successive numeric "holes" (though it would be just the first element) in series:
[sql]
SELECT
`table`.`field` + 1 AS `missing_field`
FROM
`table`
LEFT JOIN
`table` AS `missing_field_table` ON
`table`.`fielld` + 1 = `missing_field_table`
WHERE
`missing_field_table`.`field` IS NULL
[/sql]
PostgreSql has a set returning function called generate_series which is very useful in such cases - you can try implement it in MySQL and use it in a similar way as the one show above.
I want to find out which PID is incomplete, i.e. do not have three language tuples 1,2,3.
I have done that by using
SELECT pid, COUNT(language) FROM products_description GROUP BY pid;
Re: How do I do this?
as I said.... according to the objective that you are describing GROUP_CONCAT() solve your problemwvoyance wrote: ......I want to find out which PID is incomplete, i.e. do not have three language tuples 1,2,3.
Code: Select all
SELECT pid,
GROUP_CONCAT(language ORDER BY language) AS the_lang
FROM products_description
GROUP BY pid
HAVING the_lang != '1,2,3';Re: How do I do this?
Thanks, learnt one more method. Which one will be quicker?