Welcome to Geeklog, Anonymous Wednesday, December 04 2024 @ 04:12 am EST
Geeklog Forums
Need some help on an sql and left join subselect
Status: offline
::Ben
Forum User
Full Member
Registered: 01/14/05
Posts: 1569
Location:la rochelle, France
Hello,
Here is my code
$sql = "SELECT p.id,p.name,p.short_description,p.price,p.download,i.pi_filename
FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = 1
";
I need the same request with i.pi_img_num >=1 but I would to limit the result to 1 raw.
If i.pi_img_num = 1, it's ok stop here, or if i.pi_img_num = 2 ok stop there...
I read that I must include a subselect but do not succedd yet. Any help would be appreciated.
Thanks,
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
Here is my code
Text Formatted Code
// Get products from database$sql = "SELECT p.id,p.name,p.short_description,p.price,p.download,i.pi_filename
FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = 1
";
I need the same request with i.pi_img_num >=1 but I would to limit the result to 1 raw.
If i.pi_img_num = 1, it's ok stop here, or if i.pi_img_num = 2 ok stop there...
I read that I must include a subselect but do not succedd yet. Any help would be appreciated.
Thanks,
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
6
6
Quote
Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
Can you be more specific? Are you trying to get a random image from the set of images associated with each product? I'm not really understanding what you mean by "but I would to limit the result to 1 raw. " OR "If i.pi_img_num = 1, it's ok stop here, or if i.pi_img_num = 2 ok stop there".
Can you create a few "fake" table listings and explain what you want to output:
products:
id name ...
1 abc
2 def
images:
pi_pid img_num filename
1 1 abc_1
1 2 abc_2
1 3 abc_3
2 1 def_1
2 2 def_2
What is the output you are expecting?
Can you create a few "fake" table listings and explain what you want to output:
Text Formatted Code
products:
id name ...
1 abc
2 def
images:
pi_pid img_num filename
1 1 abc_1
1 2 abc_2
1 3 abc_3
2 1 def_1
2 2 def_2
What is the output you are expecting?
4
6
Quote
Status: offline
::Ben
Forum User
Full Member
Registered: 01/14/05
Posts: 1569
Location:la rochelle, France
The actual code give me each product and the associated image when pi_img_num=1.
Like this I can print a list of products with only the first image for each product.
products:
abc picture abc_1
def picture def_1
If I delete this image, then I would like my request to give me, if the image exist, the next available one for the product (pi_img_num=2). So, there is still one image per produt on the main list.
products:
abc picture abc_1
def picture def_2
Thank you for your help;
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
Like this I can print a list of products with only the first image for each product.
Text Formatted Code
products:
abc picture abc_1
def picture def_1
If I delete this image, then I would like my request to give me, if the image exist, the next available one for the product (pi_img_num=2). So, there is still one image per produt on the main list.
Text Formatted Code
products:
abc picture abc_1
def picture def_2
Thank you for your help;
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
6
4
Quote
Status: offline
Laugh
Site Admin
Admin
Registered: 09/27/05
Posts: 1470
Location:Canada
$sql = "SELECT p.id,p.name,p.short_description,p.price,p.download,i.pi_filename
FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = 1
";
I would try something like this (I believe this will work it has been a while for me with sub queries).
SELECT p.id,p.name,p.short_description,p.price,p.download,(SELECT i.pi_filename FROM {$_TABLES['paypal_images']} AS i WHERE p.id = i.pi_id ORDER BY pi_img_num LIMIT 1) pi_filename
FROM {$_TABLES['paypal_products']} AS p
This should return all items which have at least 1 image. Sub queries can reference the outer query and be located either in the select or where clause.
Search for "subquery" on the MySQL site for more information (I would have included the url but the post was flagged as SPAM).
One of the Geeklog Core Developers.
FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = 1
";
I would try something like this (I believe this will work it has been a while for me with sub queries).
SELECT p.id,p.name,p.short_description,p.price,p.download,(SELECT i.pi_filename FROM {$_TABLES['paypal_images']} AS i WHERE p.id = i.pi_id ORDER BY pi_img_num LIMIT 1) pi_filename
FROM {$_TABLES['paypal_products']} AS p
This should return all items which have at least 1 image. Sub queries can reference the outer query and be located either in the select or where clause.
Search for "subquery" on the MySQL site for more information (I would have included the url but the post was flagged as SPAM).
One of the Geeklog Core Developers.
6
4
Quote
Status: offline
::Ben
Forum User
Full Member
Registered: 01/14/05
Posts: 1569
Location:la rochelle, France
I finally found the query I needed:
FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = (SELECT pi_img_num FROM {$_TABLES['paypal_images']} WHERE pi_img_num >= 1 AND pi_pid = p.id LIMIT 1)
";
maybe there is several approch but this one get products from database only once with no image if there is no image available or the first one if one or more are available.
Thanks.
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
Text Formatted Code
$sql = "SELECT p.id,p.name,p.short_description,p.price,p.download,i.pi_filename FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = (SELECT pi_img_num FROM {$_TABLES['paypal_images']} WHERE pi_img_num >= 1 AND pi_pid = p.id LIMIT 1)
";
maybe there is several approch but this one get products from database only once with no image if there is no image available or the first one if one or more are available.
Thanks.
::Ben
I'm available to customise your themes or plugins for your Geeklog CMS
7
6
Quote
Status: offline
jmucchiello
Forum User
Full Member
Registered: 08/29/05
Posts: 985
Quote by: cordiste
FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = (SELECT pi_img_num FROM {$_TABLES['paypal_images']} WHERE pi_img_num >= 1 AND pi_pid = p.id LIMIT 1)
";
maybe there is several approch but this one get products from database only once with no image if there is no image available or the first one if one or more are available.
Thanks.
::Ben
I finally found the query I needed:
Text Formatted Code
$sql = "SELECT p.id,p.name,p.short_description,p.price,p.download,i.pi_filename FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num = (SELECT pi_img_num FROM {$_TABLES['paypal_images']} WHERE pi_img_num >= 1 AND pi_pid = p.id LIMIT 1)
";
maybe there is several approch but this one get products from database only once with no image if there is no image available or the first one if one or more are available.
Thanks.
::Ben
If you care, that SQL will not work with MS SQL. Try:
Text Formatted Code
$sql = "SELECT p.id,p.name,p.short_description,p.price,p.download,i.pi_filename FROM {$_TABLES['paypal_products']} AS p
LEFT JOIN {$_TABLES['paypal_images']} AS i
ON i.pi_pid = p.id AND i.pi_img_num IN (SELECT MIN(pi_img_num) FROM {$_TABLES['paypal_images']} WHERE pi_img_num >= 1 AND pi_pid = p.id)
";
7
3
Quote
All times are EST. The time is now 04:12 am.
- Normal Topic
- Sticky Topic
- Locked Topic
- New Post
- Sticky Topic W/ New Post
- Locked Topic W/ New Post
- View Anonymous Posts
- Able to post
- Filtered HTML Allowed
- Censored Content