Mysql query help

Hi

I want to do one select query if the primary id for table A is in table B else do a different query if not.

Summary Example

Table a
Id int
Title varchar

Table b
Id int
Title_id int

Where

select query ONE executes if a.id is in any row of table b. b.title_id
Else
select query TWO executes

Any help much appreciated!
Karen

are you running these queries from a front end language such as php?

because there’s where you should do the flow-of-control IF logic

Hi,

I want to keep as much of the sort and retrieve functionality in the database as possible.

I was up late and finally able to solve using a simple Left Join - after days of contortions involving bizarre mixes of commands!!

It returned all of the records from the first table and a nice value of null for when there was no record in the adjoining table.

Just select the columns you want from first table and second table left join on ids and it will return all of the data you need.

Thanks!

glad to hear it, but a left join doesn’t really do what you originally asked – run a query and then optionally run a different one

Hi

Yes you’re right. I did not express my question correctly in the first place and sadly my result is only half right now.

I will tidy up my tables and produce a cleaner question shortly

Thanks!

I hope that this better clarifies by question:

I have a table called products :

CREATE TABLE products (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
name text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
category text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
subject text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
date_time datetime DEFAULT NULL,
PRIMARY KEY (id)
)

I wish to sort my table by three levels

a> category
b> subject
c> types

Now, types are found in a third table called

product_types

CREATE TABLE product_types (
product_id int(11) NOT NULL,
type varchar(255) DEFAULT NULL
)

My data is displayed in the following table headers which can be ‘clicked’ and send a sort value to my sql query which then sorts by those categories:

Name Category Subject Types Date_Time

When I sort my data, I can sort by name, category and subject easily enough as it is in the product table, however product types allow for multiple categories and I am not able to figure out how to sort it.

eg. This is how I sort by name
SELECT products.id, products.name, products.category, products.subject, GROUP_CONCAT(product_types.type ORDER BY product_types.type ) as types, users.name, products.date_time FROM products LEFT JOIN product_types ON products.id=product_types.product_id INNER JOIN users ON products.user_id=users.id GROUP BY products.id ORDER BY products.name IS NULL, products.name

And this is how I sort by category
SELECT products.id, products.name, products.category, products.subject, GROUP_CONCAT(product_types.type ORDER BY product_types.type ) as types, users.name, products.date_time FROM products LEFT JOIN product_types ON products.id=product_types.product_id INNER JOIN users ON products.user_id=users.id GROUP BY products.id ORDER BY products.category IS NULL, products.category

However, if I try to sort by types because it is a group_concat I am not sure how to sort it. Using the first value is fine as they are displayed alphabetically.

any help deeply appreciated!

thanks
Karen
er

When I try to find a solution I use http://sqlfiddle.com. If I do not solve the problem while asking, it is much easier for other to understand your question and get an answer:

http://sqlfiddle.com/#!15/8cfe2/1

Just do the jsfiddle and copy the address.

what happened when you sorted it like this –

SELECT products.id, products.name, products.category, products.subject, GROUP_CONCAT(product_types.type ORDER BY product_types.type ) as types, users.name, products.date_time FROM products LEFT JOIN product_types ON products.id=product_types.product_id INNER JOIN users ON products.user_id=users.id GROUP BY products.id ORDER BY types IS NULL, types

Hi Thanks,

Because types is from Group Concat, it comes up with the following error:

ERROR 1247 (42S22): Reference ‘types’ not supported (reference to group function)

I will try to set up on sqlfiddle.

thanks
Karen

thanks, ok
I will look at and try to set up on sqlfiddle

thanks
karen

Hi

I have made a sql fiddle here:

http://www.sqlfiddle.com/#!9/d8710c/1

I am trying to sort by types. If null, do not present row.

Many thanks!
Karen

ah, okay…

the classic way to avoid this is to use an outer query

requires aliasing the two name columns as well

this works –

SELECT *
  FROM ( SELECT products.id
              , products.name     AS product_name
              , products.category
              , products.subject
              , GROUP_CONCAT(product_types.type  
                    ORDER BY product_types.type ) as types
              , users.name        AS user_name
              , products.date_time 
           FROM products 
         LEFT 
           JOIN product_types 
             ON products.id=product_types.product_id 
         INNER 
           JOIN users 
             ON products.user_id=users.id 
         GROUP 
             BY products.id 
       ) AS duh
ORDER 
    BY types IS NULL
     , types

why? this is a new requirement, yes?

but if you insist…

SELECT *
  FROM ( SELECT ...
       ) AS duh
 WHERE types IS NOT NULL       
ORDER 
    BY types


MySQL is not my speciality, but here is a shot in the dark (simplified)

http://www.sqlfiddle.com/#!9/d8710c/42

you should simply remove your WHERE condition and make it an INNER JOIN instead of LEFT JOIN

and this advice is not specific to MySQL

1 Like

@r937 That works! Thanks!!

@sibertius - I would like the types harlequin before Javascript (maybe because it is not all caps?)

I really appreciate all your help
Fantastic thanks :smile:

Karen

http://www.sqlfiddle.com/#!9/d8710c/46

Nice…

Another way!

Great teaching :smile:

Many thanks again,
Karen

Hi

I am trying to manipulate the scheme to include another table.

I would like to see all the bids on this table but try as I might I can only get the select statements as above to return only 1 bid.

How can I do hey another join and return all of the bids associated with the product?

Any suggestions or outline of process greatly appreciated!!

New sqlfiddle

http://www.sqlfiddle.com/#!9/33aafa/1

Thanks
Karen

http://www.sqlfiddle.com/#!9/33aafa/5