dcsimg
www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] SELECT WHERE TableA.column = TableB.column OR TableA.column = TableC.column

  1. #1
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,816

    resolved [RESOLVED] SELECT WHERE TableA.column = TableB.column OR TableA.column = TableC.column

    Basically, I'm trying to match a primary key in one table to a primary key in two other tables--which are mutually exclusive.

    If anyone can make sense of and/or sort out the following pseudo-SQL, that would be a great help.

    PHP Code:
    SELECT
        
    `ds_line_attributes`.`set_num`,
        `
    ds_line_attributes`.`line_nuim`,
        `
    ds_line_attributes`.`attrib_id`
        (`
    ds_categories`.`categ_name` OR `ds_attribs_for_lines`.`attrib_desc`) AS `att_desc`
        
    FROM `ds_line_attributes`,
            (`
    ds_categoriesWHERE `ds_line_attributes`.`attrib_id` = `ds_categories`.`categ_id`) OR
            (`
    ds_attribs_for_linesWHERE `ds_line_attributes`.`attrib_id` = `ds_attribs_for_lines`.`attrib_id`)


    The reason I'm doing things this way is because the category of a line attribute can serve as an attribute in and of itself.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,213
    My first thought would be to use a union of two separate join queries.
    Code:
    (select * from main_table mt
    inner join join_table_a ja on mt.primary_key_field = ja.foreign_key_field)
    union
    (select * from main_table mt
    inner join join_table_b jb on mt.primary_key_field = jb.foreign_key_field)
    Alternatively, a couple of left joins might do the work in a single query:
    Code:
    select * from main_table mt
    left join join_table_a ja on mt.primary_key_field = ja.foreign_key_field
    left join join_table_b jb on mt.primary_key_field = jb.foreign_key_field
    where ja.foreign_key_field is not null or jb.foreign_key_field is not null
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

  3. #3
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,816
    Quote Originally Posted by NogDog View Post
    My first thought would be to use a union of two separate join queries.
    Code:
    (select * from main_table mt
    inner join join_table_a ja on mt.primary_key_field = ja.foreign_key_field)
    union
    (select * from main_table mt
    inner join join_table_b jb on mt.primary_key_field = jb.foreign_key_field)
    Alternatively, a couple of left joins might do the work in a single query:
    Code:
    select * from main_table mt
    left join join_table_a ja on mt.primary_key_field = ja.foreign_key_field
    left join join_table_b jb on mt.primary_key_field = jb.foreign_key_field
    where ja.foreign_key_field is not null or jb.foreign_key_field is not null
    And the result is:

    Code:
    SELECT 
    	`latts`.`set_num`,
    	`latts`.`line_num`,
    	`latts`.`attrib_id`,
    	CASE
    		WHEN `ds_categories`.`categ_id` IS NULL
    			THEN `ds_attribs_for_lines`.`attrib_desc`
    		ELSE `ds_categories`.`categ_name`
    	END AS `att_desc`
    	FROM `ds_line_attributes` `latts`
    	LEFT JOIN `ds_categories` ON `latts`.`attrib_id` = `ds_categories`.`categ_id`
    	LEFT JOIN `ds_attribs_for_lines` ON `latts`.`attrib_id` = `ds_attribs_for_lines`.`attrib_id`
    	WHERE `ds_categories`.`categ_id` IS NOT NULL OR `ds_attribs_for_lines`.`attrib_id` IS NOT NULL
    ;

    Thanks, NogDog.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center