A little query help?

[Switch0r]

Hey peeps, I just want to know if something is possible, particularly as I don't really know how to implement what I want...

 

Right, so I've got a table that i want to get pairs of unique records from 4 separate columns (its for 2 sets of peoples first & last names), named first1, last1, first2, last2. I want to be able to select unique variations of first & last for both, and then get a unique set of records covering both people.

 

I hope this makes sense

 

I'll give an example...

 

First/Last 1 we have: Jeff Beck, Jeff Buckley, Jeff Daniels

First/Last 2 we have: James Dean, Jeff Daniels, Paul Daniels

 

From the query I want 1 set of records to be: Jeff Beck, Jeff Buckley, Jeff Daniels, Paul Daniels, James Dean

 

Bearing in mind that the first/last names are in different columns of the table.

 

Any thoughts? Some kind of JOIN maybe (I've never used one before...)?

[Barand]

With that structure I'd do it like this

<?php

$sql = "SELECT CONCAT(first1, ' ', last1) as name1, CONCAT(first2, ' ', last2) as name2
        FROM tablename";
$res = mysql_query($sql);

$names = array();
while (list($n1, $n2) = mysql_fetch_row($res))
{
    $names[] = $n1;
    $names[] = $n2;
}

$names = array_unique($names);
?>

 

or you could try (untested)

SELECT DISTINCT x.name FROM
(SELECT CONCAT(first1, ' ', last1) as name FROM tablename
    UNION
    SELECT CONCAT(first2, ' ', last2) as name FROM tablename) as x
ORDER BY x.name

[Switch0r]

Thanks for that!

 

I've gone with the UNION option (so I can keep the values of first1/last1 etc separate), although now I can't get the sorting I want to work.

 

My query is now this:

 

(SELECT last1, first1 FROM table WHERE last1 LIKE '$operator' ORDER BY last1) UNION (SELECT last2, first2 FROM table WHERE last2 LIKE '$operator' ORDER BY last2)

 

This gets all the data I need for my list, although I want to sort all the items together, not just have last1 a-z then last2 a-z, I want (last1 & 2) a-z.

 

I'm thinking of some kind of temporary table situation (which I read about optimizing in the mysql docs, but not actually how to go about it...). Any thoughts?

[Barand]

I gave you my thoughts. The sort works fine with my query.

 

Or have you an old version of MySql that doesn't support subqueries? You need 4.1+

[Switch0r]

I'm running MySQL 5.0.41, so that shouldn't be a problem should it?

 

With the query in my prev post running i get this response:

 

H

Mike Hodges

Charles Herman-Wurmfeld

Brian Helgeland

Stephen Herek

Amy Heckerling

John Hamburg

Guy Hamilton

Albert Hughes

Jared Hess

Walter Hill

Curtis Hanson

Renny Harlin

Allen Hughes

 

(It's a list of film directors in case you were wondering )

 

The list should be sorted by the last name of all items going a-z, but it's not

 

What I was thinking, other than changing the query to do it for me, was either making a temp table and populating it with the results, then querying that table to get a properly sorted list, or populating an array with all the stuff and sorting that somehow...

[Barand]

SELECT DISTINCT x.name FROM
(SELECT CONCAT(first1, ' ', last1) as name, last1 as lname FROM tablename
    UNION
    SELECT CONCAT(first2, ' ', last2) as name, last2 as lname  FROM tablename) as x
ORDER BY x.lname

[Switch0r]

That works like a charm! Is there any way to still select first1, last1, first2, & last2 all as separate items though?

 

If not could I use the concatenated name to then make another search?

 

ie. SELECT * WHERE CONCAT(first1, ' ', last1) = $fullname OR CONCAT(first2, ' ', last2) = $fullname;

[Switch0r]

Scratch that last one, I've just checked it, and it works...I should really try things before I post

[Switch0r]

I do have 1 final question (I promise I'll stop annoying everyone after this!)

 

Will that query work with more than 2 columns? So when using the DISTINCT x.name portion, could that be x1.x2.x3 etc?

[Barand]

SELECT DISTINCT x, y, z

 

will give distinct combinations of the 3 columns.