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
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:
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:
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
, typeswhy? 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)
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
@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 
Karen
Nice…
Another way!
Great teaching
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
Thanks
Karen