JOINS! not getting correct data from query.

[34534534534555]

Hello all,

I have the following query:

$sql = "SELECT auction.auctionid, book.title, bid.bidusername, bid.bidprice
		FROM auction INNER JOIN book ON (auction.isbn = book.isbn)
		LEFT JOIN bid ON (auction.auctionid=bid.auctionid)
		WHERE sellerusername = '$username'
		ORDER BY bid.bidprice DESC
		LIMIT 1"

A USER can have many AUCTIONS. An AUCTION can have many BIDS.

At present this is only displaying one auction for the user not all of them. It is displaying the correct information for each instance.

Would someone please be so kind as to modify this to show all the auctions for a user.


| Feedback is always appreciated as this will help to further our knowledge as well |

 

[MartijnTonies]

How about removing the "LIMIT" clause?

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[34534534534555]

Martin,

If i remove the limit clause then i will get all the bids for one auction. But still not all the other auctions for that user.

Many Thanks

 

[MartijnTonies]

In what table is "sellerusername"? It doesn't have a table specified.

And try: LEFT OUTER JOIN bid



Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[cocobingo]

If you want all auctions for one user, try :

SELECT auction.auctionid, book.title, bid.bidusername, bid.bidprice
FROM auction INNER JOIN book ON (auction.isbn = book.isbn)
LEFT JOIN bid ON (auction.auctionid=bid.auctionid)
WHERE sellerusername = '$username'
GROUP BY auction.auctionid

But it won't tell you if the user has the highest bid.

Arnaud

 

[swampBoogie]

For each auction, get the highest bid

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

 

[34534534534555]

Martijn

sellerusername is in auction. - i have specified a table now, and it makes no difference.

I also, tried making it a LEFT OUTER JOIN and it made no difference

Arnaud

i tried your code and it actually seemed to do everything i wanted it to , unitl i entered more data , then thinks went wild!!

SwampBoogie

thanks for that link, but i am using SQL 3.23.. which i believe does not support sub selects

I think i am going to re-think how to display which information and where

Many Thanks for your reply's

p.s. Is it possible to use multiple AND clauses in a SELECT??
I need to SELECT

WHERE book.isbn = auction.isbn 
						AND sellerusername = '$username'
      AND auction.status <> 'withdrawn'&quot;;

..when i do this, it returns no results, even though there is data to display.


| Feedback is always appreciated as this will help to further our knowledge as well |

 

[MartijnTonies]

It's hard to tell what to do with the full metadata available. Can you attach a script and some sample data perhaps?

And explain again what the results should be?

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[swampBoogie]

The article describes how to do it without subqueries.

 

[34534534534555]

Many thanks for all of your reply's.

Martijn,

I have tables:

Auction:
===AuctionId=== ===isbn===
1 123
2 456

Book:
===Title=== ===isbn===
Mary Poppins 123
Good Food 456

Bid:
===Auctionid=== ===BidderName=== ===BidPrice===
1 richard 10.00
1 alex 8.00
1 richard 6.00
2 richard 9.00
2 richard 11.00
2 edmund 5.00

Output Should Be:

Richard, you are bidding On:
===AuctionId=== ===Title=== ===Price===
1 Mary P 10.00
2 Good Food 11.00


| Feedback is always appreciated as this will help to further our knowledge as well |

 

[cocobingo]

OK I understand it better when I see the tables...
swampBooggie gave you the good answer!!!
You should check his link again and see at the end of the doc. The SQL you are looking for is at the end of the page, it is called the maxconcat trick.

Have fun! ;-)

Arnaud