PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Nov 23, 2014 7:58 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: How do I do this?
PostPosted: Thu Jun 21, 2012 4:40 am 
Offline
Forum Contributor
User avatar

Joined: Tue Apr 17, 2012 8:24 pm
Posts: 135
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?


Top
 Profile  
 
 Post subject: Re: How do I do this?
PostPosted: Thu Jun 21, 2012 6:31 am 
Offline
Forum Regular

Joined: Wed Jan 13, 2010 8:22 pm
Posts: 754
one easy alternative is using GROUP_CONCAT() comparing the result with whatever string you like.
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat


Top
 Profile  
 
 Post subject: Re: How do I do this?
PostPosted: Thu Jun 21, 2012 10:42 am 
Offline
Forum Contributor
User avatar

Joined: Tue Apr 17, 2012 8:24 pm
Posts: 135
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/group-by-functions.html#function_group-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 :P

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.


Top
 Profile  
 
 Post subject: Re: How do I do this?
PostPosted: Thu Jun 21, 2012 4:12 pm 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4294
Location: Sofia, Bulgaria
Common solution to find non successive numeric "holes" (though it would be just the first element) in series:
Syntax: [ Download ] [ Hide ]
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
 


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.

_________________
Image
http://openfmi.net/projects/flattc/ Linux is better :)


Top
 Profile  
 
 Post subject: Re: How do I do this?
PostPosted: Thu Jun 21, 2012 6:05 pm 
Offline
Forum Contributor
User avatar

Joined: Tue Apr 17, 2012 8:24 pm
Posts: 135
VladSun wrote:
Common solution to find non successive numeric "holes" (though it would be just the first element) in series:
Syntax: [ Download ] [ Hide ]
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
 


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.


Thank for this nice method. But this does not solve my problem.
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;


Top
 Profile  
 
 Post subject: Re: How do I do this?
PostPosted: Thu Jun 21, 2012 9:43 pm 
Offline
Forum Regular

Joined: Wed Jan 13, 2010 8:22 pm
Posts: 754
wvoyance wrote:
......I want to find out which PID is incomplete, i.e. do not have three language tuples 1,2,3.


as I said.... according to the objective that you are describing GROUP_CONCAT() solve your problem

Syntax: [ Download ] [ Hide ]
SELECT pid,
       GROUP_CONCAT(language ORDER BY language) AS the_lang
  FROM products_description
  GROUP BY pid
  HAVING the_lang != '1,2,3';


Top
 Profile  
 
 Post subject: Re: How do I do this?
PostPosted: Fri Jun 22, 2012 5:40 am 
Offline
Forum Contributor
User avatar

Joined: Tue Apr 17, 2012 8:24 pm
Posts: 135
Thanks, learnt one more method. Which one will be quicker?


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group