Help with SQL Join Query!

Grrrr....

[dreamcatcher]

Joins seriously do my head in. :)

So, before I sell my laptop on Ebay, can someone help me out with this please?

I have two tables, each containing links. Both tables have an 'accountid' row, so I`m trying to pull the rows from each table based on the accountid no:

Table starsites has rows webname & weblink.
Table cinemamusic has rows name and link.

mysql_query("SELECT starsites.webname,starsites.weblink,cinemamusic.name,cinemamusic.link FROM starsites,cinemamusic WHERE starsites.accountid = '1' OR cinemamusic.accountid = '1' ORDER BY starsites.id,cinemamusic.id DESC") or die(mysql_error());

I put a LEFT JOIN in with an ON query but to be honest, I dont really know what I`m doing. I`ve tried AS queries and all sorts of things.

There are only two links in each table with the account id 1, so the query should only fetch four rows. Instead its fetching about 1700 rows. he he! Its all good fun.

First person to help, gets a free pint next time I see them in the pub. :)

Thanks guys!

[freeflight2]

You most likely want do something like this:

SELECT s.webname, s.weblink, c.name, c.link FROM starsites AS s,cinemamusic AS c WHERE s.accountid=1 AND c.accountid=s.accountid ORDER BY s.id,c.id DESC

(*)

==> you provide a specifc accountid and bot starsites and cinemamusic must have an entry for this accountid

LEFT JOIN:

SELECT s.webname, s.weblink, c.name, c.link FROM starsites AS s LEFT JOIN cinemamusic AS c ON c.accountid=s.accountid WHERE s.accountid=1 ORDER BY s.id,c.id DESC

==> again, you provide a specifc accountid and there must be starsites row with such an accountid and there may be a cinemamusic entry as well - if there is no cinemaentry with such an accountid you would still get a result (a full join would result nothing (0 rows)) - you would then get something like:
webname ¦ weblink ¦ NULL

(*) you also could have written the first query as:
SELECT s.webname, s.weblink, c.name, c.link FROM starsites AS s,cinemamusic AS c WHERE s.accountid=1 AND c.accountid=1 ORDER BY s.id,c.id DESC
but it's often nicer to show how tables are joined together in a query (plus the SQL optimzer gets some hints how to execute it best)

[dreamcatcher]

Thanks for the help freeflight2.

I tried both of your examples, but they both only fetch 1 row of data, which is 1 row from the starsites table.

If I try this:

SELECT starsites.webname,starsites.weblink,cinemamusic.name,cinemamusic.link FROM starsites LEFT JOIN cinemamusic ON starsites.accountid = '1' AND cinemamusic.accountid = '1' ORDER BY starsites.id,cinemamusic.id DESC

It fetches 1556 rows of data. No matter what I try I can`t seem to get it to only fetch the 4 rows. :(

Any ideas?

[baertyp]

Dreamcatcher,

a JOIN is the cartesian product of the referenced tables. Each row of table1 is merged with all of the rows of table2. So if you JOIN your two tables and work with the OR operator you will not get the desired output because in the JOIN you have

cardinality(starsites) * cardinality(cinemamusic)

rows going through the WHERE clause. That's why you get so many rows in the result.

Try AND instead of OR making your query look like

mysql_query("SELECT starsites.webname,starsites.weblink, 
cinemamusic.name,cinemamusic.link  
FROM starsites,cinemamusic 
WHERE starsites.accountid = '1' AND cinemamusic.accountid = '1'  
ORDER BY starsites.id,cinemamusic.id DESC") or die(mysql_error()); 

and this should do the job as far as I can see without actually testing it.

Regards
Markus

[dreamcatcher]

Thanks Markus. I have tried the AND operator (see previous post), but whenever I use it all I get is 1 row returned. I tried it exactly as you posted and still, just 1 row returned.

If I use LEFT JOIN I get data from both tables, but all the rows returned. I cant understand how its fetching all the rows, when only 4 of them have the account id set to 1.

[baertyp]

Are the two tables related in any way "logically" at all? The field name "accoutid" might be referencing into an id in a third table (maybe "accounts") which is not part of the query at all. In this case it might be better to sequentially query the two tables with two simple non-join queries and merge the results yourself in your code.

Still, if in each of the tables you have 2 rows having accountid=1 you end up with 4 rows in the JOIN having s.accountid=1 AND c.accountid=1 and thus the "AND"-query should show them all four.

Well, I might not have understood the problem enough.

Regards
Markus

[dreamcatcher]

Markus,

Thanks for the help you are giving me.

The two tables only have one common denominator and thats that they both have a row called accountid. There are 1500 or so links in the starsites table and about 200 in the cinemamusic table. I am just testing things at the moment, so what I did was I set the values of accountid to 0 for all rows, then edited 2 in each table and changed the value to 1.

Two seperate queries return 2 rows each, which is fine. I just thought there may be a way to do one query as opposed to 2. The other thing I could do is merge the data from both tables in to one new table. This is actually what I will probably do.

You are right in thinking there is an 'accounts' table. Maybe this is the problem? This does not have a accountid row though and only has 3 rows itself at the moment.

I`ve done joins before and they always worked ok. I`m more curious than anything as to why this isnt working.

[ergophobe]

The two tables only have one common denominator and thats that they both have a row called accountid.

The question is not whether or not the two tables have a commonly-named columns, but whether the tables have and logical relation to each other.

If the tables are not related, and it sounds as though they aren't, you will need to do two queries, pure and simple, otherwise you get a Cartesian product.

In other words, what you should be getting from your queries is (if I have this right... let's assume 100 rows in each table):

- a set of rows that includes the first starsites row where account_id=1 and all rows from cinemamusic (thus 100 rows)
- a set of rows that includes the second starsites row where account_id=1 and all rows from cinemamusic
- a set of rows that includes the first cinemamusic row where account_id=1 and all rows from startsites
- a set of rows that includes the first cinemamusic row where account_id=1 and all rows from startsites

total: 400 rows.

[coopster]

Maybe some sample data will clarify things here...?

[dreamcatcher]

Thanks for the help guys, but after serious headaches I decided to go with 2 seperate queries which works fine.

I shall be using a join when I come to syntax my search engine, so no doubt I`ll be back.

:)