sql query help please

sde · 05-05-2003, 01:09 PM

can't seem to get this happenin.

my tables are like this:

Code:

issues - issue_id,topic
issue_product - issue_id,product_id
products - product_id, product_name

i structured it this way so i can have more than one product associated with an issue.

i need to display the issue id, the topic, and all product names associated with the issue with one query .. is it possible?

for example .. this code doesn't work, but you can see where i'm going

PHP Code:


			
select issues.issue_id, issues.topic, products.product_name

  FROM issues,issue_product,products

  WHERE issues.issue_id=issue_product.issue_id 

    AND issue_product.product_id=product.product_id;

i need it to return multiple products separated by commas though .. i think i might have to do 2 queries.

joe_bruin · 05-05-2003, 01:56 PM

the trick to doing what you want is not to do it in the query, but in your display code. if it's php, it's really easy.

do an "order by issues.issue_id". then, when you iterate through your results, if the issue_id is the same as the previous issue_id, just print a comma and the product name. otherwise start a whole new row, printing the issue_id and topic

abc123 · 05-05-2003, 02:19 PM

Quote:

Originally posted by sde

PHP Code:

select issues.issue_id, issues.topic, products.product_name FROM issues,issue_product,products WHERE issues.issue_id=issue_product.issue_id AND issue_product.product_id=product.product_id;

i need it to return multiple products separated by commas though .. i think i might have to do 2 queries.

isn't it:

Code:

select
  x
from
 tblY
inner join tblZ
  on tblZ.id = tblY.id
where
  1 = 1

..? note the "inner join" ...

like joe said, you can't really get it to put them with commas however, do it in your code, much easier.

sde · 05-05-2003, 02:34 PM

cool thanks a lot guys .. i had already created 2 queries out of it, but i'm going to go back now and use this info.

sde · 05-05-2003, 03:52 PM

that was pretty much it .. the php parsing was the tricky part. for each time it looped, i had to make the script look ahead at the next mysql result .. however .. i had to make sure it didn't check for the next mysql row when it reached the last loop , .. otherwise it generated a mysql error. fun stuff =) thanks

sde · 05-05-2003, 03:55 PM

why do i need to do an inner join? here's my actual query .. joining 6 tables =)

this will be a thread stretcher .. sorry =/

Code:

SELECT details.detail_id,issues.issue_id,details.open,contacts.f_name,contacts.l_name,issues.topic,agents.nick,products.product_name
  FROM issues,details,contacts,agents,products,issue_product
  WHERE issues.issue_id=details.issue_id AND issues.contact_id=contacts.contact_id AND details.agent_id=agents.agent_id AND issue_product.product_id=products.product_id AND issue_product.issue_id=details.issue_id ORDER BY details.detail_id DESC LIMIT 10

abc123 · 05-05-2003, 04:12 PM

Quote:

Originally posted by sde
why do i need to do an inner join? here's my actual query .. joining 6 tables =)

oh, that just must be the syntax for ms sql server ...

mysql must use a diff ver of sql syntax ...

bdl · 05-05-2003, 04:53 PM

Quote:

Originally posted by abc123
oh, that just must be the syntax for ms sql server ...

mysql must use a diff ver of sql syntax ...

Actually, MySQL uses the same sort of syntax, you can read about it here. I've yet to actually use this for anything, but it is syntatically correct to use 'INNER JOIN'.