sql join

[red]

This is a simple join but I am having trouble with it. I feel like such
an idiot.

I start with this select statement, which works fine:
$query =
"SELECT a.auction, a.winner, b.id
FROM PHPAUCTIONXL_wi nners a, PHPAUCTIONXL_au ctions b
WHERE a.auction=b.id" ;

Now I only want the results from auctions with ids that are not listed
in the 'archived' field of the PHPAUCTIONXL_ar chived table. So I added
another table and an AND to the SELECT statement. Now I don't understand
why it doesn't show the auctions which are not archived.

$query =
"SELECT a.auction, a.winner, b.id
FROM PHPAUCTIONXL_wi nners a, PHPAUCTIONXL_au ctions b,
PHPAUCTIONXL_ar chived c
WHERE a.auction=b.id
AND a.auction !=c.archived";

How do I get this to work ?

red

[Geoff Berrow]

Re: sql join

I noticed that Message-ID: <a4FMd.72243$Yh 2.25349881@twis ter.nyc.rr.com>
from red contained the following:
[color=blue]
> AND a.auction !=c.archived";
>
>How do I get this to work ?[/color]

Just a guess (I'm not a major SQL guru)

AND NOT a.auction =c.archived
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Stijn Verholen]

Re: sql join

Geoff Berrow wrote:[color=blue]
> I noticed that Message-ID: <a4FMd.72243$Yh 2.25349881@twis ter.nyc.rr.com>
> from red contained the following:
>
>[color=green]
>> AND a.auction !=c.archived";
>>
>>How do I get this to work ?[/color]
>
>
> Just a guess (I'm not a major SQL guru)
>
> AND NOT a.auction =c.archived[/color]

or:
AND a.auction <> c.archived

http://dev.mysql.com/doc/mysql/en/comparison-operators.html

http://dev.mysql.com/doc/mysql/en/logical-operators.html

HTH

[Erwin Moller]

Re: sql join

red wrote:
[color=blue]
> This is a simple join but I am having trouble with it. I feel like such
> an idiot.
>
> I start with this select statement, which works fine:
> $query =
> "SELECT a.auction, a.winner, b.id
> FROM PHPAUCTIONXL_wi nners a, PHPAUCTIONXL_au ctions b
> WHERE a.auction=b.id" ;
>
> Now I only want the results from auctions with ids that are not listed
> in the 'archived' field of the PHPAUCTIONXL_ar chived table. So I added
> another table and an AND to the SELECT statement. Now I don't understand
> why it doesn't show the auctions which are not archived.
>
> $query =
> "SELECT a.auction, a.winner, b.id
> FROM PHPAUCTIONXL_wi nners a, PHPAUCTIONXL_au ctions b,
> PHPAUCTIONXL_ar chived c
> WHERE a.auction=b.id
> AND a.auction !=c.archived";
>
> How do I get this to work ?
>
> red[/color]

http://www.w3schools.com/sql

Try it. Good site for starters. Really.

good luck!

Regards

[Ed Simmons]

Re: sql join

Stijn Verholen wrote:[color=blue]
> Geoff Berrow wrote:
>[color=green]
>> I noticed that Message-ID: <a4FMd.72243$Yh 2.25349881@twis ter.nyc.rr.com>
>> from red contained the following:
>>
>>[color=darkred]
>>> AND a.auction !=c.archived";
>>>
>>> How do I get this to work ?[/color]
>>
>>
>>
>> Just a guess (I'm not a major SQL guru)
>>
>> AND NOT a.auction =c.archived[/color]
>
>
> or:
> AND a.auction <> c.archived
>
> http://dev.mysql.com/doc/mysql/en/co...operators.html
> http://dev.mysql.com/doc/mysql/en/lo...operators.html
>
> HTH[/color]

ND NOT a.auction =c.archivedT results in no rows

AND a.auction <> c.archived results in all rows

so niether one filters out the archived auctions

[Dan Stumpus]

Re: sql join


"red" <groups2@reenie .org> wrote
[color=blue]
> $query =
> "SELECT a.auction, a.winner, b.id
> FROM PHPAUCTIONXL_wi nners a, PHPAUCTIONXL_au ctions b,
> PHPAUCTIONXL_ar chived c
> WHERE a.auction=b.id
> AND a.auction !=c.archived";
>
> How do I get this to work ?[/color]

I think there's a couple of ways to do this:

select a.auction, a.winner, b.id
from (PHPAUCTIONXL_w inners a join PHPAUCTIONXL_au ctions b on a.auction=b.id)
where not exists (select archived from PHPAUCTIONXL_ar chived c where
c.archived = a.auction)

or

select a.auction, a.winner, b.id, c.archived
from (PHPAUCTIONXL_w inners a join PHPAUCTIONXL_au ctions b on a.auction=b.id)
left join PHPAUCTIONXL_ar chived c on a.auction=c.arc hived
where c.archived is null


Think of it as a multi step process:

You need to do an inner join on a & b first,
then each row is checked against the archived table and rejected if there's
a match.

My sql is a bit rusty, and this page refreshed my memory about exists...

http://mysqld.active-venture.com/EXISTS_and_NOT_EXISTS_subqueries.html

-- Dan

[Ed Simmons]

Re: sql join

Dan Stumpus wrote:[color=blue]
> "red" <groups2@reenie .org> wrote
>
>[color=green]
>>$query =
>>"SELECT a.auction, a.winner, b.id
>>FROM PHPAUCTIONXL_wi nners a, PHPAUCTIONXL_au ctions b,
>>PHPAUCTIONXL_ archived c
>>WHERE a.auction=b.id
>> AND a.auction !=c.archived";
>>
>>How do I get this to work ?[/color]
>
>
> I think there's a couple of ways to do this:
>
> select a.auction, a.winner, b.id
> from (PHPAUCTIONXL_w inners a join PHPAUCTIONXL_au ctions b on[/color]
a.auction=b.id)[color=blue]
> where not exists (select archived from PHPAUCTIONXL_ar chived c where
> c.archived = a.auction)[/color]
This is the result I got:
Error

SQL-query :

SELECT a.auction, a.winner, b.id
FROM (
PHPAUCTIONXL_wi nners a
JOIN PHPAUCTIONXL_au ctions b ON a.auction = b.id
)
WHERE NOT
EXISTS (

SELECT archived
FROM PHPAUCTIONXL_ar chived c
WHERE c.archived = a.auction
)

MySQL said:

You have an error in your SQL syntax near 'ON a.auction = b.id ) WHERE
NOT EXISTS ( SELECT archived FROM PHPAUCTIONXL_ar ' at line 1

[color=blue]
>
> or
>
> select a.auction, a.winner, b.id, c.archived
> from (PHPAUCTIONXL_w inners a join PHPAUCTIONXL_au ctions b on[/color]
a.auction=b.id)[color=blue]
> left join PHPAUCTIONXL_ar chived c on a.auction=c.arc hived
> where c.archived is null
>
>[/color]
This is the result I got:
Error

SQL-query :

SELECT a.auction, a.winner, b.id, c.archived
FROM (
PHPAUCTIONXL_wi nners a
JOIN PHPAUCTIONXL_au ctions b ON a.auction = b.id
)
LEFT JOIN PHPAUCTIONXL_ar chived c ON a.auction = c.archived
WHERE c.archived IS NULL
LIMIT 0 , 30

MySQL said:

You have an error in your SQL syntax near 'ON a.auction = b.id ) LEFT
JOIN PHPAUCTIONXL_ar chived c ON a.auction = c.archiv' at line 1



[color=blue]
> Think of it as a multi step process:
>
> You need to do an inner join on a & b first,
> then each row is checked against the archived table and rejected if[/color]
there's[color=blue]
> a match.
>
> My sql is a bit rusty, and this page refreshed my memory about exists...
>
> http://mysqld.active-venture.com/EXI...ubqueries.html
>
> -- Dan
>
>[/color]

[Dan Stumpus]

Re: sql join

Ed:

I just created the tables and fields on my xp box (mysql 4.1) and ran the
queries.
Results are below...(I shortened the tablenames by removing the PHPAUCTIONXL
prefix):

"Ed Simmons" <huh@huh.com> wrote
[color=blue]
> SELECT a.auction, a.winner, b.id
> FROM (
> PHPAUCTIONXL_wi nners a
> JOIN PHPAUCTIONXL_au ctions b ON a.auction = b.id
> )
> WHERE NOT
> EXISTS (
>
> SELECT archived
> FROM PHPAUCTIONXL_ar chived c
> WHERE c.archived = a.auction
> )
>
> MySQL said:
>
> You have an error in your SQL syntax near 'ON a.auction = b.id ) WHERE
> NOT EXISTS ( SELECT archived FROM PHPAUCTIONXL_ar ' at line 1[/color]

== cut and paste from my query analyzer follows ==

SELECT a.auction, a.winner, b.id
FROM (
winners a
JOIN
auctions b ON a.auction = b.id
) where not exists (select archived from archived c
where c.archived=a.au ction)

....and it ran successfully, rejecting the transaction if it was in the
archived table,
and showing it if not archived.
[color=blue]
> Error SQL-query :
>
> SELECT a.auction, a.winner, b.id, c.archived
> FROM (
> PHPAUCTIONXL_wi nners a
> JOIN PHPAUCTIONXL_au ctions b ON a.auction = b.id
> )
> LEFT JOIN PHPAUCTIONXL_ar chived c ON a.auction = c.archived
> WHERE c.archived IS NULL
> LIMIT 0 , 30
>
> MySQL said:
>
> You have an error in your SQL syntax near 'ON a.auction = b.id ) LEFT
> JOIN PHPAUCTIONXL_ar chived c ON a.auction = c.archiv' at line 1[/color]

I don't know what's wrong, but the below query was cut/pasted from my query
analyzer, and it ran perfectly (also on mysql 4.1):

select a.auction, a.winner, b.id, c.archived
from (winners a join auctions b on
a.auction=b.id)
left join archived c on a.auction=c.arc hived
where c.archived is null

I was relieved that they worked right away (since my original post was just
off the top of my head).
Could you have created the tables or fields with different spellings?

Scratching my head,

-- Dan

[Ed Simmons]

Re: sql join

Dan Stumpus wrote:[color=blue]
> Ed:
>
> I just created the tables and fields on my xp box (mysql 4.1) and ran the
> queries.
> Results are below...(I shortened the tablenames by removing the PHPAUCTIONXL
> prefix):
>
> "Ed Simmons" <huh@huh.com> wrote
>
>[color=green]
>>SELECT a.auction, a.winner, b.id
>>FROM (
>>PHPAUCTIONXL_ winners a
>>JOIN PHPAUCTIONXL_au ctions b ON a.auction = b.id
>>)
>>WHERE NOT
>>EXISTS (
>>
>>SELECT archived
>>FROM PHPAUCTIONXL_ar chived c
>>WHERE c.archived = a.auction
>>)
>>
>>MySQL said:
>>
>>You have an error in your SQL syntax near 'ON a.auction = b.id ) WHERE
>>NOT EXISTS ( SELECT archived FROM PHPAUCTIONXL_ar ' at line 1[/color]
>
>
> == cut and paste from my query analyzer follows ==
>
> SELECT a.auction, a.winner, b.id
> FROM (
> winners a
> JOIN
> auctions b ON a.auction = b.id
> ) where not exists (select archived from archived c
> where c.archived=a.au ction)
>
> ...and it ran successfully, rejecting the transaction if it was in the
> archived table,
> and showing it if not archived.
>
>[color=green]
>>Error SQL-query :
>>
>>SELECT a.auction, a.winner, b.id, c.archived
>>FROM (
>>PHPAUCTIONXL_ winners a
>>JOIN PHPAUCTIONXL_au ctions b ON a.auction = b.id
>>)
>>LEFT JOIN PHPAUCTIONXL_ar chived c ON a.auction = c.archived
>>WHERE c.archived IS NULL
>>LIMIT 0 , 30
>>
>>MySQL said:
>>
>>You have an error in your SQL syntax near 'ON a.auction = b.id ) LEFT
>>JOIN PHPAUCTIONXL_ar chived c ON a.auction = c.archiv' at line 1[/color]
>
>
> I don't know what's wrong, but the below query was cut/pasted from my query
> analyzer, and it ran perfectly (also on mysql 4.1):
>
> select a.auction, a.winner, b.id, c.archived
> from (winners a join auctions b on
> a.auction=b.id)
> left join archived c on a.auction=c.arc hived
> where c.archived is null
>
> I was relieved that they worked right away (since my original post was just
> off the top of my head).
> Could you have created the tables or fields with different spellings?
>
> Scratching my head,
>
> -- Dan
>
>[/color]


Anyway I don't know what the problem was. I was kinda hopin someone
could understand and explain the error message so they could explain
what it was getting at.

Although I would love to be able to write more complex joins I decided
the whole approach was wrong. Instead of using an archive table with a
list of auctions on the auction table that are archived, I now move all
the values in the archived auctions to thier own table.

I realized that if I archived 2000 auctions a week, at the end of the
year I would have a table with over 100,000 auctions. Not too bad for
the archive because it won't get used as much. But the active auctions,
should be on separate table.