Re: get into big problem-need help - Database Discussion Boards

aligatojohn1-Jul-06 0:10

1-Jul-06 0:10

I have two table with one-many relationship
table1 has 2 fields:type(int,PK),name
table2 has 4 fields:id(PK),type(int,FK reference field type of table1),content,shortDesc

How to write a SELECT statement that return rows from table1 join table2(via type field) but each row has a different value of type.
Result example:
--------------------------------------------------
| table1.type | name | content | ShortDesc | <--- FIELDS NAME
--------------------------------------------------
| type1 | name1 | content1| shortDesc1 | <---VALUE( ROW )
--------------------------------------------------
| type2 | name2 content2| shortDesc2|

Can anyone help me.Thanks in advance

Re: get into big problem-need help

Colin Angus Mackay1-Jul-06 1:21

Colin Angus Mackay

1-Jul-06 1:21

SELECT table1.type, table1.name, table2.content, table2.shortDesc
FROM table1
INNER JOIN table2 ON table1.type = table2.type

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: get into big problem-need help

aligatojohn1-Jul-06 18:51

aligatojohn

1-Jul-06 18:51

Sorry but the statement don't return what i expected
Example:
type1 |Id1 | name1...
type1 |Id2 |name2...
because there's a one-many relationship between 2 table
Thanks anyway

Re: get into big problem-need help

Colin Angus Mackay1-Jul-06 23:05

Colin Angus Mackay

1-Jul-06 23:05

ali33 wrote:
Sorry but the statement don't return what i expected

However, it does return what you asked for.

You asked for: | table1.type | name | content | ShortDesc |

The SELECT statement returns: table1.type, table1.name, table2.content, table2.shortDesc
name only exists on table1, content and shortDesc only exist on table2

You are now asking for something with an example output of:
type1 |Id1 | name1...
type1 |Id2 |name2...

This is not possible because name is on table1. Therefore any output row that contains type1 must also contain name1. An output row containing type1 cannot contain name2

ali33 wrote:
because there's a one-many relationship between 2 table

Yes, I know that - That is what I gave you. I suspect that either you did not give the full information when you asked the question or you do not understand the implications of the join scenario.

Re: get into big problem-need help

aligatojohn2-Jul-06 1:32

aligatojohn

2-Jul-06 1:32

oh!sorry . I have a mistake

When I run your SQL statement the result return is:

type1 |Id1 | name1...
type1 |Id2 |name1... <---(sorry not name2)
But the result I expected is:

type1| Id1 |name1...
type2| id2 |name2...
type3| Id3 |name3....
(each type return only one ID of table 2,for all type)
Sorry for the mistake and can you help me.Thanks

Re: get into big problem-need help

Colin Angus Mackay2-Jul-06 1:42

Colin Angus Mackay

2-Jul-06 1:42

ali33 wrote:
each type return only one ID of table 2,for all type

Sorry, but I'm still convinced my SQL is correct.

Show me an example of a few rows from each table that should match and tell me for each row in table1 what rows in table2 match to it.

Re: get into big problem-need help

aligatojohn3-Jul-06 0:16

aligatojohn

3-Jul-06 0:16

Colin Angus Mackay wrote:
Sorry, but I'm still convinced my SQL is correct.

Show me an example of a few rows from each table that should match and tell me for each row in table1 what rows in table2 match to it.

here is an example of some rows from each table:
table1:
|type |name |
----------------------------
| 1 | type1 |
----------------------------
| 2 | type2 |

table2:
|id |type | content | shortDesc |
---------------------------------------------
|1 | 1 | content1 | desc 1 |
|2 | 1 | content2 | desc 2 |
|3 | 2 | content3 | desc3 |
|4 | 2 | content4 | desc4 |
|5 | 1 | content5 | desc5 |

The expected result :
|id | table1.type | name |content | shortDesc |
-------------------------------------------------
| 1 | 1 | type1|content1 | desc1 |
| 3 | 2 | type3|content3 |desc3 |

each type returns only one row.
So now can you help me.If you need,i 'll send you my database and the description more clearly.
Thanks.

Re: get into big problem-need help

Colin Angus Mackay3-Jul-06 1:51

Colin Angus Mackay

3-Jul-06 1:51

I see now. You need to aggregate the rows in table2 somehow then join it. However there is no natural aggregation that you can use. From your example result set you appear to be taking the lowest id for each type - so that is the colum that needs to be aggregated.

This is the aggregation component:

SELECT MIN(id) AS id, type
FROM table2
GROUP BY type

It basically takes the first row for each type and discards the rest.

Now it has to be joined up. The result of the aggregation is added to the previous join to act as a filter.

SELECT table1.type, table1.name, table2.content, table2.shortDesc
FROM table1
INNER JOIN table2 ON table1.type = table2.type
INNER JOIN (SELECT MIN(id) AS id, type
            FROM table2
            GROUP BY type) AS filterQuery ON filterQuery.id = table2.id

This should now produce the results that you want.

Re: get into big problem-need help

aligatojohn3-Jul-06 20:49

aligatojohn

3-Jul-06 20:49

wonderfull!It run perfectly.Thanks Colin Angus Mackay
Best wishes!

How 2 find 4th working day of any month using PL/SQL?

Jazzy_J30-Jun-06 23:55

Jazzy_J

30-Jun-06 23:55

Hi ALL,

How to find 4th working day(any for tht matter) of
any month...If for ex. For June 2006, the 4th working day is
6th June. So, i need to write a PL/SQL(or even SQL Query is also fine) to obtain tht particular day.

Plz do the needful.

Regs,
Jazzy

Re: How 2 find 4th working day of any month using PL/SQL?

Paul Conrad1-Jul-06 17:53

Paul Conrad

1-Jul-06 17:53

Getting Data from Excel to SQL Server..

GBal30-Jun-06 23:33

GBal

30-Jun-06 23:33

Hi,
Im trying to get the records from excel sheet to sql server table.
pls help me..getting some errors..

--------------------------------------------
@file_name varchar(255)

as

begin
--select @file_name = DOCUMENT_PATH from DOCUMENT
SELECT @file_name =(' \\ZSW125\AON\BatchProcessing;HDR=YES ')

--Get the excel filename & path to be imported from the reference table

declare @strsql varchar(500)

set @strsql = 'select * from openrowset(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=' + @file_name + ';HDR=YES'', ''" Smile | :)

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database='\\ZSW125\AON\BatchProcessing;HDR=YES','')

Please Help me for finding a word from field in SQL

Jesal Rana30-Jun-06 19:32

Jesal Rana

30-Jun-06 19:32

Hi,

Please anyone can help me for finding a word from a particular field from table in database of SQL.

I have the problem below.

DataTable Name: TempTable
Field name: Dtail
other field name: KeyWords

in the Detail field i have data like:
<--Begin-->
(TenderProcess <> 'Sell' and (State in ('Gujarat') and (ProductName in ('Iso-9002 Consultancy','Audit') or ProductDetails like '% ISO %' or ProductDetails like '% ISO- %' or ProductDetails like '% ISO - %')))
<--End-->
Above is the data in field of Detail Now my Question is:
I want to get words if it found the word 'Product Detail like%%'in any record.
Then for above ex. if I found the word it give
output:ISO,ISO-,ISO -
------------------------------------------------------------
So from above ex. I how I get the output as above.

Please help me.Reply me urgently.

Jesal

OK

Re: Please Help me for finding a word from field in SQL

Paul Conrad30-Jun-06 19:52

Paul Conrad

30-Jun-06 19:52

CLR Integration and SQL Express XCOPY deployment hell

Furty30-Jun-06 16:23

Furty

30-Jun-06 16:23

I originally developed my application using SQL 2005 Developer Edition, but want to switch to using an XCOPY deployed DB on SQL Express for deployement.

I have successfully copied the database.mdf/ldf files over to me project, and can connect using Data Source=.\SQLExpress and AttachDbFilename=|DataDirectory|\[database].mdf attributes.

The question is, how do I enable CLR integration for my C# SP's?

I've tried executing the following in various places:

sp_configure 'clr enabled', 1
go
reconfigure
go

But I obviously haven't hit the spot because I'm getting the following error when VS deploys my SP library:

Error: starting database upload transaction failed.
Error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Any ideas?

Create composite key on existing table

leckey30-Jun-06 9:27

leckey

30-Jun-06 9:27

I have an existing SQL table with no constraints. The PartID and DescriptionPartID need to be a composite key. I check and there are currently no duplicates in the existing table. Can I create the key now by doing the following:

UPDATE TABLE Descriptions
PRIMARY KEY (PartID, DescriptionPartID)

Thanks!

Re: Create composite key on existing table

Colin Angus Mackay30-Jun-06 10:22

Colin Angus Mackay

30-Jun-06 10:22

Not quite - You would use ALTER TABLE[^]. Something like this:

ALTER TABLE Descriptions
ADD CONSTRAINT PK_Descriptions PRIMARY KEY CLUSTERED (PartID, DescriptionPartID)

Re: Create composite key on existing table

leckey30-Jun-06 10:31

leckey

30-Jun-06 10:31

As always, Thanks man! Big Grin | :-D

IBM DB2 SQL Error Code to String

Christopher Stratmann30-Jun-06 2:55

Christopher Stratmann

30-Jun-06 2:55

In DB2 sometimes error messages are generated and returned in the sqlca.sqlcode variable in C++. To find out the string representation of this number I have to go to http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp?topic=/com.ibm.db2v7.doc/db2m0/sql0000.htm[^]. Is there function to return the error string represented by this number so I dont have to go to this web site each time.

I have been researching this for sometime and I found a C++ APIs on this web site http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.ii.doc/ad/rwrutl01.htm[^]. I didnt find any function to do this but it might help.

Another thing. If this "method" could be language specific that would be great too (meaning if English is defined return an english string or if German is defined return the german string).

Thanks Chris

Laoding the whole database tables into a DataSet

hamidreza_buddy30-Jun-06 0:43

hamidreza_buddy

30-Jun-06 0:43

hi guys
how should I load a whole database into a Dataset object.
when loading from a DataAdapter i should use an SQL command. what if i want to load all tabless and reltions and ... in a Dataset?
i want to make a program to load all sort of databases (even without knowing the name of the tables)
thx guys

Re: Laoding the whole database tables into a DataSet [modified]

Colin Angus Mackay30-Jun-06 1:02

Colin Angus Mackay

30-Jun-06 1:02

hamidreza_buddy wrote:
how should I load a whole database into a Dataset object.

Hopefully it is a small database. The last database I worked on was 150Gb when I left the project and growing at roughly 1Gb per week. That would never have fit in a DataSet.

The other question is: Why would you want to?

hamidreza_buddy wrote:
when loading from a DataAdapter i should use an SQL command. what if i want to load all tabless and reltions and ... in a Dataset?

Your SqlCommand would consist of:

SELECT * FROM TableA; SELECT * FROM TableB; SELECT * FROM TableC; -- etc...

hamidreza_buddy wrote:
i want to make a program to load all sort of databases (even without knowing the name of the tables)

You need to know the names of the tables to put in the SqlCommand. However, you can get this information:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

Re: Laoding the whole database tables into a DataSet

hamidreza_buddy1-Jul-06 0:53

hamidreza_buddy

1-Jul-06 0:53

thx
we want to make a Data Mining Application.
unfortunately i am a newbee in databases and i dont know the strategies for making such an application.
i really need ur suggestions.
how should i do proccess on databases.
* should i store them in a DataSet or DataReader or what?
as i said it should load every database without knowing name of tables.
waiting for ur strategies.
thx again

Re: Laoding the whole database tables into a DataSet

Colin Angus Mackay1-Jul-06 1:15

Colin Angus Mackay

1-Jul-06 1:15

hamidreza_buddy wrote:
we want to make a Data Mining Application.

Then leave the data in the database and only ever get what you need at that moment.

Data mining applications usually deal with vast quantities of data, much more than the RAM+swap space of a server, let alone a regular desktop.

Database engines are optimised to do vastly complex "set based" calculations on the data in a database. A DataSet can manage only small fraction of this. DataSets a bebeficial when a small amount of data is repeatedly queried - in this case the performance of the dataset out weighs the round trip cost of performing the query on the server.

Set based operations are those which involve multiple rows of data (a set of data). They are expecially good at joining sets of data together. (If you want to delve really deep into this area they you might like to read up on set theory in mathematics)

hamidreza_buddy wrote:
how should i do proccess on databases

Let the database do the work and you pull across only the answers.

hamidreza_buddy wrote:
should i store them in a DataSet or DataReader or what?

A DataReader is not a storage mechanism. A DataReader is a mechanism for reading data (hence the name DataReader) from a database.

I, personally, don't like DataSets because they are bulky and I can get better results by creating my own business objects. In .NET 2.0 the typed datasets are supposed to be much better, but I've not yet looked at them.

hamidreza_buddy wrote:
i said it should load every database without knowing name of tables.

I already gave you the query you should use to obtain a list of table names. You need to know the name of the table in order to refer to it.

hamidreza_buddy wrote:
waiting for ur strategies

I have a pathological hatred for the use or "ur" the word is "your". The same goes for "thx" - THX is a sound quality assurance system that means that if you see a movie in a THX certified theater it will sound exactly as it did in the mixing studio.

On the subject of strategies - There are lots out there, but for a data mining application I don't thing a DataSet will feature much for the mining aspect. Maybe only for helping you bind data to the controls (should you wish to go that route)

Please help me for finding a word from a field of database

Jesal Rana29-Jun-06 23:22

Jesal Rana

29-Jun-06 23:22

Hi,
Please any one can help me for how I find a word from a paricular field from database in SQL

Please reply urgently.

Regards,

Jesal Rana

OK

Re: Please help me for finding a word from a field of database

Paddy Boyd29-Jun-06 23:35

Paddy Boyd

29-Jun-06 23:35

select a from table where a like '%word%'

Or set up a full text index and use the CONTAINS predicate.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.