sql joining 3 tables

[pagegen]

Hi Guys

 

Need some advise on sql really

 

here is what I am trying to do

 

I have 4 tables:

3 table contains a provider name and a provider url

I want to join all three tables on the provider name, and get the url

but I only want to get the providers where one of the tables 'bb_BettingOffer' has a column providerId needs to match bb_Provider.id

 

 

 

the tables are:

bb_Provider - main table which contains all the provider and there url

default_providers - default providers we have selected

members_providers - the providers a member has selected

bb_BettingOffer - we are looping through this table, and joining this to providerId

 

"
SELECT 		bb_Provider.id AS bookie_id, bb_Provider.url AS bookie_url,
                dp.provider_name AS dp_name, dp.url AS dp_url,
		mp.provider_name, mp.url, 
		bb_BettingOffer.* 
FROM 		bb_Provider, default_providers dp, members_providers mp, bb_BettingOffer 
WHERE 		bb_BettingOffer.outcomeId IN($outcome_ids) AND 
		bb_Provider.id=bb_BettingOffer.providerId AND 
		mp.provider_name=bb_Provider.name AND 
		dp.provider_name=bb_Provider.name 
		GROUP BY 	providerId 
		ORDER BY 	mp.order_field ASC"

 

Hope this makes sence, feel free to ask questions

 

Thank you in advanced

[fenway]

Well, that's in the where clause -- what's not working.

[pagegen]

Hi fenway,

 

sorry about that

 

I am using MYSQL database with myphp admin

 

$outcome_ids = "1111, 1234"; its really an array of ids and these ids are the public key of table bb_providers

 

there are no errors as the query works, but the issue is, I am sure its not the best way to write an sql query as I could have used JOIN but I dont no where

 

whats output is

the provider name, and the 3 urls (in the 3 diffrent tables), plus all data from bb_BettingOffer

 

I hope I have answered every thing you need

 

Thank you

 

[pagegen]

any thing guys?

[fenway]

Sorry, I don't follow.

[mikosiko]

I don't follow either... but to answer what you posted below

 

......

there are no errors as the query works, but the issue is, I am sure its not the best way to write an sql query as I could have used JOIN but I dont no where

.....

 

You ARE using JOIN in the select that you posted... the fact that you are not using the word "JOIN" doesn't mean that you are not using it... that is called "implicit JOIN"... in other words... this 2 sentences are exactly the same and equivalents:

 

// JOIN USING IMPLICIT NOTATION
  select a.field1, b.field2
    FROM table1 a , table2 b
    WHERE a.id = b.id

 

// JOIN USING EXPLICIT NOTATION
  select a.field1, b.field2
    FROM table1 a JOIN table2 b ON a.id = b.id

[fenway]

Further to the last post, NEVER use implicit notation -- it's just evil.

[mikosiko]

Further to the last post, NEVER use implicit notation -- it's just evil.

  maybe will be good if you further elaborate on that fenway

 

[fenway]

It just leads to all kinds of problems -- missing join conditions, precedence hell will left joins, the list is endless.

[mikosiko]

It just leads to all kinds of problems -- missing join conditions, precedence hell will left joins, the list is endless.

 

agree... but is not evil   just a wild horse to need to be controlled

 

some people prefer that syntax because make your code more portable/standard. At the end, you normally could face the probability of be in need of modify your JOIN's to comply with  the specifics of the DB Engine that you will be working with (more less frequent nowadays). I personally I have been using both syntaxes without mayor problem for looooong time

 

the point for the OP is to learn that both syntaxes are equivalent and each one present pros/cons that he need to evaluate and decide which one (or both) he want to learn/use. (I suggest to learn/use both... but that is JMHO).

 

[fenway]

The JOIN and comma operators changed precedence across mysql versions a while back -- that alone makes it evil.