[RESOLVED] Special DISTINCT query?

[h4x0rmx]

I have a large db that looks like this:

CREATE TABLE `cra` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(13) NOT NULL,
  `subject` varchar(180) DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  `street` varchar(100) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  `company_name` varchar(150) DEFAULT NULL,
  `email1` varchar(40) DEFAULT NULL,
  `email2` varchar(40) DEFAULT NULL,
  `email3` varchar(40) DEFAULT NULL,
  `email4` varchar(40) DEFAULT NULL,
  `phone1` varchar(40) DEFAULT NULL,
  `phone2` varchar(40) DEFAULT NULL,
  `phone3` varchar(40) DEFAULT NULL,
  `phone4` varchar(40) DEFAULT NULL,
  `link1` varchar(250) DEFAULT NULL,
  `link2` varchar(250) DEFAULT NULL,
  `link3` varchar(250) DEFAULT NULL,
  `link4` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=latin1;

I'm omitting a few other fields, but this will suffice for my example.
I do have to create a report (or a subset of the db) with unique data. For example, I have to ignore records that have emails from domains like yahoo, hotmail, gmail. This shouldn't be a problem, what I'm worried about is that I have to ignore records where (email1 OR email2 OR email3 OR email4) are equal to (email1 OR email2 OR email3 OR email4) in any other record in the database. Same thing applies to phone numbers and links (where I have up to 10 links per record).

What would be a good and decently efficient way of doing this?
First thing that comes to my mind is comparing one by one, but that could take a while.
I was wondering if there's a way of doing something like this:
If I have a record

{id=1, ..., phone1=11111111, phone2=22222222, phone3=33333333, phone4='',...}

have mysql create a temporary table with something like

{id=1, ..., phone=11111111,...}
{id=1, ..., phone=22222222,...}
{id=1, ..., phone=33333333,...}
{id=1, ..., phone=,...}

and then just use DISTINCT. Don't know if it's possible, but I'm sure one of you do! 🙂

Btw, I'm not too worried about efficiency as I'm worried about how easy is to implement the solution.
Any ideas?
I will really appreciate your help!

[bradgrafelman]

h4x0rmx wrote:

What would be a good and decently efficient way of doing this?

The first step would be to follow common DB designing guidelines and normalize your database.

Usually, you should never have duplicated columns (email1, email2, etc.) in a table. Multi-valued fields require a second relation (related to the original table as a 1-to-many relationship) that tie the primary key of the original table to one of the values given.

You might want to read a decent article/guide or two on Database Normalization before you write any SQL queries at all - it will save you a lot of hassle in the end if you design the DB properly rather than try to kludge together some ugly SQL queries to get by.

[h4x0rmx]

bradgrafelman;10977900 wrote:

The first step would be to follow common DB designing guidelines and normalize your database.

Usually, you should never have duplicated columns (email1, email2, etc.) in a table. Multi-valued fields require a second relation (related to the original table as a 1-to-many relationship) that tie the primary key of the original table to one of the values given.

You might want to read a decent article/guide or two on Database Normalization before you write any SQL queries at all - it will save you a lot of hassle in the end if you design the DB properly rather than try to kludge together some ugly SQL queries to get by.

I understand your point. The problem is that I have been given this data. I didn't design the structure of the db, not can I change it.
Would you recommend creating my own copy of this db, but normalizing it?

[bradgrafelman]

h4x0rmx wrote:

I didn't design the structure of the db, not can I change it.

Ouch! You should go find the person who did design it and slap them across the face with the largest article on DB Designing 101 you can print! :p

h4x0rmx wrote:

Would you recommend creating my own copy of this db, but normalizing it?

Not sure - that would depend on a lot of variables (how often do you need to do this? how often does the data change? how quickly do you need results? what is your favorite sandwich topping? etc. etc.).

Is it possible to work with the table structure as-is? Yes. Is it possible that the answer will require a SQL query that is easily 3 times as long (read: complex) or more as what the normalized approach to the same data set would require? Yes.

If it were normalized, you could just use some correlated subqueries (e.g. "WHERE NOT EXISTS (SELECT email FROM email_lookup el WHERE el.id = primary_table.id AND email LIKE '%@.com')") rather than listing out numerous fields and performing the same operation(s) on them all.

[h4x0rmx]

The report is going to be created once a day with data only from the last day (which is likely to be a few thousand records). I believe they will be running it for one or two months.
They just want to have something that works and not the most efficient thing.

I will suggest to normalize the db, but in case they don't listen, what would you guys suggest?

[h4x0rmx]

So I'm allowed to create other tables now. I have a table that looks like the following for emails:

CREATE TABLE `emails` (
  `id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The tables for phones and links are similar.
How would I create my query taking into account my new 3 tables?? 😕

[bradgrafelman]

h4x0rmx;10977909 wrote:

How would I create my query taking into account my new 3 tables?? 😕

Just to make sure we're on the same page... which query are you talking about? What do you want it to do?

[h4x0rmx]

bradgrafelman;10977923 wrote:

Just to make sure we're on the same page... which query are you talking about? What do you want it to do?

I have to create a report where I have to ignore records that have emails from domains like yahoo, hotmail, gmail. This shouldn't be a problem, what I'm worried about is that I have to ignore records where (email1 OR email2 OR email3 OR email4) are equal to (email1 OR email2 OR email3 OR email4) in any other record in the database. Same thing applies to phone numbers and links (where I have up to 10 links per record).

[bradgrafelman]

h4x0rmx wrote:

I have to ignore records that have emails from domains like yahoo, hotmail, gmail.

Right; one way to do that would be to use the 'WHERE NOT EXISTS' subquery I mentioned above.

h4x0rmx wrote:

what I'm worried about is that I have to ignore records where (email1 OR email2 OR email3 OR email4) are equal to (email1 OR email2 OR email3 OR email4) in any other record in the database.

In other words, you don't want multiple listings for the same e-mail address? If so, then just use a GROUP BY clause on the e-mail column (this is assuming you've normalized the DB and don't have the 'email1', 'email2', etc. separate/redundant columns).

[h4x0rmx]

bradgrafelman;10977946 wrote:

Right; one way to do that would be to use the 'WHERE NOT EXISTS' subquery I mentioned above.

In other words, you don't want multiple listings for the same e-mail address? If so, then just use a GROUP BY clause on the e-mail column (this is assuming you've normalized the DB and don't have the 'email1', 'email2', etc. separate/redundant columns).

I see how the GROUP BY will give me unique records for emails, phones or links independently, but how would I get them all together?
These are my individual queries:

SELECT cl.id, cl.metropolitan_area, cl.subject, li.link
FROM cra AS cl
INNER JOIN links AS li
ON li.post_id = cl.id
GROUP BY li.link;

SELECT cl.id AS id, cl.metropolitan_area, cl.subject, p.phone
FROM cra AS cl
INNER JOIN phones AS p
ON p.post_id = cl.id
GROUP BY p.phone;

SELECT cl.id AS id, cl.metropolitan_area, cl.subject, e.email
FROM cra AS cl
INNER JOIN emails AS e
ON e.post_id = cl.id
WHERE 
    e.email NOT LIKE '%gmail.com'
  AND
    e.email NOT LIKE '%hotmail.com'
  AND
    e.email NOT LIKE '%live.com'
  AND
    e.email NOT LIKE '%yahoo.com'
GROUP BY e.email;

I'm guessing I'd do multiple INNER JOINS, but not sure in what order or how to nest them correctly.
Remember, I don't want records in which any email address is in any other record, same thing for links and phones.

Any ideas? :o

[h4x0rmx]

I figured out... I had to nest quite a few joins... I made sure that I had the same WHERE clause in all of my subqueries to make the whole thing more efficient.

Thanks for your help guys!