Assistance building a query...

I am trying to generate some datasets with some queries...

With a given series information, it should return PART_NOs that has STD
= 1 and a unique price at that particular 'START', and keeping the
'TYPE' in consideration...

DB examples below:

Main DB

IDPART_NOSERIESSTD
1A-1A1
2A-2A1
3A-3A1
4D-1D1
5D-2D0

Price DB

IDPART_IDTYPESTARTPRICE
501X100050
511X1000040
521Y100060
531Y1000050
542X100050
552X1000040
562Y100060
572Y1000050
582X100090

etc.

main.ID and Price.PART_ID are paired together.

So in an example case, lets say I am querying for SERIES A, with TYPE
X. A table should be outputted something like

PART_NO
A-1100050
A-11000040
A-3100090

Note how it skipped printing A2 because the price is the same as A1.

I'm really looking for the SQL code here... I can't get it to filter on
distinct price.

SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
FROM MAIN, PRICING
WHERE (MAIN.SERIES LIKE 'A')
AND (MAIN.STD = '1')
AND (PRICING.PRICE != '')
AND (PRICING.TYPE = 'X')
AND (MAIN.ID = PRICING.PART_ID)

I've been trying to use GROUP BY and HAVING to get what I need but it
doesn't seem to fit the bill. I guess I'm not terribly clear on how I
can use the SQL DISTINCT command...? If I try and use it in my WHERE
statement it gives me syntax errors, from what I understand you can
only have distinct in the select statement? I'm not sure how to
integrate that into the query to suit my needs.

Thanks for any help.A bit of clarification on my problem

If I just do a straight SQL distinct on my select statement, it does
what I want when you get down to it, but it completely destroys the
organization of the table. The part numbers were entered in a certain
manner and I do not believe they can be reorganized through any typical
sort. For example A-105A is higher then A-400B, but if you sorted in
Excel (for example) it would put 105 below 400

mazzarin@.gmail.com wrote:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
> = 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> IDPART_NOSERIESSTD
> 1A-1A1
> 2A-2A1
> 3A-3A1
> 4D-1D1
> 5D-2D0
> Price DB
> IDPART_IDTYPESTARTPRICE
> 501X100050
> 511X1000040
> 521Y100060
> 531Y1000050
> 542X100050
> 552X1000040
> 562Y100060
> 572Y1000050
> 582X100090
> etc.
> main.ID and Price.PART_ID are paired together.
>
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1100050
> A-11000040
> A-3100090
> Note how it skipped printing A2 because the price is the same as A1.
>
> I'm really looking for the SQL code here... I can't get it to filter on
> distinct price.
> SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> FROM MAIN, PRICING
> WHERE (MAIN.SERIES LIKE 'A')
> AND (MAIN.STD = '1')
> AND (PRICING.PRICE != '')
> AND (PRICING.TYPE = 'X')
> AND (MAIN.ID = PRICING.PART_ID)
> I've been trying to use GROUP BY and HAVING to get what I need but it
> doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> can use the SQL DISTINCT command...? If I try and use it in my WHERE
> statement it gives me syntax errors, from what I understand you can
> only have distinct in the select statement? I'm not sure how to
> integrate that into the query to suit my needs.
> Thanks for any help.|||Actually never mind, it doesn't do exactly what I want it to do... All
the prices are still duplicated, ideally I should only have at most 3
results being returned (according to the actual data being fed in)

I am beyond confused heh

I think I might have to do the filtering outside of SQL

mazzarin@.gmail.com wrote:
> A bit of clarification on my problem
> If I just do a straight SQL distinct on my select statement, it does
> what I want when you get down to it, but it completely destroys the
> organization of the table. The part numbers were entered in a certain
> manner and I do not believe they can be reorganized through any typical
> sort. For example A-105A is higher then A-400B, but if you sorted in
> Excel (for example) it would put 105 below 400
>
> mazzarin@.gmail.com wrote:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> > = 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > IDPART_NOSERIESSTD
> > 1A-1A1
> > 2A-2A1
> > 3A-3A1
> > 4D-1D1
> > 5D-2D0
> > Price DB
> > IDPART_IDTYPESTARTPRICE
> > 501X100050
> > 511X1000040
> > 521Y100060
> > 531Y1000050
> > 542X100050
> > 552X1000040
> > 562Y100060
> > 572Y1000050
> > 582X100090
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1100050
> > A-11000040
> > A-3100090
> > Note how it skipped printing A2 because the price is the same as A1.
> > I'm really looking for the SQL code here... I can't get it to filter on
> > distinct price.
> > SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> > FROM MAIN, PRICING
> > WHERE (MAIN.SERIES LIKE 'A')
> > AND (MAIN.STD = '1')
> > AND (PRICING.PRICE != '')
> > AND (PRICING.TYPE = 'X')
> > AND (MAIN.ID = PRICING.PART_ID)
> > I've been trying to use GROUP BY and HAVING to get what I need but it
> > doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> > can use the SQL DISTINCT command...? If I try and use it in my WHERE
> > statement it gives me syntax errors, from what I understand you can
> > only have distinct in the select statement? I'm not sure how to
> > integrate that into the query to suit my needs.
> > Thanks for any help.|||(mazzarin@.gmail.com) writes:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
>= 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> ID PART_NO SERIES STD
> 1 A-1 A 1
> 2 A-2 A 1
> 3 A-3 A 1
> 4 D-1 D 1
> 5 D-2 D 0
> Price DB
> ID PART_ID TYPE START PRICE
> 50 1 X 1000 50
> 51 1 X 10000 40
> 52 1 Y 1000 60
> 53 1 Y 10000 50
> 54 2 X 1000 50
> 55 2 X 10000 40
> 56 2 Y 1000 60
> 57 2 Y 10000 50
> 58 2 X 1000 90
> etc.
> main.ID and Price.PART_ID are paired together.
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1 1000 50
> A-1 10000 40
> A-3 1000 90
> Note how it skipped printing A2 because the price is the same as A1.

But why does A-3 appear? Ir does not seem to appear in the Price DB
at all?

If there is an A-4 with the same values as A-1 would that be printed?

I'm sorry, but as you have presented the problem there are two many
unknowns. Furthermore, there is a standard recommendation that you include
in your post:

o CREATE TABLE statements for you table(s).
o INSERT statemetns with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer
to play around with the data and develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I actually ended up doing multiple queries, passing info between VBA
and SQL. Its not as nice of a solution but it works. Thank you for your
advice though. I will keep it in mind next time!

Erland Sommarskog wrote:
> (mazzarin@.gmail.com) writes:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> >= 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > ID PART_NO SERIES STD
> > 1 A-1 A 1
> > 2 A-2 A 1
> > 3 A-3 A 1
> > 4 D-1 D 1
> > 5 D-2 D 0
> > Price DB
> > ID PART_ID TYPE START PRICE
> > 50 1 X 1000 50
> > 51 1 X 10000 40
> > 52 1 Y 1000 60
> > 53 1 Y 10000 50
> > 54 2 X 1000 50
> > 55 2 X 10000 40
> > 56 2 Y 1000 60
> > 57 2 Y 10000 50
> > 58 2 X 1000 90
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1 1000 50
> > A-1 10000 40
> > A-3 1000 90
> > Note how it skipped printing A2 because the price is the same as A1.
> But why does A-3 appear? Ir does not seem to appear in the Price DB
> at all?
> If there is an A-4 with the same values as A-1 would that be printed?
> I'm sorry, but as you have presented the problem there are two many
> unknowns. Furthermore, there is a standard recommendation that you include
> in your post:
> o CREATE TABLE statements for you table(s).
> o INSERT statemetns with sample data.
> o The desired result given the sample.
> This makes it possible to easily copy and paste into Query Analyzer
> to play around with the data and develop a tested solution.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Aspx fashion tip... ;)

Hi!

Im building a site for internal use for a company i work at.
And im having hard to decide what to do. The alternatives are

comma separated lists vs separate tables.

For example.. I will use a number of different type of objekt that all will hold 1 or more document links, phone numbers, emails etc

As far as i can i i have 2 possibilitys. One is to add textfields for each of these subojects in each objects tables. Then i add all info as commaseparrated lists in 2-3 layers wich i then save in the respecive objects fields.
(Look like something below for example )
,comment'emailadress,comment'emailadress,

Or i add 1 table for each subobject type wich manage the subobjects or link between different objects
for example:
table layout for email:
id | parent_type | parent_id | comment | email

Or for example contacts a mediator table:
id | parent_type | parent_id | contact_id

Hope you understand what i mean, would really appriechiate suggestions, comments regarding what can be considered as "best practise" in these cases. Putting it in separate tables should make searches easier, and coding easier in my book. But again im thinking if perhaps commalists is a faster and more recource conservative way of doing it + to me it feels like (perhaps i have no reason to think this?) making tables for items like feels like a timebomb if you exhaust your idfields identity count. Though this app will be used by perhaps 100ppl at most so perhaps not an extremely utilized webapp.

Anyway glad if you could give me some input!

I prefer seperate tables, as this gives clearer description of data structure/relationship, and it is much easier to manipulate the data in seperate tables.|||

Yeah well separate tables sure is the easiest way to get around it considering searches, changes when built etc.

Practically for me it will mean perhaps 3-5 querys in one page instead 1-2. So that is the main drawback i guess wich perhaps wont inflict to much.

Anyway my main concern was if there was anything that motivated commalists or its just a small gain for quite a bit more work in creating/changing.

|||

"if you exhaust your idfields identity count"? If you were inserting a new record once every second non-stop, it'd take over 68 years to exceed the limits of an int. And when you get there, consider upgrading to SQL Server 2050 which solves that problem automatically for you. But if you think there is the possability that your app will need to run for 68+ years without a change, or if your think you'll need to insert more than 86,000 rows per day, then you might consider a bigint.

|||

Yeah did som calculations and came to the same conclusion ;)

Anyway think i will go for tables, for convineance sake.

ASPNETDB.MDF - need to use SQL Server 2000

Hi,

I'm building an intranet app for a client using ASP.NET 2. The client is running SQL Server 2000 - and does not have plans to upgrade to 2005 anytime soon.

Is there a script that I can use to create the objects in the ASPNETDB database so that I can do this in SQL Server 2000?

Also, what additional changes would I have to make in order for the application to point to a SQL Server 2000 database with these objects?

Thanks in advance, Al

MDF(Microsoft data file) is only one half of a SQL Server database, the other half is the LDF(Log data file). That said the Personnal and Club starter kits comes with the membership database and Microsoft created a SQL Server 2000 version of both so I think you can install both take the membership related tables, triggers and stored procs. You know it is all manual so you just right click on the object in Object browser in Query Analyzer and click on create to generate the scripts. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyId=0DD83A11-6980-4951-A192-DA6EACC6A19E&displaylang=en

http://www.microsoft.com/downloads/details.aspx?FamilyId=2EE85ED4-7613-47E2-8375-17222B150E4F&displaylang=en

|||Hi luckydog,

as alternative in clean way, you can run aspnet_regsql in command prompt and choose any database instance that you want to have membership tables inside it.

Run it in .Net Framework SDK Command Prompt