By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,291 Members | 3,096 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,291 IT Pros & Developers. It's quick & easy.

sql join

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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/
Jul 17 '05 #2

P: n/a
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
Jul 17 '05 #3

P: n/a
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
Jul 17 '05 #4

P: n/a
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
Jul 17 '05 #5

P: n/a

"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
Jul 17 '05 #6

P: n/a
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

Jul 17 '05 #7

P: n/a
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
Jul 17 '05 #8

P: n/a
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.
Jul 17 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.