From: | "Marek Lewczuk" <newsy(at)lewczuk(dot)com> |
---|---|
To: | "'Karsten Hilbert'" <Karsten(dot)Hilbert(at)gmx(dot)net>, "'Lista dyskusyjna pgsql-general'" <pgsql-general(at)postgresql(dot)org>, "Lista dyskusyjna pgsql-sql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: SQL query problem (many table in FROM statement and many LEFT JOIN's) |
Date: | 2003-10-10 07:53:11 |
Message-ID: | 004001c38f03$8d8b8230$4701a8c0@marek |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
> > SELECT
> > _CON.con_id,
> Please make sure you get the quoting right regarding table
> names. PostgreSQL will fold _CON into _con unless quoted
> "_CON". So, it may be that you created the table with quotes
> ("_CON"). Now, in your query you don't use quotes and thusly
> it is looking for a _con table. The simple rule of thumb is
> to either always or never use quotes.
I don't think that this is the solution, becouse the query:
SELECT
_CON.con_id,
_MOD.mod_ty,
_CON.dri_id,
_CON.man_cod,
_ENG.eng_pow
FROM
db_data.mda_mod _MOD,
db_data.mda_mod_con _CON,
db_data.mda_mak_eng _ENG,
db_data.set_mda_fue _FUE
WHERE
_MOD.mod_id = '283' AND
_CON.mod_id = _MOD.mod_id AND
_CON.psd <= NOW() AND
_CON.ped > NOW() AND
_ENG.eng_id = _CON.eng_id AND
_ENG.eng_fue = _FUE.fue_id
...is working fine. I belive that this some problem with LEFT JOIN and
FROM statement. If I will rewrite this query:
SELECT
_CON.con_id,
_MOD.mod_ty,
_VER.version,
_YEA.year,
_CON.dri_id,
_CON.man_cod,
_ENG.eng_pow
FROM
db_data.mda_mod _MOD
JOIN db_data.mda_mod_con _CON ON _CON.mod_id = _MOD.mod_id AND
_CON.psd <= NOW() AND _CON.ped > NOW()
JOIN db_data.mda_mak_eng _ENG ON _ENG.eng_id = _CON.eng_id
JOIN db_data.set_mda_fue _FUE ON _ENG.eng_fue = _FUE.fue_id
LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id
WHERE
_MOD.mod_id = '283'
... It also working fine. !!!! The question is, why my first query isn't
working:
SELECT
_CON.con_id,
_MOD.mod_ty,
_VER.version,
_YEA.year,
_CON.dri_id,
_CON.man_cod,
_ENG.eng_pow
FROM
db_data.mda_mod _MOD,
db_data.mda_mod_con _CON,
db_data.mda_mak_eng _ENG,
db_data.set_mda_fue _FUE
LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id
WHERE
_MOD.mod_id = '283' AND
_CON.mod_id = _MOD.mod_id AND
_CON.psd <= NOW() AND
_CON.ped > NOW() AND
_ENG.eng_id = _CON.eng_id AND
_ENG.eng_fue = _FUE.fue_id
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2003-10-10 08:13:50 | Re: [SQL] SQL query problem (many table in FROM statement and many LEFT JOIN's) |
Previous Message | Jonathan Bartlett | 2003-10-10 06:09:30 | Re: Humor me: Postgresql vs. MySql (esp. licensing) |
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Wegner | 2003-10-10 08:09:43 | Re: Oracle 'connect by prior' now eaiser in 7.3? |
Previous Message | Iain Sinclair | 2003-10-10 05:51:54 | smart(er) column aliases |