MYSQL query syntax

Kyle Hamilton
Kyle Hamilton used Ask the Experts™
on
I'd like to export the combined results of two queries to CSV. The export to csv is not an issue.

I need help writing the query which will give me all the results I need. I'm using MySQLWorkbench.

I have some rows from this query:

SELECT * FROM iq.item where grouping_id='334' && class='D';

Open in new window


Then under each result, I would like to list the results from another query:

SELECT * FROM iq.comment where item_id = 2294;

Open in new window


where 'item_id' is the 'id' from the first query. there are multiple rows for each row in the first query.

I'm guessing these have to be joined?

Both tables have a date_created column, which I don't want to duplicate, as well as a content column, which is named differently. One is called 'description' and the second is called 'content'. I would like these two to become one column.

Please let me know if I need to clarify anything else.
Thanks in advance,
Kyle
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Ess KayEntrapenuer

Commented: 
SELECT * 
FROM iq.item i LEFT JOIN
  iq.comment c ON i.id = c.item_id

where i.grouping_id='334' && i.class='D';

Open in new window



if you want all columns from item and specific columns from comments

SELECT i.*, c.item_id, c.columnb, c.columnc, c.col......(insert comuns here)
Kyle HamiltonData Scientist
Most Valuable Expert 2014

Author

Commented:
fantastic!

can I select only certain columns from the item table as well?
Entrapenuer
Commented:
yes,  the query i wrote,  i make shortcuts to the tables

iq. items is now just i
and iq.comment is now just c
so in select if you want fields from i and from c you cad do

select i.field1,  i.timefield, i.someotherfield, c.fieldsomething

from.....


or you can use the asterisk  * to select everything

Select * from..

or you can use table.asterisk to select all columns from just one table

select c.*, i.id, i.timestamp
from...


just remember to separe them by commas
Ess KayEntrapenuer

Commented:
also if any column has a space you must enclose it in brackets

SELECT i.[my weird fieldname]
from...
Kyle HamiltonData Scientist
Most Valuable Expert 2014

Author

Commented:
luvly, thank you!
:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial