MySQL PHP UNION JOIN LEFT RIGHT WHAT??

[PWNr00t]

I need assistance with a select statement

so far i have this

"SELECT table1.lastname as ln1, table1.firstname as fn1, table2.lastnameas ln2, table2.firstname as fn2 FROM table1, table2 ORDER BY ln1, ln2";

which produces this

ln1 fn1 ln2 fn2
lastname firstname lastname firstname
lastname firstname lastname firstname
lastname firstname lastname firstname
dupename dupename lastname firstname
dupename dupename lastname firstname
dupename dupename lastname firstname

with duplicate names on the colums that are not the same length

what i would like is somthing like so

ln1orln2 fn1orfn2
lastname firstname
lastname firstname
lastname firstname
lastname firstname
lastname firstname
lastname firstname
lastname firstname

with no duplicates
thanks in advance for any help
ive scouered the net most examples seem so veg with one example but no resulting example

 

[LittleSmudge]

I'm not convinced I thoroughly understand what you're asking for but I think this is it.

SELECT table1.lastname As ln, table1.firstname as fn
FROM table1
UNION
SELECT table2.lastname, table2.firstname FROM table2
ORDER BY ln


That will give

ln        fn
lastnamea firstname
lastnameb firstname
lastnamec firstname
lastnamed firstname
lastnamee firstname
lastnamef firstname
lastnameg firstname
lastnameh firstname

'ope-that-'elps





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PWNr00t]

actualy that probaby would have worked nicely
but i dont have MySQL 4 installed
i tryed to install it today and ran into all sorts of issues
this is a production machine and i dont want kill it

i looked at a howto on geting around union with left join
it seems the examples dont really ever fit what i want

if someone can help with a left join example that would be great

i have 2 tables both with first and last name columns in them
i want to pull all the first and last names out of both tables
and display then in a single alphabetized column by lastname

thanks in advance for any help
and thank you to LittleSmudge for his reply

 

[LittleSmudge]

I suspect that you'll end up having to :-
Create a temp table
Append query to add data from table1
Append query to add data from table 2
Use temp table as data source.




Graham

 

[KamranFeroz]

What I understand is that you need to extract distinct firtname and lastname from both of your table. If this is so then try using distinct in your select clause.

Best of luck.