[SQL] Problem with joins
Hi all, I am trying to do something unusual (for me) and I can't quite find the relevant part in the documentation (no mention to joins in the insert section, or the index). I want to get a list of data sources with the corresponding data, if the data exists, or with null, if the data doesn't. But anyway I need the data sources (all of them). So I have tried: select source_name,data_value from source,data where data_source_id=source_id but it lacks those rows where there is no data in "data" table. I have also tried: select source_name,data_value from source,data where data_source_id=source_id union select source_name,source_id,NULL from source,data This is a bit better, in the sense that I get back all I need, but there are too many lines: when there is data, I get the line with the data value and also with NULL. I strongly suspect that there is a more elegant way of doing it: a solution or pointers to relevant online litterature would be welcome. Thanks, Jean-Marc Libs -- Jean-Marc Libs, ingénieur INTERNET/INTRANET Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net Professionnel : [EMAIL PROTECTED] Lieu de travail : [EMAIL PROTECTED]
Re: Antw: [SQL] Problem with joins
On Wed, 5 Jul 2000, Gerhard Dieringer wrote: > Jean-Marc Libs wrote: > >... > >I have also tried: > >select source_name,data_value from source,data where data_source_id=source_id union >select source_name,source_id,NULL from source,data > > >This is a bit better, in the sense that I get back all I need, but there > >are too many lines: when there is data, I get the line with the data value > >and also with NULL. > >... > > You are on the right way. Change your querry to > > select source_name,data_value > from source,data > where data_source_id=source_id > union > select source_name,source_id > from source > WHERE source_id NOT IN (SELECT source_id FROM data); > > and you will get your expected result. > > BTW this simulates an outer join. Oh, I wasn't aware that outer joins aren't supported :-( Well, I found that it does kinda work. My app is actually more complicated than this, so now I have: select source_name,data_value from source,data where data_source_id=source_id and "conditions on source" union select source_name,NULL from source WHERE source_id NOT IN (SELECT data_source_id FROM data where "my conditions on data") I get exactly the rows I wanted, and I am very glad for the tip (that should go in a FAQ, it it isn't already). But now I wonder where I could put my 'order by source_order' statement, as I can't figure out how to put the rows in the proper order :-( Thanks for the prompt answer anyway, Jean-Marc Libs -- Jean-Marc Libs, ingénieur INTERNET/INTRANET Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net Professionnel : [EMAIL PROTECTED] Lieu de travail : [EMAIL PROTECTED]
[SQL] SERIAL type does not generate new ID ?
Hi all,
I don't really understand what happens, so I put context, then problem:
1/ Context
--
I have this table:
CREATE TABLE film (
film_id SERIAL PRIMARY KEY,
film_country_id CHAR(2),
film_country_id2 CHAR(2),
film_country_id3 CHAR(2),
film_country_id4 CHAR(2),
film_ec_certif BOOL DEFAULT 'false',
film_ce_certif BOOL DEFAULT 'false',
film_prod_year INTEGER,
film_eur_support BOOL DEFAULT 'false',
film_media_support BOOL DEFAULT 'false',
film_budgetnat DECIMAL,
film_budgetnat_rate_id CHAR(3),
film_budget DECIMAL,
film_provisoire BOOL DEFAULT 'false',
film_production_id INTEGER,
film_production_id2 INTEGER,
film_production_id3 INTEGER,
film_production_id4 INTEGER
);
CREATE UNIQUE INDEX film_uidx ON film ( film_id );
CREATE INDEX film_production_1 ON film (film_production_id);
CREATE INDEX film_production_2 ON film (film_production_id2);
CREATE INDEX film_production_3 ON film (film_production_id3);
CREATE INDEX film_production_4 ON film (film_production_id4);
Now if I look at it with postgresadmin, I see:
-- postgresAdmin PostgreSQL-Dump
--
-- Serveur: localhost:5432 Base de données: admissions12
-
--
-- Structure de la table 'film'
--
DROP SEQUENCE film_film_id_seq;
CREATE TABLE film (
film_id serial,
film_country_id bpchar,
film_country_id2 bpchar,
film_country_id3 bpchar,
film_country_id4 bpchar,
film_ec_certif bool,
film_ce_certif bool,
film_prod_year int4,
film_eur_support bool,
film_media_support bool,
film_budgetnat numeric,
film_budgetnat_rate_id bpchar,
film_budget numeric,
film_provisoire bool,
film_production_id int4,
film_production_id2 int4,
film_production_id3 int4,
film_production_id4 int4
)
;
SELECT setval ('film_film_id_seq', 6);
2/ Problem:
I have this query in PHP:
insert into film
(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values ('FR','','','','2000','f','f','f','f','f')
And it gives the following error:
ERROR: Cannot insert a duplicate key into a unique index
3/ Question:
Shouldn't it automagically create an appropriate film_id ?
Right now, I can do it with
$sql_query_film="insert into film
(film_id,film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
values
(MAX(film_id)+1,'$film_country_id','$film_country_id2','$film_country_id3','$film_country_id4','$film_prod_year','$film_ec_certif','$film_ce_certif','$film_eur_support','$film_media_support','$film_provisoire')";
and it works fine as long as there are no concurrent accesses, but
there must be a better way :-(
This is very puzzling because it seems that this is the only table which
gives such errors.
Pointers on relevant online documentation accepted, too :-)
Jean-Marc Libs
--
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net
Professionnel : [EMAIL PROTECTED]
Re: [SQL] SERIAL type does not generate new ID ?
On Sat, 10 Jun 2000, Jean-Marc Libs wrote:
Hi all,
> I don't really understand what happens, so I put context, then problem:
>
> 1/ Context
> --
> I have this table:
>
> CREATE TABLE film (
> film_id SERIAL PRIMARY KEY,
snip
> )
> ;
> SELECT setval ('film_film_id_seq', 6);
>
> 2/ Problem:
>
> I have this query in PHP:
> insert into film
>
>(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
> values ('FR','','','','2000','f','f','f','f','f')
>
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index
>
> 3/ Question:
>
> Shouldn't it automagically create an appropriate film_id ?
>
OK, I guess I figured out what happens: this serial word just creates
some sequence which acts as a counter of sort, which increments whenever
I insert stuff whithout specifying a value for film_id. Only, when
I insert with a specified film_id (as I did when I imported my
legacy values), the sequence doesn't budge, so it is now out of sync
with the actual count of items in the database.
I'll just SELECT setval to max(film_id) and everything should be OK.
Thanks to those who answered me,
Jean-Marc Libs
--
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net
Professionnel : [EMAIL PROTECTED]
Lieu de travail : [EMAIL PROTECTED]
