[an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] (none) [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] (none) [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive][an error occurred while processing this directive]
 
[an error occurred while processing this directive] [an error occurred while processing this directive]
Skåne Sjælland Linux User Group - http://www.sslug.dk Home   Subscribe   Mail Archive   Forum   Calendar   Search
MhonArc Date: [Date Prev] [Date Index] [Date Next]   Thread: [Date Prev] [Thread Index] [Date Next]   MhonArc
 

MySQL, SELECT's og UNION



Har brændt den sidste rest af idé til en løsning på denne her, så jeg håber at der sidder en eller flere med større overskud og klarhed!

Har 2 tables i MySQL 4.1.20, table1 og table2, som stort set er ens.
table2 indeholder nogle færdige datasæt, som bliver linket over i table2 via foreign key på pretype_id.
I table1 kan brugere selv definere datasæt, men også linke sæt's fra table2 sammen via pretype_id.


Problemet er, at "pretype_id AS tid" fra table2, overskriver tid fra table1!
Logisk nok, men hvordan undgår jeg dette og beholder tid fra table1?

Har været ude i en LEFT JOIN, men så kommer der dobbelt op af de samme columns i resultatet, hvilket så giver noget ekstra arbejde på kode siden.

/Nis

OBS! Der kommer lige et par linier...

SQL:

(SELECT
	table2.pretype_id,
	table2.type_name,
	table2.type_desc,
	table2.type_number,
	table2.type_char
FROM
	table2
WHERE
	(table2.type_id = "2")
ORDER BY table2.type_number)

UNION

(SELECT
	table1.tid,
	table1.pretype_id,
	table1.type_name,
	table1.type_desc,
	table1.type_number,
	table1.type_char
FROM
	table1
WHERE
	(table1.type_id = "2") AND
	(table1.users_uid = "1") AND
	(table1.pretype_id IS NULL)
ORDER BY table1.type_number)



TABLE STRUCTURE:

CREATE TABLE `table1` (
`tid` int(10) unsigned NOT NULL auto_increment,
`type_id` int(10) unsigned NOT NULL default '0',
`users_uid` int(10) unsigned NOT NULL default '0',
`pretype_id` int(10) unsigned default NULL,
`type_name` varchar(120) collate utf8_danish_ci default NULL,
`type_desc` mediumtext collate utf8_danish_ci,
`type_number` int(2) unsigned NOT NULL default '0',
`type_char` varchar(2) collate utf8_danish_ci NOT NULL default '',
`type_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`tid`),
UNIQUE KEY `type_index4345` (`type_char`,`type_id`,`users_uid`),
UNIQUE KEY `type_index4197` (`type_number`,`type_id`,`users_uid`),
KEY `type_FKIndex1` (`users_uid`),
KEY `type_FKIndex2` (`type_id`),
KEY `type_FKIndex3` (`pretype_id`)
) ENGINE=InnoDB;



CREATE TABLE `table2` (
`pretype_id` int(10) unsigned NOT NULL auto_increment,
`type_id` int(10) unsigned NOT NULL default '0',
`type_name` varchar(120) collate utf8_danish_ci NOT NULL default '',
`type_desc` mediumtext collate utf8_danish_ci NOT NULL,
`type_number` int(2) unsigned NOT NULL default '0',
`type_char` varchar(2) collate utf8_danish_ci NOT NULL default '',
`type_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`pretype_id`),
UNIQUE KEY `pretype_index4366` (`type_id`,`type_number`),
UNIQUE KEY `pretype_index4367` (`type_id`,`type_char`),
KEY `pretype_FKIndex1` (`type_id`)
) ENGINE=InnoDB ;


 
Home   Subscribe   Mail Archive   Index   Calendar   Search

 
 
Questions about the web-pages to <www_admin>. Last modified 2006-09-01, 02:01 CEST [an error occurred while processing this directive]
This page is maintained by [an error occurred while processing this directive]MHonArc [an error occurred while processing this directive] # [an error occurred while processing this directive] *