[SQL] Selecting rows as if they were columns?
Gang,
I've got what I think should be a pretty simple problem- I just can't
figure out how I'd do it. Heck, I can't even figure out how I'd search
for an answer to this problem.
I've got 2 tables: member and member_attr
Member is just a standard entry for a member. Very simple:
table member {
idinteger,
name character varying(30)
}
Member_attr is just a table of name/value pairs, such as:
table memberextra {
memberid integer,
param1 character varying(512),
param2 character varying(512),
}
where param1 is the name, param2 is the value.
Right now, I can:
select member.id, member.name, member_attr.param1, member_attr.param2
from member, member_attr where member.id = member_attr.memberid;
...this gives me n rows per member where n = # of attributes for that
member, like this:
id | name | param1| param2
===
1| dave | home | /home/dave
1| dave | testing| 1
2| john | home | /home/john
...is there some way to make this return like this:
id | name| home | testing
==
1 | dave| /home/dave | 1
2 | john| /home/john |
...where it sort of "expands" the rows out into columns? Am I just
going about this the wrong way? Thoughts on the subject would be
greatly appreciated.
thanks!
/kurt
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL]
Vishal Charan (IT Fiji) wrote: please remove my email from your database contacts. Best Regards, *_Vishal Charan_* *IT Support * *Courts/Homecentres* Is this another worm that attempts to lower the internet traffic by requesting to remove people from mailing lists? It's the 5th or so "unsubscribe" message I see with the same wording in the body. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Selecting rows as if they were columns?
Kurt, > I've got what I think should be a pretty simple problem- I just can't > figure out how I'd do it. Heck, I can't even figure out how I'd search > for an answer to this problem. This is an old common SQL problem ... the "crosstab" table. There are 3 standard ways to solve it, depending on the exact circumstances of your case. It's probably best if you buy Joe Celko's "SQL for Smarties, 2nd Ed.", which has a 50-page chapter on the topic and explores the methods in detail. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Selecting rows as if they were columns?
On Thursday 09 October 2003 18:14, Josh Berkus wrote: > Kurt, > > > I've got what I think should be a pretty simple problem- I just can't > > figure out how I'd do it. Heck, I can't even figure out how I'd search > > for an answer to this problem. > > This is an old common SQL problem ... the "crosstab" table. There are 3 > standard ways to solve it, depending on the exact circumstances of your > case. It's probably best if you buy Joe Celko's "SQL for Smarties, 2nd > Ed.", which has a 50-page chapter on the topic and explores the methods in > detail. Or see the contrib/tablefunc functions which can do this sort of thing for you. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL query problem
Marek, Not sure but, try switching the lines db_data.mda_mod_con _CON, db_data.set_mda_fue _FUE with db_data.set_mda_fue _FUE, db_data.mda_mod_con _CON so there query is: 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_mak_eng _ENG, db_data.set_mda_fue _FUE, db_data.mda_mod_con _CON 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 --- Marek Lewczuk <[EMAIL PROTECTED]> wrote: > Hello, > I'm in the middle of the migration process from > MySQL to PostgreSQL and > I cannot understand why this query isn't working (in > MySQL it's working > fine). PG returns: ERROR: Relation "_con" does not > exist > > This is my query: __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
> > 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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] SQL query problem
Hello, I'm in the middle of the migration process from MySQL to PostgreSQL and I cannot understand why this query isn't working (in MySQL it's working fine). PG returns: ERROR: Relation "_con" does not exist This is my 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, 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 I will be appreciated for you help. ML ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL query problem
On Thu, Oct 09, 2003 at 20:52:58 +0100, Marek Lewczuk <[EMAIL PROTECTED]> wrote: > Hello, > I'm in the middle of the migration process from MySQL to PostgreSQL and > I cannot understand why this query isn't working (in MySQL it's working > fine). PG returns: ERROR: Relation "_con" does not exist Probably you quoted the table name when you created it so that it is named _CON. However when you don't quote the name in later references _CON is converted to _con which is a different name that doesn't match any existing table. This behavior is contrary to the spec (lowercasing instead of uppercasing), but isn't going to change since uppercase names look ugly. A good general rule to follow is to either never quote names or always quote names. > > This is my 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, > 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 > > > I will be appreciated for you help. > > ML > > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
