when one uses a where clause like
Select ...
where ID IN (125,234,300)
It's a shorthand OR operation.
I am wondering if there's a similar way to do an AND? Or do we need to
simply use the usual syntax:
Select ...
where ID = 125 and ID = 234 and ID = 300
Is there a better/shorthand way to achieve multiple ANDs?No, not really. IN() is not just shorthand for OR predicates; you can
have a set in the parentheses that's returned from a subquery (like
"WHERE c1 IN (SELECT Foo FROM dbo.Bar) ...") - you can't do that with OR
predicates.
Often joins can produce the same behaviour that AND predicates can. For
example, if you had a table that had all those IDs in it, you could
simple do an INNER JOIN with your main table to achieve the same
affect. For example:
SELECT * FROM MyTable AS t
INNER JOIN MyIDTable AS i ON t.ID = i.ID
But this would require creating the table if it doesn't already exist
and then populating it. However, this approach can sometimes, in
specific cases, perform better than long strings of ANDs especially if
the query optimiser can take advantage of indexes that have been defined
on the tables.
*mike hodgson*
http://sqlnerd.blogspot.com
rsphere@.gmail.com wrote:
>when one uses a where clause like
>Select ...
>where ID IN (125,234,300)
>It's a shorthand OR operation.
>I am wondering if there's a similar way to do an AND? Or do we need to
>simply use the usual syntax:
>Select ...
>where ID = 125 and ID = 234 and ID = 300
>Is there a better/shorthand way to achieve multiple ANDs?
>
>|||If the column ID is an IDENTITY or UNIQUE column, then your logic will
return nothing: It is impossible a value in this column could be equal to
125 and also equals to 234...
If your real intention is the select records with its ID column's value is
125, OR 234, OR 300, it is perfect to use IN clause.
If your intention is to NOT select anything, then you can use "WHERE ID=125
AND ID=235...", but you may make it simpe: "SELECT...FROM...WHERE 1=2"
<rsphere@.gmail.com> wrote in message
news:1141689883.663017.174190@.v46g2000cwv.googlegroups.com...
> when one uses a where clause like
> Select ...
> where ID IN (125,234,300)
> It's a shorthand OR operation.
> I am wondering if there's a similar way to do an AND? Or do we need to
> simply use the usual syntax:
> Select ...
> where ID = 125 and ID = 234 and ID = 300
> Is there a better/shorthand way to achieve multiple ANDs?
>|||thanks for the replies.
the id column is not indentity - i should have specified that to be
more clear.
heres the query:
SELECT td.TestDescriptionID as tid, (td.TestName + td.ScoreType +
td.Category + td.SubCategory) as fulltestname FROM
TestDescription td INNER JOIN ReportFact rf ON
td.TestDescriptionID = rf.TestDescriptionID WHERE (rf.DistrictID =
@.DistrictId) and rf.CampusID in (767,795,942,768) and (rf.TotCount <>
0) GROUP BY td.TestDescriptionID, td.TestName, td.ScoreType,
td.Category, td.SubCategory order by fulltestname
What I need however is a list of only the tests that all campuses
(767,795,942,768) use. Not the total list of tests that any of the
campuses use.
Would AND still be best?
I am just worried about performance. The list is coming from a
dropdownlist - and they can select any number of items in the list.|||These are equivelent:
rf.CampusID in (767,795,942,768)
(rf.CampusID = 767 OR
rf.CampusID = 795 OR
rf.CampusID = 942 OR
rf.CampusID = 768)
NOTE THE PARENTHESES!! This set of ORs has to be in parentheses so it
can then be ANDed with the other criteria.
Personally I find the IN approach to be clearer and simpler, and it
will perform at least as well as the ORs.
Roy Harvey
Beacon Falls, CT
On 6 Mar 2006 17:12:20 -0800, rsphere@.gmail.com wrote:
>thanks for the replies.
>the id column is not indentity - i should have specified that to be
>more clear.
>heres the query:
>SELECT td.TestDescriptionID as tid, (td.TestName + td.ScoreType +
>td.Category + td.SubCategory) as fulltestname FROM
>TestDescription td INNER JOIN ReportFact rf ON
>td.TestDescriptionID = rf.TestDescriptionID WHERE (rf.DistrictID =
>@.DistrictId) and rf.CampusID in (767,795,942,768) and (rf.TotCount <>
>0) GROUP BY td.TestDescriptionID, td.TestName, td.ScoreType,
>td.Category, td.SubCategory order by fulltestname
>What I need however is a list of only the tests that all campuses
>(767,795,942,768) use. Not the total list of tests that any of the
>campuses use.
>Would AND still be best?
>I am just worried about performance. The list is coming from a
>dropdownlist - and they can select any number of items in the list.|||But i need AND functionality to reduce the set to only matches in all
campuses. I love the IN statement too - but was hoping that there was
something like it with AND behavior...but I gather there is not.|||Yes. The IN (SELECT x FROM Foobar) predicate is the same as
x = ANY(SELECT x FROM Foobar)
Likewise IN (<list> ) is the same as
x = ANY(VALUES <list> )
We also have the
x = ALL (SELECT x FROM Foobar)
or
x = ALL (VALUES <list> )
It is a part of SQL that a lot of newbies do not know about.|||I guess I just don't see what your problem is. If you really must
have AND, there is yet another alternative.
>These are equivelent:
>rf.CampusID in (767,795,942,768)
>(rf.CampusID = 767 OR
> rf.CampusID = 795 OR
> rf.CampusID = 942 OR
> rf.CampusID = 768)
This is the same as either of the above, for what that is worth.
rf.CampusID <> 767 AND
rf.CampusID <> 795 AND
rf.CampusID <> 942 AND
rf.CampusID <> 768
Roy Harvey
Beacon Falls, CT
On 6 Mar 2006 17:47:51 -0800, rsphere@.gmail.com wrote:
>But i need AND functionality to reduce the set to only matches in all
>campuses. I love the IN statement too - but was hoping that there was
>something like it with AND behavior...but I gather there is not.|||i guess I am not explaining it very well.
i was simply polling to see if there was a shorthand or efficient
alternative to using
rf.CampusID= 767 AND
rf.CampusID = 795 AND
...
Since I am in a situation where a user might select up to 40 or 50
items. That's a lot of ANDs... and may not perform well.
I am not a very experienced SQL programmer so I am not sure if I am
explaining this accurately.|||On 6 Mar 2006 18:23:18 -0800, rsphere@.gmail.com wrote:
>i guess I am not explaining it very well.
>i was simply polling to see if there was a shorthand or efficient
>alternative to using
>rf.CampusID= 767 AND
>rf.CampusID = 795 AND
It can never be true that:
rf.CampusID= 767 AND
rf.CampusID = 795
It can be equal to one, or the other, or neither, but it can NOT be
equal to both, and that test would only be true if it was equal to
both.
>Since I am in a situation where a user might select up to 40 or 50
>items. That's a lot of ANDs... and may not perform well.
Use IN. That is what it is there for. It is clearer than any of the
alternatives. It is as efficient as any alternative that will produce
the same results. The optimizer will see to that, you don't have to
second guess it. Concentrate on writting clear SQL that is correct.
Until you have done that it is futile to worry about efficiency. Once
you have done that, you usually find that it is efficient - or that
you need to look at indexing.
Good luck!
Roy Harvey
Beacon Falls, CT
Showing posts with label similar. Show all posts
Showing posts with label similar. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
If Else query for a complex data set
I am creating an application which uses logic similar to J.D.Edwards (for those of you are familiar with its wildcarding data structure).
Basically, a customer purchases a particular house, with a particular elevation, in a particular community. This data is stored in a Customer table. From the customer's selection (community, plan, elevation) criteria, I need to create a unique list of options from data stored in an optionmaster table.
A customer's data looks similar to this in the customer table:
CustomerID
CommunityID
Community Name
PlanID
Plan Name
ElevationID
ElevationName
1234567
7
Hickory Hills
25
Allen
3
C
The Optionmaster table is structured like this (there are actually about 1000 records):
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
0
+
0
+
0
+
100
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optional Dormer
0
+
25
Allen
2
B
50
9125
Optional Tub
8
Smithville
0
+
0
+
800
9125
Optional Kitchen
0
+
0
Lori
0
+
2500
First. Based on the customer's table I need to first select all options = to their community, as well as those available to communities everywhere (+). In some cases the same option is maintained for both at both levels. In that case the community specific option must be selected.
To do this I think I need an if else statement to select those records:
In this case, if CommID =7, select record, else select 0. This eliminates the other Smithville community and pulls the correct option # 1234.
The result would look like this:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
9125
Optinal Kitchen
0
+
0
Lori
0
+
2500
Second, from this dataset, I need to select all options equal to the plan ID, and those which apply to all plans (+). So, the Lori Plan data goes away.
Something like, If the plan ID = 25, select record, else 0. the result would be:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
Third and finally, based on that dataset, I would have to select any elevation specific options for that plan or options for all elevations of that plan (+).
Something like this: If the Elevation ID = 2, select record, else. 0. Note the elevation B data goes away.
The final result would be for this customer is:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
Any assistance anyone can provide in coming up with a sql statement to do this would be appreciated.
Something like, If the plan ID = 25, select record, else 0. the result would be:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
why did ooption #4567 stay here. the rest i follow.
|||4567 Stays because it is available in all communites (signified by the +). At the community level, a customer can choose options available (+) AND CommID 7 "Hickory Hills" options. In situations were there is are two options, as in 1234, the community specific one is chosen.
This same pattern goes on 2 more times.
Basically, it starts general, and gets specific.
|||Just one suggestion:?there?is?no?PK?in the Optionmaster table, which is useful for filtering rows in that table.?Not?a?good?table?desing. For performance consideration, I also suggest you add a PK:ALTER TABLE OptionMaster ADD UniqueID INT PRIMARY KEY IDENTITY(1,1)
And for your last step, I can't understand why 9101 goes away, as this row has ElevationID=2, which should be returned according to your rule. Anyways, try some query as following:
DECLARE @.PlanID int,@.ElevationID int
SELECT @.PlanID=25,@.ElevationID=2
SELECT o.*
FROM Optionmaster o ,Customers c
WHERE
(o.CommID=c.CommunityID
OR (o.CommID=0
AND NOT EXISTS (SELECT 1 FROM Optionmaster
WHERE Option#=o.Option#
AND CommID=c.CommunityID)))
AND (o.PlanID=@.PlanID OR (o.PlanID=0 AND o.PlanName ='+'))
AND (o.ElevationID=@.ElevationID OR (o.ElevationID=0 AND o.ElevationName='+'))|||
Thanks for the reply. There is actually a PK but I didn't show it.
Actually, I solved it a few minutes ago.
What I found out was the actual JD Edwards Option Master table has a column with a value (1-9 -- 1 being most specific, 9 being less specific). So all options are stored in 1 of 9 ways: (this is how it can be structured in their system).
So, basically, I when I run a query on all the following data:
Where the community = "Bed0000', Phase = "Two", Plan = "Shilo" and Elevation ="A", I get 4 records
I created a demo form to input the parameters. Then, I created a query to first select the relevant option data according to JDE's 9 levels. This gave me all the possible records. Then, I filtered the results based on the MIN value of Level (grouped on the option #). This gave me the specific records.
There's probably a better way of doing this, but I can't figure one out. I'd like to have it as a stored procedure but whenever I tried I kept running into problems declaring the variables. I don't know how much that will impact performance. I know it would be better to run on the server...I'll figure it out eventually, I'm still a bit new to SQL2005.
Here's what it looks like.
<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb"Inherits="_Default" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="DSSpecificOptionData" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT F44H401.ocoplvl, F44H401.ochbmcus, F44H401.occphase, F44H401.ochbplan, F44H401.ochbelev, F44H401.ocooption, F44H401.ocdlo1, F44H401.occspr FROM F44H401 INNER JOIN [Find_Lowest_Level] ON F44H401.ocoplvl = [Find_Lowest_Level].[Level] AND F44H401.ocooption = [Find_Lowest_Level].ocooption WHERE (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev ='+') AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+')"> <SelectParameters> <asp:ControlParameter ControlID="TBComm" Name="ochbmcus" PropertyName="Text" /> <asp:ControlParameter ControlID="TBPlan" Name="ochbplan" PropertyName="Text" /> <asp:ControlParameter ControlID="TBElev" Name="ochbelev" PropertyName="Text" /> <asp:ControlParameter ControlID="TBPhas" Name="occphase" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource> <br /> <table style="position: static"> <tr> <td style="width: 100px"> Community "bed0000"</td> <td style="width: 100px"> <asp:TextBox ID="TBComm" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Phase "two"</td> <td style="width: 100px"> <asp:TextBox ID="TBPhas" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Plan "shilo"</td> <td style="width: 100px"> <asp:TextBox ID="TBPlan" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Elev "a"</td> <td style="width: 100px"> <asp:TextBox ID="TBElev" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> <asp:Button ID="Button1" runat="server" Style="position: static" Text="Search" /></td> <td style="width: 100px"> </td> </tr> </table> <br /> <span style="color: #0000ff">Results with most specific records:</span><br /> <asp:DataList ID="DataList1" runat="server" DataSourceID="DSSpecificOptionData" Style="position: static"> <ItemTemplate> <table style="position: static"> <tr> <td style="width: 100px"> <asp:Label ID="ocoplvlLabel" runat="server" Style="position: static" Text='<%# Eval("ocoplvl") %>' Width="19px"></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbmcusLabel" runat="server" Style="position: static" Text='<%# Eval("ochbmcus") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="occphaseLabel" runat="server" Style="position: static" Text='<%# Eval("occphase") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbplanLabel" runat="server" Style="position: static" Text='<%# Eval("ochbplan") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbelevLabel" runat="server" Style="position: static" Text='<%# Eval("ochbelev") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ocooptionLabel" runat="server" Style="position: static" Text='<%# Eval("ocooption") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ocdlo1Label" runat="server" Style="position: static" Text='<%# Eval("ocdlo1") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="occsprLabel" runat="server" Style="position: static" Text='<%# Eval("occspr") %>'></asp:Label></td> </tr> </table> </ItemTemplate> <HeaderTemplate> <table style="position: static"> <tr> <td style="width: 100px"> ocoplvl: </td> <td style="width: 100px"> ochbmcus:</td> <td style="width: 100px"> occphase:</td> <td style="width: 100px"> ochbplan: </td> <td style="width: 100px"> ochbelev:</td> <td style="width: 100px"> ocooption:</td> <td style="width: 100px"> ocdlo1:</td> <td style="width: 100px"> occspr:</td> </tr> </table> </HeaderTemplate> </asp:DataList></div> <span style="color: #0000ff"><br /> </span> <br /> </form></body></html> |||Oh that's great, I'm glad to see you've solved the problem. Really thank you very much for sharing your experience with us!Cheers,sql
Subscribe to:
Posts (Atom)