[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
 

Re: [PROGRAMMERING] MySQL, SELECT's og UNION



Burde måske lige tilføje, at jeg skal bruge alle datasæt fra tabel1 og i tilfælde af et pretype_id skal det datasæt der matcher pretype_id fra table2 linkes sammen med tid fra table1. Men alle datasæt fra table2 som ikke er linket ind i table1, skal også med...

Har kastet mig over noget CASE WHEN THEN med en UNION til sidst for at få table2 med...

Og hvorfor spørger nogen. Fordi jeg ellers kommer ud i noget med 3 gange connect + query med tilhørende 3 gange PHP kode!
MySQL er stadig noget hurtigere til at afvikle det end med PHP flettet ind i!


/Nis

Nis wrote:
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] *