|
Hi
I also tried pulling the SQL tables into Access and running the same query, but had to change the line:
max(poh.date_entered) last_date
to
max(poh.date_entered) AS last_date ????
But then it returns many records for each product, wheras I only want to return ONE record for EVERY product, and that record must be the once which has the latest date
Maybe I explained what I'm trying to do badly, so if you get a chance, perhaps look at my reply to Kschuler's post as I try to explain what I want in more detail...
|
|
|
|
|
My previous SQL would return too many rows. Try this instead.
select t.product
,t.last_date
,pod.local_expect_cost
from ( select pod.product
,max(poh.date_entered) last_date
from poheadm poh
,podetm pod
where poh.order_no = pod.order_no
group by pod.product ) t
,poheadm poh
,podetm pod
where poh.date_entered = t.last_date
and poh.order_no = pod.order_no
and pod.product = t.product
As long as your order header records have unique date time stamps, you should only get one record for each product. But this assumes that on the podetm table that the order_no, product fields are a unique key. If they are not a unique key, it possible that a product would appear twice.
Good Luck.
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|
Hi Chris
That did not work either, I got multiple results for each product.
One entry in the PoHeadm table can have many line Items in Podetm table
The same product can appear many times in the podetm table, but each with a different order_no, and hence a different date_entered in the header table.
I tried to write a statement in English stating what I want, hoping that I could develop that into an SQL statement, but even that proved difficult.
Select each item from podetm, and return the product and cost of the order number that has the latest date_entered in the poheadm table????
Maybe if you have time to spend, you could try to write a statement based on the data below?
Header Table: Poheadm
Fields: |date_entered |order_no|
Data: |2007-05-28 |00001 |
Data: |2007-05-29 |00002 |
Detail Table: podetm
Fields: |order_no |local_expec_cost |product|
Data: |00001 | USD5.06 |AA01 |
Data: |00001 | USD1.00 |AA02 |
Data: |00001 | USD9.00 |AA03 |
Data: |00002 | USD7.00 |AA01 |
Data: |00002 | USD2.00 |AA04 |
So above we have TWO Purchase orders. Order 00001, has 3 line items. Order two has 2 line items.
As you can see, product AA01 is on TWO purchase orders, I want to create a list of EACH of the products in podetm, and I want the LAST price paid based on the date_entered field in the poheadm table. So for the above, the data I want returned would look as follows (each product is ONLY ONCE, and has the latest price and date):
|product |local_expect_cost |date_enetered|
|AA01 |USD7.00 |2007-05-29 | (Data from Latest Date Order2)
|AA02 |USD1.00 |2007-05-28 | (Only Ordered once)
|AA03 |USD9.00 |2007-05-29 |
|AA04 |USD2.00 |2007-05-29 |
|
|
|
|
|
These are the settings I've set for a one-to-many relationship between 'order' and 'orderItem' tables:
Enforce Foreign Key Constraint: No
Delete Rule: Set Default
Update Rule: No Action
I've set 'Enforce Foreign Key Constraint' to 'No' so that I can delete an order that has some orderItems.
The orderItem table contains orderId field as the foreign key. Now I want this foreign key to be set to its default value (0) whenever I delete an order record. The problem is whenever I delete an order, the orderId value in the corresponding records in orderItem table remain intact and sustain their orderId value which has been deleted. Do you know what's wrong with that? I'm confused!
|
|
|
|
|
I think I've figured out what's going on. The problem is that I should set Enforce Foreign Key Constraint to Yes for this to work. And, Default Value of orderId should not be (0), it should be something that already exists in order table.
|
|
|
|
|
I have 2 databases with over 7 million records where about half are probably duplicated. I would like to merge these tables and take out the duplicates. The problem comes in where the the only fields are name address and email address(sometimes). So i have to do a dup check on the name and address. The way im doing it is taking 5 min for every 1000 records.
I tried writing a stored procedure that checked each one with a if exists statement then insert. I know there has to be a better way than that. ANy help or am i stuck waiting a couple weeks to be done.
By the way the cpu i'm using is also using 85% cpu to do the checking.
|
|
|
|
|
The answer to your question would depend on where your duplicates can exist. Are there duplicates inside of the individual databases or only between the two databases? If it is the latter you can use a strategy like this:
1) Copy over 1 table to a new table.
2) The new table should have indexes on Name & Address (to avoid mulitple table scans). For speed you can create the indexes after the copy.
3) Create an insert statement qualified by the non-duplicates.
INSERT INTO [NewTable]
(Name, Address, Email, etc...)
SELECT
s.Name,
s.Address,
s.Email,
s.etc...
FROM
[2ndTable] s
LEFT JOIN
[NewTable] n
ON ((s.Name = n.Name)&&(s.Address = n.Address))
WHERE
n.Name IS NULL
|
|
|
|
|
I would like to setup a website where people can purchase products online. I need help figuring out how the money transaction part done.
|
|
|
|
|
I had to know that in Ms SQL database we cannot able grant permission of one particular table in a database to another user but we can have it in Oracle.
Is it true?
Ahamed Azeem
|
|
|
|
|
Try grant select on MyTable to AnotherUser. Its exactly the same as with Oracle.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Ok, once again I need some help with replication.
Due to a limit of 5-Transactional Subscription on SQL Server 2005 Workgroup edition I want to make use of a "uni-directional" Merge Publication instead, simply because those subscriptions are only limited to 25, which will suffice for now.
Question: Can I create a merge-replication from SQL 2005 >to> SQL 2000?
What I've tried:
When creating the publication I get an option to set the publication's compatibility to include SQL 2000 servers, but as soon as I try to add an article SQL fails with an error reporting that my publication's compatibility level should be at leat 90RTM and that I should run sp_changemergepublication.
After running that SP, I can successfully add articles but I cannot subscribe any of our SQL 2000 servers.
I'm guessing the article itself should somehow be set to be compatible... but that's only a guess.
_______________________________________________________________________
http://www.readytogiveup.com/[ ^]
"you can't forget something you never knew..." M. Du Toit
|
|
|
|
|
Got it, answering myself for the benefit of googlers.
It seems that SQL 2000 doesn't support Merge Replications with update options.
When creating a publication in SQL 2005 you can choose the following update options (A.k.a Synchronization direction):
* Bidirectional
* Download-only to Subscriber, allow Subscriber changes
* Download-only to Subscriber, prohibit Subscriber changes
As far as I can tell, when replicating to SQL 2000, you can only choose the bidirectional option because publications with a compatibility-level lower than 90 cannot use the 'subscriber_upload_option'.
Cheers
_______________________________________________________________________
http://www.readytogiveup.com/[ ^]
"you can't forget something you never knew..." M. Du Toit
|
|
|
|
|
i had a datatable in memory and i want to change the datatype of one of it's column
help or hint required ?
|
|
|
|
|
ALTER TABLE TableName ALTER COLUMN ColumnName varchar(255)
--change datatype instead of varchar
I Love SQL
|
|
|
|
|
HI
I am having problems with the join i need to do to get the required result. This is the problem: I have two tables one with user data and one with user profile image. I need to join these tables to get each user with his profile image (user might have many profile pictures but only one is set to be default=1). But some users might not have profile images uploaded yet. So the problem wen i do left join i get an entry with users having no profile images and also i get TWO entries for the same users that have a profile image : one with the right answer and one with an empty image. But i want my join to select empty image ONLY wen the user has no image uploaded. Any help??
/\|-||\/|/\|)
|
|
|
|
|
I don't understand how unless you actually have a placeholder row for an empty image. If that is the case then it should be simple to add something into the WHERE clause to filter that out.
You might want to post the relevent parts of the datamodel and the query you are using. It might make it easier to visualise your actual situation.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
SELECT C.UserID
FROM Contact AS C
LEFT JOIN Album AS A ON A.UserID = C.UserID AND A.Type = 1
LEFT JOIN AlbumImage AS AI ON AI.AlbumID = A.AlbumID AND AI.'Default' = 1
Contact:
UserID pk
...
one to many
Album:
AlbumID pk
UserID fk
Type
...
one to many
AlbumImage:
ImageID pk
AlbumID fk
Default
a contact may have many albums one of which is for profile pictures and all the profile pictures can be selected from album image
if a user did not upload a picture yet he will have an album for profile picture (of type=1) but without album images i want to select this user so i can give him throught the code later a default picture of my choice
/\|-||\/|/\|)
|
|
|
|
|
I think you need to add a WHERE clause
WHERE AI.AlbumID IS NULL
Also, if Album will always exist at least once with a Type of 1 for every contact then that should be changed to an INNER JOIN. The join to AlbumImage stays as a LEFT JOIN.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
I try this wat i get is trully all the results that i need except the users that has a profile picture ill get the result that is without a profile picture. So this way ill get all users without a profile picture eventhough they have one. But wat i need is to get the profile picture for the people who has one and get null for people who don't
thx for ur suggestion though
/\|-||\/|/\|)
|
|
|
|
|
I don't understand the difficulty you are having. You clearly specify the qualifications for the join in your question. Did you not integrate Default into the JOIN? A sample of your code would be helpful if the query below does not solve your issue.
SELECT
u.UserId,
u.UserName,
u.OtherUserStuff,
p.UserImage
FROM
[UserTable] u
LEFT JOIN
[ProfileTable] p
ON ((u.UserId = p.UserId) AND (p.Default = 1))
|
|
|
|
|
I did integrate default into the join the problem is simple:
If a user has more than one profile picture but only one is selected as default and if i do the join as u and i suggested ill get two results one witht the profile picture and another one without the profile picture. It is this second result that i wish to eliminate
thanks anyway for your help 
/\|-||\/|/\|)
|
|
|
|
|
You have lost me. Given the join I specified (and I think it mirrors your attempt) the only way you should get more than one row per user is if there are Duplicate User Records or muliple Default=1 per user.
|
|
|
|
|
By definition the Left join gives two results. I have read something similar to the following in many references about the left join:
"If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table."
I want that this is y i chose the left join but the thing is that apparently the left join gave an extra record for all users having a profile picture with all columns set to null for the right table but i need this to happen ONLY wen there is no profile picture. This is the question how can I make this diffrenciation??
Ps: There is no multiple users Records nor there is multiple default = 1 per user
Btw wen I add the default = 1 in the where clause i get strict result about only users with profile pictures and users without are neglected...
/\|-||\/|/\|)
|
|
|
|
|
You can't put the default=1 in the WHERE clause. It has to be put in the JOIN clause as I listed in a previous post. The WHERE clause will eliminate non-matching rows (a null in the default column does not match 1). I have long since stopped qualifying my joins in the WHERE clause because of this type of issue.
The sample query I listed before should get you your desired results if your tables are structured as you have stated. If you are getting duplicate results, there is something in your structure that I don't understand. I do LEFT JOINS all the time without the side effect you are getting.
Can you post the actual query you are using?
|
|
|
|
|
dear everyone
according to your insturctions i have successfully filled a listbox with student names on my form.
then i wished that when i click a student name in the list, my defined data-adapter should bring certain record from database and fill my textboxes, checkboxes, comboboxes with values relating to that particular student.
i am wonder to see that when i did that, my dataset is having same row again and again so i am finding error message when i am binding textbox source to the table.
help me plz
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
