sql - How to get data with multiple references to single table -


i have image table 3 columns: id, name, , refid - contain id other table (with example category table , product table). category table 3 columns: id, name , productid. product table includes 3 columns: id, name. product's data:

id                                     name     659543d6-1a0d-4151-b94c-f44ccda752b5   product1 5c1a5a2f-4579-41d2-8fae-93ef71451285   product2      2c35a651-1681-4e5e-9f0c-315169bdfa1d   product3

and category's data:

id                                     name        productid      f3fff612-1d6d-4447-b472-2486c4893cdd   category1   659543d6-1a0d-4151-b94c-f44ccda752b5 4a2ff39d-13e0-4365-8dea-7fad6d6df405   category2   659543d6-1a0d-4151-b94c-f44ccda752b5 599aea59-8a85-409c-9026-7e4313af523d   category3   659543d6-1a0d-4151-b94c-f44ccda752b5

image's data: 

id                                     name     refid      bdd396a2-37a7-4b20-b8c5-8ef2cd58d4c0   image1   659543d6-1a0d-4151-b94c-f44ccda752b5 7cda01ff-b98a-4cdc-a92b-0e31a9cdcd20   image2   f3fff612-1d6d-4447-b472-2486c4893cdd

please see data example: image1 image of product , image2 image of category. , query:

    select category.id, category.name, product.name, image.name      category      inner join product     on category.productid = product.id     inner join image     on category.id = image.refid  -- on product.id = image.refid

with query image2 or image1. problem how 2 image 1 query?

are looking this?

select c.id category_id,         c.name category_name,         p.name product_name,         i1.name category_image,         i2.name product_image   category c join product p     on c.productid = p.id left join image i1      on c.id = i1.refid left join image i2     on p.id = i2.refid

output:

 |                          category_id | category_name | product_name | category_image | product_image | -------------------------------------------------------------------------------------------------------- | f3fff612-1d6d-4447-b472-2486c4893cdd |     category1 |     product1 |         image2 |        image1 | | 4a2ff39d-13e0-4365-8dea-7fad6d6df405 |     category2 |     product1 |         (null) |        image1 | | 599aea59-8a85-409c-9026-7e4313af523d |     category3 |     product1 |         (null) |        image1 |

here sqlfiddle demo


Comments

Post a Comment

Popular posts from this blog

How to mention the localhost in android -

i trying call localhost in android application. unfortunately not possible used several methods emulator local host http://10.0.2.2/android/dbconnection.php and network ip address http://192.168.1.xx/android/dbconnection.php also http://localhost:8080/android/dbconnection.php but nothing connected localhost. purpose refer stackoverflow answers not working i facing same problem when worked on localhost. referred stackoverflow answer got solution create new emulator works fine in new emulator.
Read more

php - Calling a template part from a post -

i include iframe within post in wordpress. located inside content of post, meaning cannot in post template file, otherwise either before or after. i'd add facebook plugin within posts: <div id="socialwrap"><div class="fb-like" data-href="http://onlinearizonahomes.info" data-send="true" data-width="450" data-show-faces="true" data-font="arial"></div></div> i thinking of calling template part within post <?php get_template_part( 'social' ); ?> cannot use php within post without plugin (i'd avoid using plugin this). an alternative using javascript create html in post, seems unnecessary use of javascript. what best way add html within wordpress post? you can use shortcode. add functions.php function cudjex_fbshare( $atts, $content = null ) { global $post; $link = get_permalink($post->id); return '<div id="socialwrap...
Read more

c# - String.format() DateTime With Arabic culture -

check below code snippet, namespace testdateconvertion { class program { static void main(string[] args) { datetime testvalue = new datetime(2013, 12, 15, 15, 33, 44); cultureinfo culture = new cultureinfo("ar-sa"); string stringvalue = string.format(culture, "{0:d} {0:hh:mm:ss}", testvalue); console.writeline(stringvalue); console.readline(); } } } it gives output 22/02/35 15:33:44 i not getting how possible. whats 35 doing in output there that's 13/12/2015 in hijri, 35 means 1435 in hijri, default date format saudi arabia.
Read more