Query not getting proper data

Databases
1

Hello,

I have this simple query and its not getting the data of the category table. Any suggestions ?

SELECT products.*, categories.* FROM products
        LEFT JOIN categories ON products.prod_catg_id = categories.catg_id
        WHERE categories.catg_status = '1' ORDER BY RAND(), products.prod_price LIMIT 0, 4

Thanks.

2

With that WHERE condition, the LEFT JOIN becomes an INNER JOIN. So what do you mean by ‘its not getting the data of the category table’ ? If it isn’t getting the category table data, it shouldn’t be getting any data.

If you really need a LEFT JOIN, then try putting the condition in the ON clause instead of in the WHERE clause.

3

Hello,

Thanks for your response…i have modified my query:

SELECT products.*, categories.* FROM products
        LEFT JOIN categories ON products.prod_catg_id = categories.catg_id
        ORDER BY RAND(), products.prod_price LIMIT 0, 4

But even after removing the WHERE clause I am still not getting the data from the categories table but in both the cases the data from products table is coming fine.

Please suggest.

Thanks.

4

Can you give some example data from both tables, the result you want and the result you are getting right now?

5

Hi,

LOL very sorry! My bad. I was executing the wrong query lol! :rofl:

Thanks.

6

Hello,

Sorry to bother again. I have now a new problem with the same tables. I have now included sub-categroies also in the categories table. I have attached the picture which shows the categories table.

I am executing the following query to get the results but its not returning any data at all.

SELECT products.*, categories.*, categories.* FROM products
        LEFT JOIN categories ON products.prod_catg_id = categories.catg_id
        LEFT JOIN categories ON products.prod_sbcg_id = categories.catg_id
        WHERE categories.catg_status = '1' ORDER BY RAND(), products.prod_price LIMIT 0, 10

Please help.

Thanks.

7

if you want to join to the same table more than once, you ~must~ use table aliases

e.g.


LEFT JOIN categories [COLOR="Blue"]AS maincat [/COLOR]ON [COLOR="blue"]maincat[/COLOR].catg_id = products.prod_catg_id 
LEFT JOIN categories [COLOR="blue"]AS subcat [/COLOR]ON [COLOR="blue"]subcat[/COLOR].catg_id = products.prod_sbcg_id

please note, you are still forcing an inner join by putting a condition on one of the right tables into the WHERE clause – it belongs in the ON clause

8

Hi,

I made this simple query as per your guideline but it is not returning any data.

SELECT products.*, categories.*, categories.* FROM products
	LEFT JOIN categories AS category ON products.prod_catg_id = category.catg_id
	LEFT JOIN categories AS subcategory ON products.prod_sbcg_id = subcategory.catg_id

Please advice.

Thanks.

9

you missed a couple :slight_smile:

see the SELECT clause

10

Hi,

Removed the double categories but still not working :frowning:

SELECT products.*, categories.* FROM products
	LEFT JOIN categories AS category ON products.prod_catg_id = category.catg_id
	LEFT JOIN categories AS subcategory ON products.prod_sbcg_id = subcategory.catg_id

Thanks.

11

you are still referencing a table called categories in the SELECT clause

you don’t have that table any more, but you do have two tables called category and subcategory

you must use the alias(es) in the SELECT clause too

(hint: use bofadem)

:slight_smile:

12

Hi,

Sorry but I am not understanding it. When I have done like following:

SELECT products.*, categories.* AS maincat, categories.* AS subcat FROM products
	LEFT JOIN categories AS maincat ON maincat.catg_id = products.prod_catg_id
	LEFT JOIN categories AS subcat ON subcat.catg_id = products.prod_sbcg_id

Even then i am getting error: Unknown table ‘categories’

Also what is bofadem ? Googling it just bring me back to some of your old posts at siteforum. :frowning:

Please teach.

Thanks.

13

From the manual

A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

And I guess ‘bofadem’ means ‘both of them’.

14

you cannot assign an alias like that, using the dreaded, evil “select star”

first of all, you should stop using that

secondly, you have to use table aliases like this –

SELECT products.*
     , [COLOR="Blue"]maincat[/COLOR].* 
     , [COLOR="blue"]subcat[/COLOR].* 
  FROM ...

but even then, your troubles are not over, because both of your tables (maincat and subcat) have the same columns, so you should really assign column aliases to them as well

do a search for threads within this forum for “join same table twice” and i’m sure you will find numerous examples :slight_smile:

now ~that’s~ funny!! :smiley: :smiley:

it means “both of them”

:wink:

15

Hello,

Thanks very much. After your hints and tips it has worked :D. I am so happy. The final query:

SELECT products.*,
		maincat.catg_id AS catgid, maincat.catg_name AS catgname,
		subcat.catg_id AS sbcgid, subcat.sbcg_name AS sbcgname FROM products
		LEFT JOIN categories AS maincat ON maincat.catg_id = products.prod_catg_id
		LEFT JOIN categories AS subcat ON subcat.catg_id = products.prod_sbcg_id

Thanks. :smiley: