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_winners a, PHPAUCTIONXL_auctions 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_archived 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_winners a, PHPAUCTIONXL_auctions b,
PHPAUCTIONXL_archived c
WHERE a.auction=b.id
AND a.auction !=c.archived";

How do I get this to work ?

red

[Geoff Berrow]

I noticed that Message-ID: <a4**********************@twister.nyc.rr.com>
from red contained the following:

AND a.auction !=c.archived";

How do I get this to work ?

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]

Geoff Berrow wrote:

I noticed that Message-ID: <a4**********************@twister.nyc.rr.com>
from red contained the following:

AND a.auction !=c.archived";

How do I get this to work ?

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

AND NOT a.auction =c.archived

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

[Erwin Moller]

red wrote:

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_winners a, PHPAUCTIONXL_auctions 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_archived 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_winners a, PHPAUCTIONXL_auctions b,
PHPAUCTIONXL_archived c
WHERE a.auction=b.id
AND a.auction !=c.archived";

How do I get this to work ?

red

http://www.w3schools.com/sql

Try it. Good site for starters. Really.

good luck!

Regards

[Ed Simmons]

Stijn Verholen wrote:

Geoff Berrow wrote:

I noticed that Message-ID: <a4**********************@twister.nyc.rr.com>
from red contained the following:

AND a.auction !=c.archived";

How do I get this to work ?

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

AND NOT a.auction =c.archived

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

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]

"red" <gr*****@reenie.org> wrote

$query =
"SELECT a.auction, a.winner, b.id
FROM PHPAUCTIONXL_winners a, PHPAUCTIONXL_auctions b,
PHPAUCTIONXL_archived c
WHERE a.auction=b.id
AND a.auction !=c.archived";

How do I get this to work ?

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

select a.auction, a.winner, b.id
from (PHPAUCTIONXL_winners a join PHPAUCTIONXL_auctions b on a.auction=b.id)
where not exists (select archived from PHPAUCTIONXL_archived c where
c.archived = a.auction)

or

select a.auction, a.winner, b.id, c.archived
from (PHPAUCTIONXL_winners a join PHPAUCTIONXL_auctions b on a.auction=b.id)
left join PHPAUCTIONXL_archived c on a.auction=c.archived
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/EXI...ubqueries.html

-- Dan

[Ed Simmons]

Dan Stumpus wrote:

"red" <gr*****@reenie.org> wrote

$query =
"SELECT a.auction, a.winner, b.id
FROM PHPAUCTIONXL_winners a, PHPAUCTIONXL_auctions b,
PHPAUCTIONXL_archived c
WHERE a.auction=b.id
AND a.auction !=c.archived";

How do I get this to work ?

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

select a.auction, a.winner, b.id
from (PHPAUCTIONXL_winners a join PHPAUCTIONXL_auctions b on

a.auction=b.id) where not exists (select archived from PHPAUCTIONXL_archived c where
c.archived = a.auction) This is the result I got:
Error

SQL-query :

SELECT a.auction, a.winner, b.id
FROM (
PHPAUCTIONXL_winners a
JOIN PHPAUCTIONXL_auctions b ON a.auction = b.id
)
WHERE NOT
EXISTS (

SELECT archived
FROM PHPAUCTIONXL_archived 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


or

select a.auction, a.winner, b.id, c.archived
from (PHPAUCTIONXL_winners a join PHPAUCTIONXL_auctions b on a.auction=b.id) left join PHPAUCTIONXL_archived c on a.auction=c.archived
where c.archived is null

This is the result I got:
Error

SQL-query :

SELECT a.auction, a.winner, b.id, c.archived
FROM (
PHPAUCTIONXL_winners a
JOIN PHPAUCTIONXL_auctions b ON a.auction = b.id
)
LEFT JOIN PHPAUCTIONXL_archived 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_archived c ON a.auction = c.archiv' at line 1

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/EXI...ubqueries.html

-- Dan

[Dan Stumpus]

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" <hu*@huh.com> wrote

SELECT a.auction, a.winner, b.id
FROM (
PHPAUCTIONXL_winners a
JOIN PHPAUCTIONXL_auctions b ON a.auction = b.id
)
WHERE NOT
EXISTS (

SELECT archived
FROM PHPAUCTIONXL_archived 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
== 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.auction)

....and it ran successfully, rejecting the transaction if it was in the
archived table,
and showing it if not archived.
Error SQL-query :

SELECT a.auction, a.winner, b.id, c.archived
FROM (
PHPAUCTIONXL_winners a
JOIN PHPAUCTIONXL_auctions b ON a.auction = b.id
)
LEFT JOIN PHPAUCTIONXL_archived 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_archived c ON a.auction = c.archiv' at line 1

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.archived
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]

Dan Stumpus wrote:

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" <hu*@huh.com> wrote

SELECT a.auction, a.winner, b.id
FROM (
PHPAUCTIONXL_winners a
JOIN PHPAUCTIONXL_auctions b ON a.auction = b.id
)
WHERE NOT
EXISTS (

SELECT archived
FROM PHPAUCTIONXL_archived 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

== 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.auction)

...and it ran successfully, rejecting the transaction if it was in the
archived table,
and showing it if not archived.

Error SQL-query :

SELECT a.auction, a.winner, b.id, c.archived
FROM (
PHPAUCTIONXL_winners a
JOIN PHPAUCTIONXL_auctions b ON a.auction = b.id
)
LEFT JOIN PHPAUCTIONXL_archived 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_archived c ON a.auction = c.archiv' at line 1

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.archived
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

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.