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]

I noticed that Message-ID: <a4************ **********@twis ter.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************ **********@twis ter.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_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

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************ **********@twis ter.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_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 ?

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

-- Dan

[Ed Simmons]

Dan Stumpus wrote:

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

$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 ?

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) 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


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

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

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_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
== 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.
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

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]

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_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

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

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

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

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.