Hello to everyone,

I need a little help with php/mysql since i'm new on this.
I have a table named countries that have fields countryID, countryName, countryCode, countryActive and countryUrl;
another table named category that have fields categoryID, categoryName, categoryActive and category Url
and the next table named offers that have field offerID, offerCountry (that is some data got from countries table), offerCategory (that is some data got from category table), offerName, offerTxt, etc

What i want to do is to join those 3 tables and use informations of eachother, something like this:

SELECT B.*, 
	A.offerID AS offerID, 
	A.offerName AS offerName, 
	A.offerCountry AS offerCountry, 
	A.offercategory AS offerCategory 
		FROM offers A LEFT JOIN countries B ON 
	A.offerCountry = B.countryID 
		WHERE B.countryID = offerCountry ...";

I have used something like this in an other application but it joins only to tables and i was a little confused on examples that i found on the internet, for this i chose to post a new thread out here.

I need this kind of combination bcz, i want to made a kind of sort of offers depending on countries or categories 😉

Can someone help me on this, please?
Thank you in advance!

    What did you try? All you need to do is add the second JOIN in the same manner that the first was done.

    In addition, your WHERE condition is superfluous since you've already specified that condition in the JOIN condition above, so just remove it from the WHERE condition (unless you actually enjoy trying to confuse yourself/others by adding extraneous information 😉).

      bradgrafelman;10989198 wrote:

      What did you try? All you need to do is add the second JOIN in the same manner that the first was done.

      In addition, your WHERE condition is superfluous since you've already specified that condition in the JOIN condition above, so just remove it from the WHERE condition (unless you actually enjoy trying to confuse yourself/others by adding extraneous information 😉).

      Yep, u'r right 😉
      So, the join issue (for me an issue 😛 ) should look like this?

      SELECT B.*, C.*,
	A.offerID AS offerID, 
	A.offerName AS offerName, 
	A.offerCountry AS offerCountry, 
	A.offerCategory AS offerCategory 
		FROM offers A LEFT JOIN countries B ON 
	A.offerCountry = B.countryID 
        A.offerCategory = C.categoryID
		WHERE B.countryActive=1 and C.categoryActive=1 and offerActive=1";

      Is this correct?

      Also, what type of db field should be for offerCountry and offerCategory? Init or varchar?
      Thanks!

        Kleidi;10989220 wrote:
        SELECT B.*, C.*,
	A.offerID AS offerID, 
	A.offerName AS offerName, 
	A.offerCountry AS offerCountry, 
	A.offerCategory AS offerCategory 
		FROM offers A LEFT JOIN countries B ON 
	A.offerCountry = B.countryID 
        A.offerCategory = C.categoryID
		WHERE B.countryActive=1 and C.categoryActive=1 and offerActive=1";

        Is this correct?

        Where is the 'C' alias ever defined? Also, you still only have one JOIN in that query.

        Kleidi;10989220 wrote:

        Also, what type of db field should be for offerCountry and offerCategory? Init or varchar?

        They should be exactly the same type as whatever column they are meant to reference. In addition, consider adding foreign keys on those columns as well (assuming you're using a storage engine like InnoDB that even knows what a 'foreign key' is).

        EDIT: Just noticed another error in that SQL query; this:

         A.offerCategory = C.categoryID

        is all by itself wedged in between a JOIN and a WHERE condition, so it doesn't make any sense. Perhaps there's a missing JOIN that the above line is supposed to go with?

          Write a Reply...