Обсуждение: select with multiple occurences in same table
<div class="Section1"><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>Table "inventorytrack"</span></font></span><p class="MsoNormal"><spanclass="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>Attribute<span style="mso-spacerun: yes"> </span>|<span style="mso-spacerun: yes"> </span>Type<span style="mso-spacerun: yes"> </span>| Modifier</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><fontcolor="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">------------+-----------+----------</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black"face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>tattoo<span style="mso-spacerun: yes"> </span>| text<span style="mso-spacerun:yes"> </span>|</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black"face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>past_group | text<span style="mso-spacerun: yes"> </span>|</span></font></span><pclass="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>ranch<span style="mso-spacerun: yes"> </span>| text<span style="mso-spacerun:yes"> </span>|</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black"face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>ent_date<span style="mso-spacerun: yes"> </span>| timestamp |</span></font></span><pclass="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>indate<span style="mso-spacerun: yes"> </span>| date<span style="mso-spacerun:yes"> </span>|</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black"face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">In the above table, I have multiple occurrences of ‘tattoo’.<span style="mso-spacerun: yes"> </span>However, someof the tattoo’s have null value in ‘indate’, some do not.<span style="mso-spacerun: yes"> </span>I would like to selectthose tattoos with a null indate, unless there is another identical tattoo in the table that has<span style="mso-spacerun:yes"> </span>a date value in <span style="mso-spacerun: yes"> </span>‘indate’.<span style="mso-spacerun:yes"> </span>For example, I might have:</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><fontcolor="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">tattoo |<span style="mso-spacerun: yes"> </span>past_group<span style="mso-spacerun: yes"> </span>| ranch<spanstyle="mso-spacerun: yes"> </span>|<span style="mso-spacerun: yes"> </span>ent_date<span style="mso-spacerun: yes"> </span>|<span style="mso-spacerun: yes"> </span>indate</span></font></span><pclass="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">--------+----------------+--------+------------------------+------------</span></font></span><p class="MsoNormal"><spanclass="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>1039<span style="mso-spacerun: yes"> </span>| Barn2/Behind<span style="mso-spacerun:yes"> </span>| Iberia | 2000-03-16 11:03:32-08 |</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><fontcolor="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>1039<span style="mso-spacerun: yes"> </span>| Across/Bubs<span style="mso-spacerun:yes"> </span>| Iberia | 2000-12-28 14:57:13-08 | 2000-12-28</span></font></span><p class="MsoNormal"><spanclass="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"><span style="mso-spacerun: yes"> </span>1832<span style="mso-spacerun: yes"> </span>| GardnerBlock#3 | Iberia |2000-03-16 11:03:32-08 |</span></font></span><p class="MsoNormal" style="margin-left:33.6pt;text-indent:-30.6pt;mso-list:l0level1 lfo2; tab-stops:list 33.6pt"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size: 12.0pt;font-family:Arial">1832<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></font></span><spanclass="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size: 10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial">| GardnerBlock#2 | Iberia | 2000-12-28 12:46:57-08 | 2000-12-28 </span></font></span><pclass="MsoNormal" style="margin-left:33.6pt;text-indent:-30.6pt;mso-list:l0 level1 lfo2; tab-stops:list 33.6pt"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size: 12.0pt;font-family:Arial">1833<font face="Times New Roman" size="1"><span style="font:7.0pt "Times New Roman""> </span></font></span></font></span><spanclass="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size: 10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial">| Becker1<span style="mso-spacerun: yes"> </span>| Iberia | 2000-03-16 11:03:32-08 |</span></font></span><p class="MsoNormal" style="margin-left:3.0pt"><spanclass="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size: 12.0pt;font-family:Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial"size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">So the prospective SELECT would give back:</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><fontcolor="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">tattoo |<span style="mso-spacerun: yes"> </span>past_group<span style="mso-spacerun: yes"> </span>| ranch<spanstyle="mso-spacerun: yes"> </span>|<span style="mso-spacerun: yes"> </span>ent_date<span style="mso-spacerun: yes"> </span>|<span style="mso-spacerun: yes"> </span>indate</span></font></span><pclass="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">1039<span style="mso-spacerun: yes"> </span>| Across/Bubs<span style="mso-spacerun: yes"> </span>| Iberia | 2000-12-2814:57:13-08 | 2000-12-28</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black"face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">1832| GardnerBlock#2 | Iberia | 2000-12-28 12:46:57-08 | 2000-12-28</span></font></span><p class="MsoNormal" style="margin-left:3.0pt"><spanclass="EmailStyle15"><font color="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size: 12.0pt;font-family:Arial">1833| Becker1<span style="mso-spacerun: yes"> </span>| Iberia | 2000-03-16 11:03:32-08 |</span></font></span><p class="MsoNormal"><span class="EmailStyle15"><fontcolor="black" face="Arial" size="2"><span style="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial"> </span></font></span><p class="MsoNormal"><span class="EmailStyle15"><font color="black" face="Arial" size="2"><spanstyle="font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family: Arial">Any suggestions on how to do this?</span></font></span></div>
WIlliam, > Table "inventorytrack" > Attribute | Type | Modifier > ------------+-----------+---------- > tattoo | text | > past_group | text | > ranch | text | > ent_date | timestamp | > indate | date | > Are you sure that you're using the most efficient data types for this table? And where's your primary key? > In the above table, I have multiple occurrences of tattoo. > However, some > of the tattoos have null value in indate, some do not. I would > like to > select those tattoos with a null indate, unless there is another > identical > tattoo in the table that has a date value in indate. THis one is easy. However, I'm not sure exactly waht you want; the sample return tuples you provided do not match what you asked for! If you wanted to answer the question above: SELECT * FROM inventorytrack WHERE indate IS NULL AND NOT EXISTS (SELECT tattoo FROM inventorytrack it2WHERE it2.tattoo = inventorytrack.tattoo AND indate IS NOT NULL ); -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
I'm having trouble getting LIKE clauses to work correctly inside a plpgSQL
function.
Here's my table:
id | val
----+-------------
1 | hello
2 | there
3 | everyone
Here's my function:
CREATE FUNCTION intable(char)
RETURNS INTEGER
AS
'
DECLARE
input ALIAS FOR $1;
temp INTEGER;
BEGIN
SELECT INTO temp id FROM test WHERE val LIKE ''input%'';
RAISE NOTICE ''Value of temp is %'',temp;
RETURN temp;
END;
'
LANGUAGE 'plpgsql';
I should be able to SELECT('hello') and get back 1, correct?
No matter what I put in as a parameter, it always returns null.
If I change the LIKE clause to read "...LIKE ''hello%''" it does
in fact work. Or if I scrap the LIKE clause and have it
read something such as ".... id = input" (if input is an integer)
it also works fine.
Any thoughts?
Thanks,
Robby