Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > DB-Aware

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 08-17-2010, 12:58 AM
passpass passpass is offline
Member
 
Join Date: Nov 2002
Posts: 41
Default Trouble with my SQL

First of all, sorry for the long post, but I'm not that adept at sql querries and I don't know how to explain my problem in detail without explaining it well.

Hey, I have two SQL querries that deliver me the 2 results I want (combined):

SQL1 (14 resulted records):
Query1.SQL.Text := 'Select * FROM goods WHERE goods.price = ''Free''';

SQL2 (2 resulted records):
Query2.SQL.Text := 'Select * FROM goods, userdata WHERE (userdata.UserID = ' + inttostr(user) + ' AND userdata.goodsID = goods.goodsID)';


I have tried to use "Union" to get the two results together in my table like this...

SQL:
Query1.SQL.Text := 'Select * FROM goods WHERE goods.price = ''Free'' UNION Select * FROM goods, userdata WHERE (userdata.UserID = ' + inttostr(user) + ' AND userdata.godsID = goods.goodsID)';


Now this technically works (no errors), it gives me every record in the table 6 times... resulting in 14*6 = 84 records. While it should be 14+2 (1) = 16 (15) records. (I add (1) and (15) between brackets because as later will be explained, one of the 2 resulted records from SQL2 is a duplicate record already fetched from SQL1.)

The number of duplicate records (6) is directly linked to the number of records in the "userdata" table... if I add some more records in the userdata table (for example 12 records instead of 6) then I will have every result 11x duplicated for 12 total and that for each and every record...

I don't understand it cause seperatly both querries give me exactly what I want, SQL1 gives me all records who are "free", while SQL2 gives me all records where the column UserID equals the variable "user" (which is collected before this code is executed) and then in my table shows all goods where the userdata.goodsID is the same as the goods.goodsID.

A practical example of the two tables:

Table1 (goods)
GoodsID - Price - Etc... (other fields not important to the querry)
1 - Free
2 - Free
3 - 5 Euro
4 - Free
5 - Free
6 - Free
7 - Free
8 - Free
9 - Free
10 - 2 Euro
11 - Free
12 - Free
13 - Free
14 - Free
15 - Free
16 - Free

Table2 (userdata)
UserID - GoodsID - Etc... (other fields not important to the querry)
1 - 3
1 - 13
2 - 5
3 - 1
3 - 12
4 - 9


Now SQL1 is simple, it just looks and gives me the 14 records with "free" in its price column, the 2 records who have a price value are ignored.

SQL2 however is where I suspect the error lies... every "userID" is a "user". The first part of the querry (userdata.UserID = ' + inttostr(user)) filters the result set to only show the results of the current logged in user to the application, in my example, that's user "1". So this gives 2 results (the first 2).

The second part of the query is to fetch the records from goods who correspond to the goods.ID field. In this example that are the numbers "3" and "13". (3 was the one costing 5 euro, while 13 was free).

But this doesn't work, im getting every record as result x the amount of records in the userdata table... and this while there should only be 2 results the query should work with (those matching userdata.UserID = User). It should ignore the other userdata records not matching the user field as they are irrelevant to the current user and are forbidden to be shown.

Second of all it's really weird both querries when done seperatly work perfectly, yes even the complicated SQL2 querry gives me exact the 2 records (the 5 euro and the free one which are goods.id 3 and 13) as it should be doing ... the first one gives me the 14 free records as it should be doing .... add a union and badam I suddenly get everything duplicated all over...

One more thing, as you can see even when we interprete the SQL1 and 2 seperatly. They both will result in one record that complies to both SQL's. And that's goods.goodid 3. It's free so it will be included in one of the 14 results of SQL1. Yet it's also listed in the userdata.goodsID table and thus will be picked up by SQL2. That's intentional and not an error...

I was under the presumtion that a UNION query disregards duplicate results by nature so I'm not thinking to much about it... but obviously if it doesn't then that will be an added problem if I ever can get my querry to fetch me the records I desire to start with ...


Huge THANKS for anyone brave enough to plunge into my question... it's a notch above my knowledge I think

Last edited by passpass; 08-17-2010 at 01:02 AM.
Reply With Quote
  #2  
Old 08-17-2010, 02:03 AM
David B David B is offline
Senior Member
 
Join Date: Jun 2009
Posts: 319
Default

Hi, sorry I do not have time to read your whole post as I have to go out.
But I think what you are looking for is the SQL GROUP BY clause

That should get rid of your duplicates.

Will try and read it all a little later.

Good luck
Reply With Quote
  #3  
Old 08-17-2010, 12:01 PM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,333
Default

Second query is completely wrong, if you execute this separately you'll get the increment you're talking about...

Should be something like (NOT TESTED THOUGH):
Code:
SELECT goods.* FROM goods
LEFT JOIN userdata ON userdata.goodsid=goods.goodsid
WHERE userdata.userid=' + IntToStr(user)
Reply With Quote
  #4  
Old 08-17-2010, 02:43 PM
passpass passpass is offline
Member
 
Join Date: Nov 2002
Posts: 41
Default

Hey, tnx for the help, I tested your sql Norrit and it seems to work. However how would I go about on adding the results from SQl1 to the querry ?

Remember I need the records fetched from your querry (witch works) but also all free goods (Where goods.price = "Free") and I don't want any duplicates even if both parts fetch the same result record(s).

I tried this querry to get both results (doesn't work unfortanatly)

QueryGoods.SQL.Text := 'SELECT goods.* LEFT JOIN userdata ON userdata.goodsid = goods.goodsid WHERE userdata.userid =' + IntToStr(user) + ' OR goods.price = ''Free''';

Last edited by passpass; 08-17-2010 at 02:48 PM.
Reply With Quote
  #5  
Old 08-17-2010, 03:03 PM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,333
Default

I guess it's a UNION of your first query and mine, and then either DISTINCT or GROUP BY (goodsid, price)...
Reply With Quote
  #6  
Old 08-17-2010, 04:12 PM
passpass passpass is offline
Member
 
Join Date: Nov 2002
Posts: 41
Default

Quote:
Originally Posted by Norrit View Post
I guess it's a UNION of your first query and mine, and then either DISTINCT or GROUP BY (goodsid, price)...
Hey

Tried:
QueryGoods.SQL.Text := 'SELECT * FROM goods LEFT JOIN userdata ON userdata.goodsid = goods.goodsid WHERE userdata.userid =' + IntToStr(user) + ' UNION Select * FROM goods WHERE goods.price = ''Free''';

This gives me the following error:
"The used select statements have different amount of columns"

I take it's caused by the second querry in the Union not using the userdata table, but well ... that part doesn't need the userdata for anything so why does it want it anyway ? Should I just let it fetch userdata in the second select statement again ?


EDIT:
just tried adding the "userdata" table to the second part of the select query like this:
QueryGoods.SQL.Text := 'SELECT * FROM goods LEFT JOIN userdata ON userdata.goodsid = goods.goodsid WHERE userdata.userid =' + IntToStr(user) + ' UNION Select * FROM goods, userdata WHERE goods.price = ''Free''';

This results in the records being duplicated again... I take it's because I add the userdata to the second select statement while it really doesn't need that table for the second select statement (only for the first) so I have no clue why it demands to have it... (it's not like it's physically adding any of the columns fetched from userdata into my dbgrid anyway ... userdata is only used to select which records from goods it's allowed to display for the logged in user)...


EDIT2:
Well I tried the Group By function like this:
QueryGoods.SQL.Text := 'SELECT * FROM goods LEFT JOIN userdata ON userdata.goodsid = goods.goodsid WHERE userdata.userid =' + IntToStr(user) + ' UNION Select * FROM goods, userdata WHERE goods.price = ''Free'' GROUP BY goods.goodsid';

This WORKS !!! I'm not sure if it's the best possible querry cause it seems when (and it will) the userdata column will go to the thousands of records the querry will fetch 10.000 or so records and it then groups them to 30 records..... so it seems like weird behaviour to me... but it works .... (unless the duplication is happening client side and not server side and it's not physically taxing my online mysql server for all those duplicate records)


EDIT3
Well..... I spoke to soon, it "almost" works. What it does now is give me no duplicates of the records fetched by the second select statement. However it still give me duplicates on the records fetched by the first select statement for the total of the amount of records fetched by said select statement.... I tried adding a "group by" to the first select statement as well like this:

QueryGoods.SQL.Text := 'SELECT * FROM goods LEFT JOIN userdata ON userdata.goodsid = goods.goodsid WHERE userdata.userid =' + IntToStr(user) + ' GROUP BY goods.goodsid UNION Select * FROM goods, userdata WHERE goods.price = ''Free'' GROUP BY goods.goodsid ORDER BY title ASC';

But this while giving me no error still duplicates records selected from the first select statement......



To be honest, i'm a ta total loss here ... everytime I think it's fixed, something else pops up

Last edited by passpass; 08-17-2010 at 05:18 PM.
Reply With Quote
  #7  
Old 08-19-2010, 03:03 PM
vishualsoft vishualsoft is offline
Senior Member
 
Join Date: Jan 2010
Location: Pune, India
Posts: 310
Smile

Hi passpass,

Let me have a look on this, I hope I could do something.

Give me some little time to understand your requirement.


With Best Regards.


Vishal
__________________
"Hardwork always pays."

"Confidence never comes if we have all the answers
but it comes only when we are ready for all the questions."

The man who removes a mountain begins by carrying away small stones. - William Faulkner
Reply With Quote
  #8  
Old 08-19-2010, 03:54 PM
vishualsoft vishualsoft is offline
Senior Member
 
Join Date: Jan 2010
Location: Pune, India
Posts: 310
Smile

Hi passpass,

Could you send me the expected result set as per your example given in your first mail.


Because I need to know your expected result, coz I am slightly confused about your expectation.

No doubt we would make it, No problem, Okay?


Just send me the sample of your expected result (if possible complete sample, if you could, and if it is small in length or if it is huge then just give some sample records). Without that I would not be able to work further, coz I still didn't get what exactly you need.


The query you wrote was not included the Goods table, just try following SQL:

SELECT Goods.* from Goods LEFT OUTER JOIN UserData ON UserData.GoodsIdD = Goods.GoodsID WHERE UserData.UserID = ' + IntToStr(UserID) + ' OR Upper(Goods.Price) = Upper('Free')



With Best Regards.


Vishal
__________________
"Hardwork always pays."

"Confidence never comes if we have all the answers
but it comes only when we are ready for all the questions."

The man who removes a mountain begins by carrying away small stones. - William Faulkner

Last edited by vishualsoft; 08-19-2010 at 04:16 PM.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 05:18 AM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.