montymoose
Posts: 85
|
| Posted: 09/18/2007, 6:40 AM |
|
Hi, any help with this will be gratefully recieved:
I'm having trouble creating the SQL code to do the following. Let's assume we have the following data in a table:
----------------------------------------
FIELD A~~~~~~FIELD B
10_____________1
11_____________1
12_____________2
13_____________3
14_____________2
15_____________2
16_____________1
17_____________1
----------------------------------------
I want to compare FIELD A to an URL value (id) and only return rows which do not have the corresponding FIELD B value.
Example: www.site.com/index.php?id=16
Should return rows:
----------------------------------------
FIELD A ~~~~~~FIELD B
12_____________2
13_____________3
14_____________2
15_____________2
----------------------------------------
This happens because the B value corresponding to A 16 is '1' the query simply takes out all '1' values, displaying what's left.
Any suggestions on what the SQL code would be for this?
Cheers,
M00S3.
|
 |
 |
Benjamin Krajmalnik
|
| Posted: 09/18/2007, 9:59 AM |
|
In your BeforeBuildSelect embed:
Set your where clause to be
"FieldB <> (Select FieldB from table where FieldA = " . CCGetParam("id", 0)
.. ")";
This is, of course, procided that your database allows for subselects.
If it does not, then you can just do a CCDLookup for the value of field B
corresponding to the desired Field A, and then
$myvalue = CCDLookup("FieldB", "Table", "FieldA = " . CCGetParam("id", 0),
$DBConnection);
and then set the where clause to
"FieldB <> " . $myvalue;
"montymoose" <montymoose@forum.codecharge> wrote in message
news:546efd54934737@news.codecharge.com...
> Hi, any help with this will be gratefully recieved:
>
> I'm having trouble creating the SQL code to do the following. Let's assume
> we
> have the following data in a table:
>
> ----------------------------------------
> FIELD A FIELD B
> 10 1
> 11 1
> 12 2
> 13 3
> 14 2
> 15 2
> 16 1
> 17 1
> ----------------------------------------
>
> I want to compare FIELD A to an URL value (id) and only return rows which
> do
> not have the corresponding FIELD B value.
>
> Example: www.site.com/index.php?id=17
>
> Should return rows:
>
> ----------------------------------------
> FIELD A FIELD B
> 12 2
> 13 3
> 14 2
> 15 2
> ----------------------------------------
>
> This happens because the B value corresponding to A 17 is '1' the query
> simply
> takes out all '1' values, displaying what's left.
>
> Any suggestions on what the SQL code would be for this?
>
> Cheers,
>
> M00S3.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
whiterabbitwond
Posts: 28
|
| Posted: 09/18/2007, 10:35 AM |
|
Hi Moose, must be from Canada eh. me too.
So, you omitted some important details like what DBMS and version you are using. For the purposes of this reply I will assume mysql 5+ Althought this should work with any modern database like mssql, oracle, etc. it likely will not work with msaccess or mysql 3 or 4 as it uses a subselect in the where clause.
so,
select * from test27;
id value
10 1
11 1
16 1
17 1
12 2
14 2
15 2
13 3
SELECT test27.id, test27.value
FROM test27
WHERE test27.value <> (select distinct test27.value from test27 where test27.id = 16);
id value
12 2
14 2
15 2
13 3
explain extended SELECT test27.id, test27.value
FROM test27
WHERE test27.value <> (select distinct test27.value from test27 where test27.id = 16);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY test27 index test27_Index01 test27_Index01 258 8 Using where; Using index
2 SUBQUERY test27 const PRIMARY PRIMARY 4 1
Although this forum gimbles up the results above, you can see that this subselect is fairly efficient. I am sure a sql guru would have a more elegant solution that could scale to google size, but for your average app this will be work.
|
|
|
|
montymoose
Posts: 85
|
| Posted: 09/19/2007, 2:37 AM |
|
Thanks for your help, that code works great, but my full working project is slightly more complicated.
Considering the table below: (Using MYSQL latest version)
ID-------FIELD A-------FIELD B
1----------10------------1
2----------10------------2
3----------11------------1
4----------11------------2
5----------12------------3
6----------10------------4
If I pass '10' to field A via URL.
I want only row 5 to be returned:
ID-------FIELD A-------FIELD B
5----------12------------3
This is because row 5 is the only row where the value in field B is different from any rows where 10 is the value in Field A.
Example: Row 3 isn't returned because it's field B value of '1' is already used on a 'Field A = 10' row. See Row 1.
My brain hurts!
Thanks in advance...
M00s3
|
|
|
|
wkempees
|
| Posted: 09/19/2007, 2:51 AM |
|
Who's example did you use?
Or even better, what are you using right now.
Benjamins example:
"FieldB <> (Select FieldB from table where FieldA = " . CCGetParam("id", 0)
.. ")";
Is testing the for one value in the subselect.
That should based on your new table example fail with some message
like"multiple values returned"
which is in fact a good thing, you should ammend the test to "not in set"
like
"FieldB !in (Select FieldB from table where FieldA = " . CCGetParam("id",
0)
.. ")";
But posting your current source would be more helpful.
Walter
"montymoose" <montymoose@forum.codecharge> schreef in bericht
news:546f0edcc9d6e7@news.codecharge.com...
> Thanks for your help, that code works great, but my full working project
> is
> slightly more complicated.
>
> Considering the table below: (Using MYSQL latest
> version)
>
> ID-------FIELD A-------FIELD B
> 1----------10------------1
> 2----------10------------2
> 3----------11------------1
> 4----------11------------2
> 5----------12------------3
> 6----------10------------4
>
> If I pass '10' to field A via URL.
>
> I want only row 5 to be returned:
>
> ID-------FIELD A-------FIELD B
> 5----------12------------3
>
> This is because row 5 is the only row where the value in field B is
> different
> from any rows where 10 is the value in Field A.
>
> Example: Row 3 isn't returned because it's field B value of '1' is already
> used
> on a 'Field A = 10' row. See Row 1.
>
> My brain hurts!
>
> Thanks in advance...
>
> M00s3
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
montymoose
Posts: 85
|
| Posted: 09/19/2007, 3:26 AM |
|
Thanks wkempees,
I was using 'whiterabbitwond's' example and it does work:
SELECT *
FROM test27
WHERE test27.valueB <> (select distinct test27.valueB from test27 where test27.valueA = {city})
GROUP BY test27.valueB
But,
if you pass it a Value A which is used more than once it returns:
"Database Error: Subquery returns more than 1 row"
Thanks,
M00S3
|
|
|
|
montymoose
Posts: 85
|
| Posted: 09/19/2007, 3:26 AM |
|
PS: city is my URL call.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 09/19/2007, 8:25 AM |
|
SELECT *
FROM test27
WHERE test27.valueB NOT IN (select distinct test27.valueB from test27 where test27.valueA = {city})
GROUP BY test27.valueB
Should do the trick, both in a single and a multiple resultset.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 09/19/2007, 7:45 PM |
|
His original table did not show duplicate entire for field A, therefore my
query.
With the duplicates, yours is the correct one.
Did you get the email I sent you earlier in the week concerning the graphics
wrapper classes?
Have not heard back.
Updating docs right now.
|
|
|
|
wkempees
|
| Posted: 09/20/2007, 2:02 AM |
|
your query was ok, his info incomplete and changing from post to post.
Should be served now.
No E-mail received, but am away, will check.
Walter
"Benjamin Krajmalnik" <kraj@illumen.com> schreef in bericht
news:fcsmt7$g6u$1@news.codecharge.com...
> His original table did not show duplicate entire for field A, therefore my
> query.
> With the duplicates, yours is the correct one.
> Did you get the email I sent you earlier in the week concerning the
> graphics wrapper classes?
> Have not heard back.
> Updating docs right now.
>
|
|
|
|
montymoose
Posts: 85
|
| Posted: 09/21/2007, 3:39 AM |
|
Thanks, works fine now.
|
|
|
|
|
